Combo Box Updates

[aball65]

I have a combo box on a form which is populated based on values in a lookup table. I need the form to allow a user to easily add a new value to the combo box without leaving the form. To do this currently, the user must:

1. Close the form
2. Open another form used to update the lookup table
3. Add the new value to the lookup table
4. Close the lookup table form
5. Re-Open the form with the combo box

Thanks

 

[RainLover]

Try this. Naturally change to suit your Names etc.


Private Sub cmbZipcode_NotInList(NewData As String, Response As Integer)
intAnswer = MsgBox("The postal code " & Chr(34) & NewData & _
Chr(34) & " is currently not found in this database." & vbCrLf & _
"Would you like to add it to the database?" _
, vbQuestion + vbYesNo, "Address Type Not Found")

If intAnswer = vbYes Then
strSQL = "INSERT INTO tblZipcodes([zipCode], [cityID]) " & _
"VALUES ('" & NewData & "', '" & Me.cmbCity & "');"

DoCmd.RunSQL strSQL

MsgBox "The new postal code has been added to the database." _
, vbInformation, "New postal code added"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Zip code not added, please reselect the zip code from the list"
Response = acDataErrContinue
End If
End Sub

 

[aball65]

Thansk man!

 

[RainLover]

You are most welcome.