Combo Box Add to List

[dwaterman]

I have used the combo box wizard to add a search field to my form. It is set to Limit to List, and I have added code I got from the MSDN site to ask if you want to add a record if not in the list. My yes/no message box displays correctly, and the code to add the record fires. My issue is that it keeps looping back to the do you want to add this record message until I answer no. If I look in the table, I can see the new record, but it is not available in the form. After my INSERT statement, which seems to work, I turn the warnings back on and use

Response = acDataErrAdded

. I understand that acDataErrAdded is supposed to refresh the form, yet the new record is not available to me. My goal is to add the new record and then go to that record if the user answers "yes." Am I going about this the right way?

Dim intAnswer As Integer
intAnswer = "MsgBox("""" & NewData & """ is not available. " & vbCrLf _
& "Do you want to add it now?", vbYesNo + vbQuestion, "Not in List")

Select Case intAnswer
   Case vbYes
      DoCmd.SetWarnings False
      DoCmd.RunSQL " INSERT INTO tblUnit(txtSerialNo)", _
           & "Select """ & NewData & """;"
      DoCmd.SetWarnings True
      Response - acDataErrAdded

   Case vbNo
      MsgBox "Please select an item from the list.", _
          vbExclamation + vbOKOnly, "Invalid Entry"
      Me!Combo71.Undo
      Response = acDataErrContinue

End Select

[orange]

After your Insert statement and before the Case vbNo
add a line to requery the recordsource of the form

  me.Requery

(best guess) Good luck.

[dwaterman]

I tried that already, and it didn't work. I'm wondering if I have code firing somewhere else that is affecting this. What I have here should be working.

[dwaterman]

I have used this same code on other combo boxes in the same form and it works properly. The difference is that I am using it on a lookup combo box. The after update event has a SearchForRecord macro with the condition == "[txtSerialNo] = " & """ & [Screen].[ActiveControl] & """. Perhaps this is firing before the re-query to throw me into the loop. Even when I explicitly re-query, I still get the loop.