updating combobox using Notinlist query

keyur · Jul 11, 2009

Hello,
I am using Access 2007 and am having trouble with notinlist event.

I am trying to update a combobox on the fly using NotinList event. I have a table called tbl_cities with only 1 field 'City'. When user entere a new city then I want to add the new and udpate the combobox.

The sql command works meaning the new value gets inserted but my combobox doesn't gets updated so i keep getting error then the value is not in list. when i exit the form without saving and reopen then i can see the new value in the list. Any suggestions would help. thanks.

Code:

Private Sub City_NotInList(NewData As String, Response As Integer)
sqlstring = "INSERT INTO tbl_cities (City) VALUES ('" & NewData & "')"
DoCmd.RunSQL sqlstring
Response = acErrDataAdded
End Sub

rmethe · Jul 11, 2009

what if you add a requery of your combo box object?

keyur · Jul 11, 2009

Thanks for the reply. I did try to requery but I get a run time error 2118
"You must save the current field before you run the Requery sction,"

Code:

Private Sub City_NotInList(NewData As String, Response As Integer)
sqlstring = "INSERT INTO tbl_cities (City) VALUES ('" & newcity & "')"
DoCmd.RunSQL sqlstring
Response = acErrDataAdded
Me.City.Requery
End Sub

rmethe · Jul 11, 2009

figures, it should refresh the list automatically...
I see, try acDataErrAdded (you hve the Err and Data switched)

keyur · Jul 11, 2009

Great eye you have there. Thank you so much. Can't believe I didn't notice.

updating combobox using Notinlist query

keyur

Registered User.

rmethe

New member

keyur

Registered User.

rmethe

New member

keyur

Registered User.

Similar threads

Users who are viewing this thread