combobox, NotInList, update form

[Kathy R.]

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

 

[Kathy R.]

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)

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

 

[Kathy R.]

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.

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.

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