Originally Posted by
Micron
There is no need to complicate things by opening another form to add a value that someone has already tried to enter.
Thank you again for the advice. I have several places where comboboxes draw from tables and all but one have only a primary key and a name or description field. I decided that the information in the second field on this particular one is not critical, so I'm going to take your suggestion and run with it. I tend to be overly cautious about getting bad data so I have a habit of having too many hurdles, thank you for helping me eliminate one.
I did use the code in the link but it did not function right away, I had to go through, study and modify it a bit to get it to work, a great learning experience. Here's the code which does work:
Code:
Private Sub cboMFGName_NotInList(NewData As String, Response As Integer) On Error GoTo Error_Handler
Dim intAnswer As Integer
intAnswer = MsgBox("""" & NewData & """ is not an approved Manufacturer. " & vbCr & vbCr _
& "Do you want to add it now?", vbQuestion + vbYesNo, "Invalid Selection")
Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblMFG (MFGName) " & "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.Number & "," & Err.Description
Resume Exit_Procedure
Resume
End Sub