Dlookup problem

[maGemme]

Hello everyone,

Now I have searched this and other forums for days but cannot get my Dlookup function to work.

Here's what I'm trying to do:

I have 3 tables, one called Staff_List, another called Question_Form and a third called Questions.

The first contains information about our staff, the fields I use for this DB are [Full Name] and [Current Manager]. **I know that using spaces is wrong but the list is updated monthly and comes from an excel spreadsheet.

Basicaly I have a form with a combo box. This box allows the user to chose which staff member is calling, the source of the combo box is in the Staff_list table and the result is stored in another table called Questions.

What I want to do is automaticaly add the agent's manager's name to a field in the Questions table whenever the user selects the agent from the combo box.

The best way I have found so far is to use the Dlookup function.

Right now what I have is an "Afterupdate event" that initiates the Dlookup and hopfully returns it to the Questions table.

Here is my code :

Private Sub CSS_AfterUpdate()
Dim txtManager As String
txtManager = DLookup("[Current Manager]", "Staff_list", "[Full Name] = ' " & CSS & "'")
End Sub


txtManager is a temporary txt box that I put on my form, it is linked to a field called [manager] in my Questions form. CSS is the name of my combobox with the agent names.

I hope I've been clear enough, if not i'll try to clarify.

Thank you!

 

[vbaInet]

You can have the DLookup() in the Control Source property of your textbox by the way.

= Nz(DLookup("[Current Manager]", "Staff_list", "[Full Name] = '" & CSS & "'"), "")

I noticed a space between the quotes highlighted and removed it. Put that code in the control source.

However, does the agent not have an ID?

Welcome to AWF by the way

 

[maGemme]

Well that does work but now that I've changed the control source how can I record the value in my Questions table? that was the whole purpose of the text box imo.

Also, would you please explain the "= NZ(" part that you added before the function because that obviously did the trick.


Thank you for the welcome and the quick reply by the way!

 

[vbaInet]

Ah, alright. I didn't notice you were saving the value. Go back to your previous After_Update event.

What actually did the trick was that extra space I mentioned that was removed. The space made it impossible to find a match.

Nz() function would return an empty string to the control if the DLookup couldn't find a match. The DLookup returns a Null if there's no match and because a control's value cannot be set to Null we would need to check for Null and act accordingly.

 

[maGemme]

That did the trick, I had to remove the "Dim txtManager As String" part and it worked like a charm.

Here is my final code if ever this helps someone.


Private Sub CSS_AfterUpdate()
txtManager = Nz(DLookup("[Current Manager]", "Staff_list", "[Full Name] = '" & CSS & "'"), "")
End Sub

Thank you again!!

 

[vbaInet]

Great! You're welcome. Glad it's working.

 

[maGemme]

Hi again, I am realy feeling like a Dlookup retard now.

The previous worked perfectly but now I have a new problem.

On a form (Settlement), I have 3 bound boxes. The first (Initial_Offered_Amount) records an offer from a client, the second (Settled_amount) record the amount that we negociated.

The third (Initial_vs_Settled) should lookup the result of a small calculation done in a hidden unbound box (intial_settled_lkup).

The way I tried to set it up is through an AfterUpdate setup on the 2nd bound box.

Here's my code :

Private Sub Initial_vs_Settled_AfterUpdate()
Initial_vs_Settled = Nz(DLookup("[intial_settled_lkup]", "[Settlement]"), "")
End Sub

When I tab out of the box, nothing happens. I need help again!

Thank you