combobox, NotInList, update form
in [Forms]
|
Prev: lost focus
Next: Add a wait time.
From: Kathy R. on 4 Sep 2009 09:56 Access 2007 Player_tbl playerID (autonumber, primary key) player_name Player_Trans_frm Record Source: SELECT Player_tbl.player_name, Player_tbl.playerID Controls: PlayerID player_name cboFindPlayer Trans_sub (subform parent/child on PlayerID) I have an unbound combobox on a form that I use to jump to a particular record. The code works fine as long as the player_name is already in the table when I open the form. I am trying to use the NotInList property to update the combobox and jump to the new record that was just added. The following code adds the new name to the combobox and underlying table, but I can't figure out how to move to the newly created record on the form. Even when I choose the new name from the combobox the form does not move to the new record. I presume I need to requery something somewhere, but I don't know where, or how. I've also seen where you can pop up a new form to add the info and then close it, updating the original form, but because all I need to do is add the name, I thought that would be a more complicated solution to simple problem. Although if that is the way to do it, I'll go back and revisit it. Your help would be very much appreciated! Kathy R. ------------------------------------------------------------- Private Sub cboFindPlayer_NotInList(NewData As String, Response As Integer) Dim strSQL As String Dim intAnswer As Integer Dim NewPlayerName As String 'Exit this sub if the combo box is cleared If NewData = "" Then Exit Sub NewPlayerName = "'" & NewData & "' is not currently in the player list." & vbCr & vbCr NewPlayerName = NewPlayerName & "Do you want to add this name?" intAnswer = MsgBox(NewPlayerName, vbQuestion + vbYesNo, "Add New Player?") If intAnswer = vbYes Then strSQL = "Insert Into Player_tbl ([player_name]) " & _ "values ('" & NewData & "');" CurrentDb.Execute strSQL, dbFailOnError Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub --------------------------------------------------- The code I use to move to the record upon choosing it in the combobox in case you need to see it too. Private Sub cboFindPlayer_AfterUpdate() Dim rs As Recordset If Me.Dirty = True Then Me.Dirty = False Set rs = Me.RecordsetClone If Not IsNull(Me!cboFindPlayer) Then rs.FindFirst "PlayerID = " & cboFindPlayer If rs.NoMatch Then Exit Sub Else Me.Bookmark = rs.Bookmark Me.player_name.SetFocus End If End If rs.Close Set rs = Nothing End Sub
From: Steve Berg on 4 Sep 2009 17:24 Try requerying the form (Me.Requery) Kathy R. wrote: >Access 2007 > >Player_tbl > playerID (autonumber, primary key) > player_name > >Player_Trans_frm >Record Source: SELECT Player_tbl.player_name, Player_tbl.playerID >Controls: > PlayerID > player_name > cboFindPlayer > Trans_sub (subform parent/child on PlayerID) > >I have an unbound combobox on a form that I use to jump to a particular >record. The code works fine as long as the player_name is already in >the table when I open the form. > >I am trying to use the NotInList property to update the combobox and >jump to the new record that was just added. The following code adds the > new name to the combobox and underlying table, but I can't figure out >how to move to the newly created record on the form. Even when I choose >the new name from the combobox the form does not move to the new record. > I presume I need to requery something somewhere, but I don't know >where, or how. > >I've also seen where you can pop up a new form to add the info and then >close it, updating the original form, but because all I need to do is >add the name, I thought that would be a more complicated solution to >simple problem. Although if that is the way to do it, I'll go back and >revisit it. > >Your help would be very much appreciated! > >Kathy R. > >------------------------------------------------------------- > >Private Sub cboFindPlayer_NotInList(NewData As String, Response As Integer) > > Dim strSQL As String > Dim intAnswer As Integer > Dim NewPlayerName As String > > 'Exit this sub if the combo box is cleared > If NewData = "" Then Exit Sub > > NewPlayerName = "'" & NewData & "' is not currently in the player >list." & vbCr & vbCr > NewPlayerName = NewPlayerName & "Do you want to add this name?" > > intAnswer = MsgBox(NewPlayerName, vbQuestion + vbYesNo, "Add New >Player?") > If intAnswer = vbYes Then > strSQL = "Insert Into Player_tbl ([player_name]) " & _ > "values ('" & NewData & "');" > CurrentDb.Execute strSQL, dbFailOnError > Response = acDataErrAdded > > Else > Response = acDataErrContinue > End If > >End Sub > >--------------------------------------------------- >The code I use to move to the record upon choosing it in the combobox in >case you need to see it too. > >Private Sub cboFindPlayer_AfterUpdate() >Dim rs As Recordset >If Me.Dirty = True Then Me.Dirty = False >Set rs = Me.RecordsetClone >If Not IsNull(Me!cboFindPlayer) Then >rs.FindFirst "PlayerID = " & cboFindPlayer >If rs.NoMatch Then >Exit Sub >Else >Me.Bookmark = rs.Bookmark >Me.player_name.SetFocus >End If >End If >rs.Close >Set rs = Nothing >End Sub
From: Kathy R. on 4 Sep 2009 21:06 That's what I thought would need to be done. The trouble is, I don't know where to put it. It wouldn't go in the cboFindPlayer_NotInList, would it? Because that would requery the control, not the form. I little more direction would be appreciated. Steve Berg wrote: > Try requerying the form (Me.Requery) > > > Kathy R. wrote: >> Access 2007 >> >> Player_tbl >> playerID (autonumber, primary key) >> player_name >> >> Player_Trans_frm >> Record Source: SELECT Player_tbl.player_name, Player_tbl.playerID >> Controls: >> PlayerID >> player_name >> cboFindPlayer >> Trans_sub (subform parent/child on PlayerID) >> >> I have an unbound combobox on a form that I use to jump to a particular >> record. The code works fine as long as the player_name is already in >> the table when I open the form. >> >> I am trying to use the NotInList property to update the combobox and >> jump to the new record that was just added. The following code adds the >> new name to the combobox and underlying table, but I can't figure out >> how to move to the newly created record on the form. Even when I choose >> the new name from the combobox the form does not move to the new record. >> I presume I need to requery something somewhere, but I don't know >> where, or how. >> >> I've also seen where you can pop up a new form to add the info and then >> close it, updating the original form, but because all I need to do is >> add the name, I thought that would be a more complicated solution to >> simple problem. Although if that is the way to do it, I'll go back and >> revisit it. >> >> Your help would be very much appreciated! >> >> Kathy R. >> >> ------------------------------------------------------------- >> >> Private Sub cboFindPlayer_NotInList(NewData As String, Response As Integer) >> >> Dim strSQL As String >> Dim intAnswer As Integer >> Dim NewPlayerName As String >> >> 'Exit this sub if the combo box is cleared >> If NewData = "" Then Exit Sub >> >> NewPlayerName = "'" & NewData & "' is not currently in the player >> list." & vbCr & vbCr >> NewPlayerName = NewPlayerName & "Do you want to add this name?" >> >> intAnswer = MsgBox(NewPlayerName, vbQuestion + vbYesNo, "Add New >> Player?") >> If intAnswer = vbYes Then >> strSQL = "Insert Into Player_tbl ([player_name]) " & _ >> "values ('" & NewData & "');" >> CurrentDb.Execute strSQL, dbFailOnError >> Response = acDataErrAdded >> >> Else >> Response = acDataErrContinue >> End If >> >> End Sub >> >> --------------------------------------------------- >> The code I use to move to the record upon choosing it in the combobox in >> case you need to see it too. >> >> Private Sub cboFindPlayer_AfterUpdate() >> Dim rs As Recordset >> If Me.Dirty = True Then Me.Dirty = False >> Set rs = Me.RecordsetClone >> If Not IsNull(Me!cboFindPlayer) Then >> rs.FindFirst "PlayerID = " & cboFindPlayer >> If rs.NoMatch Then >> Exit Sub >> Else >> Me.Bookmark = rs.Bookmark >> Me.player_name.SetFocus >> End If >> End If >> rs.Close >> Set rs = Nothing >> End Sub >
From: seberg via AccessMonster.com on 4 Sep 2009 23:49 Me always refers to the current form (or report), so Me.Requery will requery the form reguardless of where you put it. To requery the control would be Me. cboFindPlayer.Requery. Kathy R. wrote: >That's what I thought would need to be done. The trouble is, I don't >know where to put it. It wouldn't go in the cboFindPlayer_NotInList, >would it? Because that would requery the control, not the form. I >little more direction would be appreciated. > >> Try requerying the form (Me.Requery) >> >[quoted text clipped - 83 lines] >>> Set rs = Nothing >>> End Sub -- Message posted via http://www.accessmonster.com
From: Kathy R. on 5 Sep 2009 09:06
I'm sorry, I feel like I'm being particularly dense here. But I still can't figure out where to put the Me.Requery If I put it after the "Response=acDataErrAdded" or the after the "End If" line then I get an error at the CurrentDb.Execute strSQL, dbFailOnError line in the Debug window: Run-time error '3022' The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redifine the index to permit duplicate entries and try again. The player_name field in the table is set to Indexed, no duplicates The combobox is unbound with the Record Source SELECT [Player_tbl].[playerID], [Player_tbl].[player_name] FROM [Player_tbl] ORDER BY [player_name]; The bound column is 1 If I've understood it correctly, the combobox does not need to be requeried because that's what acDataErrAdded does. Again, thank you for your help and patience! Kathy R. seberg via AccessMonster.com wrote: > Me always refers to the current form (or report), so Me.Requery will requery > the form reguardless of where you put it. To requery the control would be Me. > cboFindPlayer.Requery. > > > Kathy R. wrote: >> That's what I thought would need to be done. The trouble is, I don't >> know where to put it. It wouldn't go in the cboFindPlayer_NotInList, >> would it? Because that would requery the control, not the form. I >> little more direction would be appreciated. >> >>> Try requerying the form (Me.Requery) >>> >> [quoted text clipped - 83 lines] >>>> Set rs = Nothing >>>> End Sub > |