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!~

 

[Remou]

What is the row source for your combobox?

 

[Elvis72]

This is it:

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

 

[Remou]

There is your problem already. Include the unique key for the person in a hidden column.

 

[Elvis72]

OK...updated my combobox recordsource to:

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

But this is still not pointing me in the direction to figure out how to update 2 fields?

 

[Remou]

You will need to open form to allow the user to add all the necessary details for a manager. Here is some information:

http://support.microsoft.com/kb/197526

 

[Elvis72]

This part works fine:

Private Sub Manager_NotInList(NewData As String, Response As Integer)

Dim Result
Dim Msg As String
Dim CR As String

CR = Chr$(13)

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not in the list." & CR & CR
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then

DoCmd.OpenForm "FrmManager", , , , acAdd, acDialog, NewData
End If

End Sub

IT does not like:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
' If form's OpenArgs property has a value, assign the contents
' of OpenArgs to the CompanyName field. OpenArgs will contain
' a company name if this form is opened using the OpenForm
' method with an OpenArgs argument, as done in the Orders
' form's CustomerID_NotInList event procedure.
Me![FrmManager] = Me.OpenArgs
End If
End Sub

it can't find FrmManager?

 

[Remou]

You need:

Me![Name of the control that should have new data, txtSurname for example] = Me.OpenArgs

In your case, I would skip this bit for now, it will just make life more difficult. You would probably need to split the name into its various parts and put each part in the correct box.

 

[Elvis72]

OK...then how do you on close of that form update the table that managers is based?

 

[Remou]

The form should be linked to the managers table in the usual way. That is, the record source is set to a query on the managers table.

 

[Elvis72]

I think I my brain is turned off today because I am really confused..

I have my main form with the Managers ComboBox which has Last Name, First Name.

I have it where on not in list it opens FrmManager where I can enter the new person.

But when I close the form and I see the original main form the manager's combobox does not update.

 

[Remou]

Do you have Response=acDataErrAdded in there anywhere?

 

[Elvis72]

Nope...where would I put it???

I haven't done this in a really long time, but I don't remember it being so hard...then again...I'm older and my brain apparently isn't working so well...lol

 

[Remou]

After the code that opens your form. The link I posted has this code:

    ' Look for the customer the user created in the Customers form.
    Result = DLookup("[CompanyName]", "Customers", _
             "[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
       ' 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

 

[Elvis72]

OK...here is what I have:

' Look for the customer the user created in the Customers form.
Result = DLookup("[Manager]", "TblManager", _
"[Manager]='" & 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

When I type:

Rogers Amber

In the Manager's Combobox the prompt comes up to add.

I click add and the FrmManager comes up, I type in Rogers and Amber in the respective fields and close the form.

It says Please Try Again...

 

[Remou]

I think we need to try something else here. There are a couple of possibilities. If you do not have many users creating managers at the same time and the ManagerID is an autonumber, you can use:

Result = DLookup("[ManagerID]", "TblManager", _
"[ManagerID]=DMax("[ManagerID]", "TblManager"))

If you do have a lot of creations, we can try another direction.

 

[Elvis72]

Well when I put that in the code it gives me a Compile Syntax Error?

 

[Remou]

Sorry.

Result = DLookup("[ManagerID]", "TblManager", "[ManagerID]=" _
& DMax("[ManagerID]", "TblManager"))

 

[Elvis72]

Good Morning, I hope your having a wonderfully fabulous day!~

I used the new code and it works!~ Yeah!~

But when I close the form that comes up it still says the that the data entered is not in list....AND when I drop down on the list for Manager on the form its there...BUT its not in the TblManager?

So, I'm thinking I am at some point telling it to add to a value list or rowsource?

 

[Remou]

Please post all your code.