NotInList Multiple Field ComboBox 1

[Elvis72]

I have searched and searched...can't find my way...

I have no problems with a one column notinlist function.

But I have one field that shows a combined combobox for Managers:

Which shows Last Name and First Name.

I cannot figure out how in the world to create and update for 2 fields?

Any direction is greatly appreciated!~

 

[Elvis72]

OK...either you don't sleep or we both get up WAY too early!~

Here is the Manager Combobox:

Private Sub Manager_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 "FrmManager", , , , acAdd, acDialog, NewData
End If

' Look for the customer the user created in the Customers form.
Result = DLookup("[ManagerID]", "TblManager", "[ManagerID]=" _
& DMax("[ManagerID]", "TblManager"))

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

I have none on the FrmManager that comes up to add the record.

 

[Remou]

The code works for me in a mock-up. Make sure that your combo row source has the ManagerID, for example:
Select ManagerID, LastName, FirstName From tblManager <etc>

Also ensure that the bound column is 1.

You could also try putting the combo and code on a new form, to ensure that there is no other code interfering.

 

[Elvis72]

Here is the Rowsource:

SELECT TblManager.ManagerID, TblManager.[Last Name], TblManager.[First Name] FROM TblManager ORDER BY [Last Name];

and the bound column is 1.

 

[Remou]

Did you try a new form as I suggested?

 

[Elvis72]

Yes I did....still does the same thing?

 

[Remou]

Ok.

    Else
       ' If the customer was created, set the Response argument to
       ' indicate that new data is being added.
       Me.Manager.Undo
       Me.Manager.Requery
       
       Me.Manager = Result
       
       Response = acDataErrContinue
    End If

 

[Elvis72]

WHOOOO HOOOOO!~

If I didn't have a twisted knee and my leg in a brace I would be doing the HAPPY Dance!~

Thanks sooo very much!~