DLookUp Command 1

[fredk]

I have a table set up with the sales rep that handles each broker. When data is entered into broker field on the input form, I would like the sales rep information (associated with the client) to populate as a result of inputting the broker name.

I have created a macro to respond to the afterupdate event of the broker textbox - When I select the broker on the form, it always uses the first sales rep in the client table.

My Expression is: DLookUp("[SalesRep]","tblBrokers"."[tblBrokers].[Broker]=Broker&quot

I appreciate any feedback that could be provided - Thanks!!!

 

[tempclerk]

Try DLookUp("[SalesRep]","tblBrokers"."[Broker]=[formname].[Broker]&quot or something like that. I always have to make a couple of stabs before I get the criteria right.

 

[jfhewitt]

I think your syntax is bad. You need a comma after the table name to separate the arguments.

 

[JoeMiller]

Try this:

DLookUp("[SalesRep]","tblBrokers"."[Broker]=[Broker]&quot

Make sure that Broker is the name of the control that stores the Broker field on your form. If it's not, change the name to Broker to make the code work.

HTH Joe Miller
joe.miller@flotech.net

 

[fredk]

Thanks for the assistance - I will try your suggestions Monday - I really appreciate your help!!!

 

[JoeMiller]

I realized I made a typo, there should be a comman in there after tblBrokers as jfhewitt pointed out:

DLookUp("[SalesRep]","tblBrokers","[Broker]=[Broker]&quot
Joe Miller
joe.miller@flotech.net

 

[japyy]

The Where-clause "[Broker]=[Broker]" isn't very specific: it's like saying "get anyone whose age equals his own age".
You would need a stament like: "get anyone whose age equals " & TheAgeINeed, in your case something like
"[Broker] = " & me.Broker

 

[fredk]

Japyy - I think you hit it on the head - My database is in work so I have not had a chance to check but I believe I was using the same sytax as Joe provided - Yet it was giving me the very first sales rep in the table.

Can you please explain the & me.Broker? Is that what will link the sales rep to the broker on the form from the table?

Thanks for everyones help!!!!!

 

[japyy]

"broker = broker"

means: broker(in tblBrokers) equals broker (in the same record, in the same table).
What you want to is: "broker = 'Jimmy'" or
"broker = 12345" (using numbers as a primary key is strongly advised).
Of course, 'Jimmy' or 12345 are variable, you don't know whom your user is going to select. So, you end up with:

"broker = '" & variable & "'"

(a string requires quotation marks) or
[/code]"broker = " & variable.[/code]
The variable is outside the quotation marks; it doesn't refer to a field in the table, but is to be evaluated (changed into 'Jimmy' or 12345) before looking at the table at all.
The variable comes from your form. 'Me' refers to the current form, I made a guess that you called the specific field 'broker'. So:

"broker = '" & Me.broker & "'"

or:

"broker = " & Me.broker

 

[fredk]

Japyy - Thanks so much for your help - I will try this
today - I really do appreciate you taking the time to
help me!!!!

 

[fredk]

Sorry to be a pain - I don't think I did a good job of explaining my situation. On the form, I want to be able to enter a salesrep and have the broker field automatically populated by using the broker form.

In the tblBroker table are brokers and their associated salesreps - The point of this is to take away some data entry on the form.

My macro is seperate so me.form is not working for me.

DLookUp("SalesRep]","tblBrokers","[Broker]=[Broker]&quot
does return the first salesrep in the table but when I change the broker nothing happens - It always refers to the first salerep in the table ???

Thanks!!!

 

[JoeMiller]

You will need to put a requery command in the OnCurrent event of the form. The reason you need to do this is so that Access performs the DLookup again every time a user pulls up a new record. Go to the form properties, click the OnCurrent event and then the ... next to it, select "Code Builder" and put this statement in the screen:

MyTextBoxName.Requery

Change MyTextBoxName to match the name of your textbox that currently has the DLookup command.

HTH Joe Miller
joe.miller@flotech.net

 

[fredk]

The only other problem I see is that regardless of what Broker I have selected, it uses the first salesrep. In other words, even when I enter a broker on the form that has Joe Miller as the broker, It populates the sales rep as Jane Doe (who is the first salesrep listed on the table for another broker)

?? Thanks Joe, I really appreciate your help!

 

[Tmconsult]

if your looking up Salesrep. take an unbound text and then make its control source
=DLookUP("[SalesRep]","[BrokerTable]","[BrokerTable]![ID]=[forms]![Brokers]![ID]&quot
this line says to lookup the salesrep from the brokertable, where the ID(field which relates the two tables) of the brokertable = the ID on the brokers form. These name are just made up, use the name of your form and the table with the info you want to lookup. also put this line in a module on maybe like a gotfocus, and set it so that whenever you go to another record the focus will go to the field which looks up the information

 

[Tmconsult]

if your looking up Salesrep. take an unbound text and then make its control source
=DLookUP("[SalesRep]","[BrokerTable]","[BrokerTable]![ID]=[forms]![Brokers]![ID]&quot
this line says to lookup the salesrep from the brokertable, where the ID(field which relates the two tables) of the brokertable = the ID on the brokers form. These name are just made up, use the name of your form and the table with the info you want to lookup. you could also put this line in a module on maybe like a gotfocus, and set it so that whenever you go to another record the focus will go to the field which looks up the information

 

[fredk]

Got it - Thanks for the help! I appreciate it!