Update Combo Box when Not In List and update related info in form

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I have a combo box for "properties". When the user enters a value that is
not in the list it asks if they want to add it. When they say yes, it opens
a form so related info can be put in. I adapted this code from posts. I
want it to add the new record to the table before the form opens. Right now
it's not doing that and I don't know why. I have the Properties form opening
to the last record so the user wouldn't have to reenter the value they just
put in the combo box. Thanks.
Marcie

Private Sub cboProperty_NotInList(NewData As String, Response As Integer)

Dim strMsg As String
Dim strSQL As String
Dim strData As String
strMsg = """" & NewData & """ is not in the list" & vbNewLine
strMsg = strMsg & "Do you want to add it?"
strData = NewData
If MsgBox(strMsg, vbInformation + vbYesNo, "New Property") = vbYes Then
strSQL = "Insert Into Properties ([Property]) Values(""" & NewData &
""");"
Response = acDataErrAdded
DoCmd.OpenForm "Properties", , , , acFormEdit, acDialog, strData
Else
Response = acDataErrContinue
End If

End Sub
 
I'd suggest checking into Access HELP for the syntax and commands needed to
add a new combobox item to the underlying table and return to the combobox
with the new value added.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top