By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,479 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

linking a dlookup

100+
P: 135
Hello,

I have one table:
ENGINEERING, with fields
engineeringid
product name french
plm

and a form:
COSTING, with
textbox: plmtxt
combobox: product name french

Products are ALREADY stored in the engineering table.

Im trying to setup the textbox in the form with a dlookup so after selecting a product from the combobox it will display its corresponding PLM in the textbox (from the table). So far it ONLY displays the plm of the first product on the table and it doesnt change when you select a different product (in the form).

Ive tried:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[PLM]","Engineering","[product_name_french]=" & forms![costing]!product_name_french)
and all sorts of variations around this with no luck.

Please help,
Gilberto
Oct 9 '07 #1
Share this Question
Share on Google+
8 Replies


nico5038
Expert 2.5K+
P: 3,072
A combobox will need a unique identifier in the Bound column (See the properties under the data tab).
When translating a code, normally the unique code is hidden and only the description visible.

Getting the idea ?

Nic;o)
Oct 9 '07 #2

100+
P: 135
A combobox will need a unique identifier in the Bound column (See the properties under the data tab).
When translating a code, normally the unique code is hidden and only the description visible.

Getting the idea ?

Nic;o)
Thanks for the reply Nico.

I checked that and the combo is bound to column 1 which is indeed PRODUCT NAME FRENCH.

I tried changing that with no luck...any ideas??
Oct 10 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Hmm, a normalized productdescription table with ProductCode as unique identifier and with fields for Language (Holding "French" or "English") and the Description would be the "normalized" way to work. This way you could also add "German", or other languages in the future.
For the DLOOKUP() you have posted, that won't work as I assume the field is of the text datatype and thus needs to be surrounded by single quotes like:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[PLM]","Engineering","[product_name_french]='" & forms![costing]!product_name_french) & "'"
  2.  
Nic;o)
Oct 10 '07 #4

100+
P: 135
Hmm, a normalized productdescription table with ProductCode as unique identifier and with fields for Language (Holding "French" or "English") and the Description would be the "normalized" way to work. This way you could also add "German", or other languages in the future.
For the DLOOKUP() you have posted, that won't work as I assume the field is of the text datatype and thus needs to be surrounded by single quotes like:
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[PLM]","Engineering","[product_name_french]='" & forms![costing]!product_name_french) & "'"
  2.  
Nic;o)
Thanks Nico.

The lookup is still not working. It just shows a #ERROR.

I tried with a subform and that DOES WORK...more or less. It works as it does display the PLM, BUT only AFTER the product selected by the combo has been STORED in the COSTING table...which is NOT what i need. I need it displayed just when selected in the combo (so that the user can "review" the products PLM BEFORE adding additional info and SAVING the record.)

So i think i still want to go with the dlookup if you could think of something else...

In the subform link properties i have:
CHILD: Product Name French
MASTER: Costing.Product Name French

i dont know if this can halep...

Thanks again,
Gilberto
Oct 10 '07 #5

nico5038
Expert 2.5K+
P: 3,072
Oops, I see a typo, try:
=DLookUp("[PLM]","Engineering","[product_name_french]='" & forms![costing]!product_name_french & "'")
when single quotes can be part of the productname use:
=DLookUp("[PLM]","Engineering","[product_name_french]=" & chr(34) & forms![costing]!product_name_french & chr(34))

Nic;o)
Oct 10 '07 #6

100+
P: 135
Oops, I see a typo, try:
=DLookUp("[PLM]","Engineering","[product_name_french]='" & forms![costing]!product_name_french & "'")
when single quotes can be part of the productname use:
=DLookUp("[PLM]","Engineering","[product_name_french]=" & chr(34) & forms![costing]!product_name_french & chr(34))

Nic;o)
Still no luck Nico, but i think i will stick to the SUBFORM which actually works kind of good.

Thanks for your time and effort, i would appreciate if you could continue helping me with my other threads.

Gilberto
Oct 10 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Hmm, always intrigued when something doesn't work that should.
I saw in your table description that the "product name french" holds spaces, but your Dlookup has underscores ( _ ). I never use spaces in names, I just capitalize the parts like: ProductNameFrench, thus saving the effort to put brackets...
Just try:
=DLookUp("[PLM]","Engineering","[product name french]=" & chr(34) & forms![costing]![product name french] & chr(34))
when the name holds spaces, or start removing the spaces (and change the dlookup accordingly.

Nic;o)
Oct 10 '07 #8

100+
P: 135
Hmm, always intrigued when something doesn't work that should.
I saw in your table description that the "product name french" holds spaces, but your Dlookup has underscores ( _ ). I never use spaces in names, I just capitalize the parts like: ProductNameFrench, thus saving the effort to put brackets...
Just try:
=DLookUp("[PLM]","Engineering","[product name french]=" & chr(34) & forms![costing]![product name french] & chr(34))
when the name holds spaces, or start removing the spaces (and change the dlookup accordingly.

Nic;o)
You did it again my friend. haha It works now...thnks.

I always thought that the ( _ ) actually replaced the spaces...Now i know, and i will definitely stop using them when naming stuff.

Thanks again,
Gilberto
Oct 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.