Full Version: Problems with updating a combo box in a form
UtterAccess Forums > Microsoft® Access > Access Forms
danco
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
Alan_G
Hi
hange
Response = acDataErrContinue
to
Response = acDataErrAdded
I'd also use the Execute method instead of DoCmd.RunSQL like this
CODE
If intAnswer = vbYes Then
strSQL = "INSERT INTO tCity([City]) " & _
"VALUES ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
MsgBox "The new City has been added to the list." _
, vbInformation, "Danco Transport Ltd."
End If
pbaldy
Try this line
esponse = acDataErrAdded
pbaldy
Dang, that's what happens when you get distracted while posting! Sorry Alan.
Alan_G
Hi Paul
o problem - 2 for the price of 1 is always a good deal wink.gif
danco
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,
Alan_G
Hi Geoff
sing 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
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.