Good Afternoon Everyone
I have used the following code to update a combo box list when the item I am typing is not on the list.
I works ok except that I have to close the form to update and then reopen.
Can anybody suggest how I can get the field to update with the current information.
Thanks.
Private Sub CityID_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The City" & Chr(34) & NewData & _
Chr(34) & "is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Danco Transport Ltd.")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tCity([City]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new City has been added to the list." _
, vbInformation, "Danco Transport Ltd."
Response = acDataErrContinue
End If
CityID_NotInList_Exit:
Exit Sub
CityID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume CityID_NotInList_Exit
Forms![frmAddress]![CityID].Requery
End Sub

Hi

Change

Response = acDataErrContinue

to

Response = acDataErrAdded

I'd also use the Execute method instead of DoCmd.RunSQL like this

Try this line

Response = acDataErrAdded

Dang, that's what happens when you get distracted while posting! Sorry Alan.

Hi Paul

No problem - 2 for the price of 1 is always a good deal

Hey Paul and Alan:

You guys are life savers!!!!
Thanks very much!
I am new to vb and need to understand these changes for future.
I understand why you asked to have code changed from
Response = acDataErrContinue

to

Response = acDataErrAdded

Please help me to understand why you asked me to use the execute method rather than the one I used as follows:

"I'd also use the Execute method instead of DoCmd.RunSQL "

Anyways, it works superb and I thank you both!!!!!
Regards,

Hi Geoff

Using the Execute method of the CurrentDB object has two definite advantages -

1 No need to SetWarnings on or off which generally speaking isn't best practice. You're SQL will still run without the Access prompts (You are about to etc etc) and because you're setting the dbFailOnError parameter you'll still get a message telling you if the query failed for any reason.

2 Your query will run faster than using the RunSQL method, as in effect it's one step closer to the database engine which ultimately executes the SQL

Hope that helps a bit