NotInList adds new record, how to go to that record?

[Applebaum]

Hello,

I'm trying to do something that I think is fairly basic.

I have a form for People, with a combo box to search through records.

When a user wants to add a new person, they first check the combo box. If
the person is in the list, they're taken to that record. If NotInList, the
new record gets added.

However, when the new record is added, I want this form to go to that very
new record, so that I can make sure that required fields get filled out.

Here's the code I've got so far:

Private Sub Form_Open(Cancel As Integer)
Me.cboPeople = Me.PersonID
End Sub

Private Sub Form_Current()
If Me!cboPeople <> Me!PersonID Then Me!cboPeople = Me!PersonID
End Sub

Private Sub cboPeople_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PersonID] = " & Str(Me![cboPeople])
Me.Bookmark = rs.Bookmark
End Sub

Private Sub cboPeople_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboPeople_NotInList
Dim ctl As Control
Dim strSQL As String
' Return Control object that points to combo box.
Set ctl = Me!cboPeople
' Prompt user to verify they wish to add new value.
If MsgBox("Item entered is not in list. Add it?", vbOKCancel) = vbOK
Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
' Add string in NewData argument to table.
strSQL = " INSERT INTO tblPeople (Person) SELECT '" & NewData & "'"
NewData & "','Person')"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
ctl.Value = NewData
DoCmd.SetWarnings True
Me.Requery
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
ctl.Undo
End If

Exit_cboPeople_NotInList:
Exit Sub

Err_cboPeople_NotInList:
MsgBox Str(Err)
MsgBox Err.Description
Resume Exit_cboPeople_NotInList
End Sub


Many thanks in advance!!!

Peace,
Matthew