need combo box to open AddRecord form if applicable

[TheSouthCole]

I can't write VBA code, so maybe I can't do this....

On a data entry form is a combo box for users to choose a customer. The CustID# is stored in that field.

But if the customer's not in the list, I want the combo box to open an AddCustomer form, and then take the new customer's CustID# from the AddCustomer form when done.

Would this site help me? Thanks for any advice!

 

[PHV]

You'll have to use VBA code in the NotInList event procedure of the combo.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheSouthCole]

...and would I be able to get that code here?

 

[TheSouthCole]

PHV,
I was given the following code for NotInList, but if you need to add a Tom Smith and there is already another Smith, the code doesn't start the process of opening the "Add" form.

Code is:
Private Sub CustID_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) = vbNo Then
Response = acDataErrContinue
Me.CustID = ""
Exit Sub
Else
'If user chose Yes, start the Customers form in data entry
'mode as a dialog form, passing the new customer last name in
'NewData to the OpenForm method's OpenArgs argument. The
'OpenArgs argument is used in AddCustomer form's Form_Load
'event procedure.
DoCmd.OpenForm "AddCustomer", , , , acAdd, acDialog, NewData
End If
'Look for the customer the user created in the AddCustomer form.
Result = DLookup("[CustID]", "Customers", _
"[CLName]='" & 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
'Display a customized message
MsgBox "Please try again!"
Else
'If the customer was created, set the Response argument to
'indicate that new data is being added.
Response = acDataErrAdded
Me.CustID.RowSource = Me.CustID.RowSource
End If

End Sub