Go to specific field in form using "on not in list " in access

[neelsfer]

I have a combobox in a form that looks up records based on this form itself(cyclist tbl).I also add new records to this same form if i cant find the IDNo of a person in this combobox. If i type in the IdNo of a person in the combobox and dont find it, then i want to move straight to the IDNumber field (first field on the form) called IdNo with the "on not in list" and create a new record to start entering immediately.
Combo is called = Combo177
Form = cyclistf
Table = cyclist
fields:
IdNo = primary key + txt
Surname = txt
Name = txt
Gender = txt

This code ask the right questions, but dont copy this IdNo that it cant find into a new blank field for me. If i say no, then it gets into a loop and you can't get out until you say yes it must add it, which it does not do properly. I need to refresh the data somehow i think.
pls help!!

Private Sub Combo177_NotInList(NewData As String, Response As Integer)
 
    On Error GoTo Combo177_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Id number " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Entries")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO Cyclist([IdNo]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new Id number has been added to the list." _
            , vbInformation, "Entries"
        Response = acDataErrAdded
 
 
    Else
        MsgBox "Please choose an Id Number from the list." _
            , vbInformation, "Entries"
        Response = acDataErrContinue
        DoCmd.GoToRecord , "", acNewRec
    End If
Combo177_NotInList_Exit:
    Exit Sub
Combo177_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Combo177_NotInList_Exit
 
End Sub

[TheSmileyCoder]

Im really at a loss to understand your points. You say one thing in your post, and then do the complete opposite in your code.

If you select yes, to the msgbox, you create the record, but don't go to it. If you select yes, you tell the user to try again, but use the code to go to a new blank record.

Anyways, I have made some modifications. I also STRONGLY suggest a more appropriate name then combobox177, an example could be: cmb_SelectCyclist

Private Sub cmb_SelectCyclist_NotInList(NewData As String, Response As Integer)
'This combobox is used to navigate the form, or as a means to go to a new record for unknown cyclists.
If vbYes= MsgBox("The Id number " & Chr(34) & NewData & _
          Chr(34) & " is not currently listed." & vbCrLf & _
          "Would you like to add it to the list now?" _
          , vbQuestion + vbYesNo, "Entries") Then
  me.cmb_Cyclist.Undo
  Response=acDataErrContinue
  DoCmd.GoToRecord , "", acNewRec
  me.TextBoxBoundToIdField=newData
else
  Me.cmb_Cyclist.Undo
  Response =acDataErrContinue 
End If
End Sub
 

I dont have time to add more detailed comments/description,but hope you can either use this, or that it will point you in the right direction.

[neelsfer]

thx i will give it a try. sorry i copied this code from somewhere on the web and try to make it work. I am a bit of a novice at vba

[neelsfer]

this line seems to give vba error

Me.TextBoxBoundToIdField = NewData

see pic pls. i changed the combobox title

[TheSmileyCoder]

You have to replace the TextBoxBoundToIdField with the name of the control containing your id field (primary key)

[neelsfer]

Thx it working 100% now

[TheSmileyCoder]

Im smiling to hear that.