I have a combo box that gathers insurance company names from my
CCtable. If you add a name that isn't in the list it:
1)asks if you want to add the company
2)if so opens up the CCform (based on my CCtable)
3)after you add the new company and save it reverts back to the
original form
My problem is that when I return to the form and hit tab to get to the
next field it asks if I want to enter the company name again! I use the
same code for 3 combo boxes and they all do the same thing. If anyone
can help I would be grateful.
Private Sub InsuranceCompanyLookup_NotInList(NewData As String,
Response As _
Integer)
Dim Result
Dim Msg As String
Dim CR As String
CR = Chr$(13)
' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub
' Ask the user if he or she wishes to add the new customer.
Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
' If the user chose Yes, start the Customers form in data
entry
' mode as a dialog form, passing the new company name in
' NewData to the OpenForm method's OpenArgs argument. The
' OpenArgs argument is used in Customer form's Form_Load
event
' procedure.
DoCmd.OpenForm "CCForm", acNormal, , , acAdd,
acWindowNormal, NewData
End If
' Look for the customer the user created in the Customers
form.
Result = DLookup("[CompanyName]", "CCTable", _
"[CompanyName]='" & NewData & "'")
If IsNull(Result) Then
' If the customer was not created, set the Response
argument
' to suppress an error message and undo changes.
Response = acDataErrContinue
Else
' If the customer was created, set the Response argument to
' indicate that new data is being added.
Response = acDataErrAdded
End If
End Sub