DLookup problems

[Richard Stricker]

Hi, I'm having some problems with the Dlookup function. I am trying to link
a text box field from another table to a combo box that is linked to that
other table.

The combo box control source is: BuyerID
This is a foreign key in the products table.

the row source of the combo box(cboBuyerScreenName) is:
SELECT [Buyers].[Buyer_ID], [Buyers].[Buyer_Screen_name] FROM Buyers;

The text box control source:

=DLookUp("[Buyer_contact_email]","Buyers","Buyers_ID =" &
[cboBuyerScreenName.buyerID])

The combo box does track the correct Buyer Screen Name but the text box
shows #name errors.

Any help would be appreciated.

Richard

 

[John W. Vinson]

Hi, I'm having some problems with the Dlookup function. I am trying to link
a text box field from another table to a combo box that is linked to that
other table.

The combo box control source is: BuyerID
This is a foreign key in the products table.

the row source of the combo box(cboBuyerScreenName) is:
SELECT [Buyers].[Buyer_ID], [Buyers].[Buyer_Screen_name] FROM Buyers;

The text box control source:

=DLookUp("[Buyer_contact_email]","Buyers","Buyers_ID =" &
[cboBuyerScreenName.buyerID])

The combo box does track the correct Buyer Screen Name but the text box
shows #name errors.

The combo box has a bound column - presumably the BuyerID - which is the value
of the combo box. To retrieve that value simply reference the combo box name;
adding .BuyerID is the reason for your problem! Just remove that. It should be

=DLookUp("[Buyer_contact_email]","Buyers","Buyers_ID =" &
[cboBuyerScreenName])

John W. Vinson [MVP]

 

[Bob Quintal]

Hi, I'm having some problems with the Dlookup function. I am
trying to link a text box field from another table to a combo
box that is linked to that other table.

The combo box control source is: BuyerID
This is a foreign key in the products table.

the row source of the combo box(cboBuyerScreenName) is:
SELECT [Buyers].[Buyer_ID], [Buyers].[Buyer_Screen_name] FROM
Buyers;

The text box control source:

=DLookUp("[Buyer_contact_email]","Buyers","Buyers_ID =" &
[cboBuyerScreenName.buyerID])

The combo box does track the correct Buyer Screen Name but the
text box shows #name errors.

Any help would be appreciated.

Richard

Why go to all the trouble of the Dlookup? You have the buyers
table already set, just add the [Buyer_contact_email] to the
controlsource of your combo, set it's width to 9" and set the
textbox controlsource to =cboBuyerScreenName.column(2) since
it's the third column.

 

[Richard Stricker]

John W. Vinson [MVP] answered:

It should be

=DLookUp("[Buyer_contact_email]","Buyers","Buyers_ID =" &
[cboBuyerScreenName])

Thanks, John. That is exactly right and now that text box tracks the combo
box correctly.

I appreciate you taking the time to point out some of the subtleties of the
controls.

Richard

 

[Richard Stricker]

Thanks for sharing this approach. The Dlookup is a challenging function for
sure. Many finicky details to get caught on. Made me realize I don't
understand the mechanics of the expression builder very well.

I had used the .column(2) approach before and ran into a problem trying to
update that control. (Later I read somewhere that using the column method
makes the text box read only.)

The Dlookup was mentioned as another way to get data in and, since I was
basically floundering anyway, thought I would give it a try. Thanks to the
group I learned a little more.

At this point I don't know if the dlookup allows update, maybe tomorrow I
can explore that section of the project.

Thanks for taking the time to share your alternate approach. It does seem
more direct.

Richard

Why go to all the trouble of the Dlookup? You have the buyers
table already set, just add the [Buyer_contact_email] to the
controlsource of your combo, set it's width to 9" and set the
textbox controlsource to =cboBuyerScreenName.column(2) since
it's the third column.

 

[John W. Vinson]

At this point I don't know if the dlookup allows update, maybe tomorrow I
can explore that section of the project.

It doesn't and shouldn't. The point of looking up a value is to *look up
what's actually there*, not to allow you to have the textbox show something
that isn't there.

If you are trying to store the looked-up value in another table, the usual
advice would be DON'T. It's redundant and unnecessary, if you can display the
looked-up value (using either DLookUp or the more efficient Column()
technique) at will.

John W. Vinson [MVP]

 

[Bob Quintal]

Thanks for sharing this approach. The Dlookup is a challenging
function for sure. Many finicky details to get caught on. Made
me realize I don't understand the mechanics of the expression
builder very well.

I had used the .column(2) approach before and ran into a
problem trying to update that control. (Later I read somewhere
that using the column method makes the text box read only.)

The Dlookup was mentioned as another way to get data in and,
since I was basically floundering anyway, thought I would give
it a try. Thanks to the group I learned a little more.

At this point I don't know if the dlookup allows update, maybe
tomorrow I can explore that section of the project.

DLookup does NOT! allow updates to the looked-up data.

Thanks for taking the time to share your alternate approach.
It does seem more direct.

Richard

Why go to all the trouble of the Dlookup? You have the buyers
table already set, just add the [Buyer_contact_email] to the
controlsource of your combo, set it's width to 9" and set the
textbox controlsource to =cboBuyerScreenName.column(2) since
it's the third column.

 

[Richard Stricker]

You are right Bob, about the Dlookup not accepting data updates either. I am
starting to see those fields that are dependent on other fields as
reflecting the data.

All the thought about the Dlookup and the combo columns did clear my head
for "the obvious answer" to my attempts to access data from two different
tables on one form.

The query.

Thanks for your help with the parameters of the situation.

Richard