I have a form with a bound ComboBox, cboVendor. If the user enters a vendor that's not in the list, I want a message box to pop up asking if they would like to add it. If yes, then it to add the vendor to the combobox's row source table and to the list. I've googled and found the following code which I've updated with the names of my forms and tables. I've been trying all day to figure out how to write the RunSQL statement with no luck, so I'm posting it here. I want to insert the NewData into the VendorName field in the Vendor2 table. Please help. Thank you!
Code:
Private Sub cboVendor_NotInList(NewData As String, Response As Integer)
On Error GoTo Error_Handler
Dim intAnswer As Integer
intAnswer = MsgBox(NewData + " is not an approved category. " & vbCrLf & "Do you want to add it now?", vbYesNo + vbQuestion, "Invalid Category")
Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Vendor2 (VendorName) "
&; _ "Select """ &; NewData &; """;"
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
MsgBox "Please select an item from the list.", _
vbExclamation + vbOKOnly, "Invalid Entry"
Response = acDataErrContinue
End Select
Exit_Procedure:
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Description, vbCritical, "Form_PurchaseOrder.cboVendor_NotInList"
Resume Exit_Procedure
End Sub