I have a form titled "Register". This form refers to a table "Register".

On the form I have the fields:

First Name (combobox)
Last Name (combobox)

I wanted the fields to bring up a subform if a name was typed in the fields that was not already in the "Register" table. The subform is titled "New Customer". The new customer info would be placed in the "Customer List" table. I got a formula from the forum that works, but it only works for the first name and does not seem to relate the first/last name together as one item.

For example, I can type "Bob" as the first name. If Bob is not already in the customer list a subform asks me to enter him in. I enter all of his information (including his last name) in the sub form and then close the subform. His last name is now at the bottom of the list under the last name field and I can select it. The problem is that I can select the name "Susan" from the first list, and as long as someone else in the list is named Susan it doesn't ask me to update the customer list. Now I can select "susan", but if her last name is not already in the last name field list, it tells me I must select from the list. It also allows me to select a name from the first name list and pair it up with a last name from the last name list without asking me to enter them as a new customer. So if I have a "Jody" in the first name field list I can select her and then I can select a last name "Smith". And although there is no "Jody Smith" in the customer list, it will allow me to enter it in the register form without updating the customer list. It is reading each individual field list instead of reading "first name/last name" together.

Here are my convoluted vb formulas:

Option Compare Database
Option Explicit

Private Sub Class_Name_AfterUpdate()

Dim stDocName As String

Exit_Class_Name_Click:
Exit Sub

Err_Class_Name_Click:
MsgBox Err.Description
Resume Exit_Class_Name_Click

Reset

End Sub

Private Sub Customer_ID__AfterUpdate()
Me.Refresh
End Sub

Private Sub Date_Offered_AfterUpdate()

Me.Refresh

End Sub

Private Sub Date_Offered_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Date_Offered_Click

Dim stDocName As String

Exit_Date_Offered_Click:
Exit Sub

Err_Date_Offered_Click:
MsgBox Err.Description
Resume Exit_Date_Offered_Click


End Sub

Private Sub Customer_ID__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 "Customer List", , , , acAdd, acDialog, NewData
End If

' Look for the customer the user created in the Customers form.
Result = DLookup("[Customer First Name]", "Customer List", _
"[Customer First Name]='" & 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
End If
End Sub

Private Sub Manager_First_Name_AfterUpdate()
Me.Refresh

End Sub

Private Sub Manager_First_Name_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Manager_First_Name_Click

Dim stDocName As String

Exit_Manager_First_Name_Click:
Exit Sub

Err_Manager_First_Name_Click:
MsgBox Err.Description
Resume Exit_Manager_First_Name_Click:

End Sub

Private Sub Manager_Last_Name_AfterUpdate()
Me.Refresh
End Sub

Private Sub Team_AfterUpdate()

Me.Refresh
End Sub

Private Sub Team_BeforeUpdate(Cancel As Integer)

End Sub