473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

linking a dlookup

135 100+
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
8 1522
nico5038
3,080 Expert 2GB
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
Gilberto
135 100+
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
3,080 Expert 2GB
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
Gilberto
135 100+
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
3,080 Expert 2GB
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
Gilberto
135 100+
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
3,080 Expert 2GB
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
Gilberto
135 100+
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

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

Similar topics

4
by: MLH | last post by:
I have tried using DLookUp in this manner... If DLookUp("","tblClients","='2021234567'") Then MsgBox "Found it!" End If I am wondering if that is a misuse of the DLookUp command? Type...
8
by: Christine Henderson | last post by:
I have a problem using the above function in the following simplified circumstance: In the lookup table called "Klms Travelled" I have 3 fields, eg: Receiver Name Receiver Suburb ...
1
by: hmiller | last post by:
I'm sorry to populate the server with yet another question about linking multiple tables and queries, howerver I have not been able to find the right criteria. My problem. I am trying to...
11
by: MLH | last post by:
DLookup("", "tblPreliminaryVINs", "=Forms!frmVINODO!SerialNum") is giving me a Type Mismatch error. That's confusing to me and I don't know how to circumvent it. The field in...
2
by: Don | last post by:
Can someone help me fix my DLookup problem. I'm far from proficiency with Access. I've been creating databases for several years for work with the help of many of you and trial and error. I have...
17
by: Nitro | last post by:
I'm having problems getting my DB to Link to different SQL servers. I got it to work fine on my own machine, but when distributed it at the client, and to another office, the Link didn't work. It...
9
by: | last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the...
15
by: OzNet | last post by:
I have a query (with calculated fields) in Access (2007) and the data changes depending on the dates and staff person selected. I need to produce a series of graphs based on the data in this query...
15
by: rleepac | last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue This is a table used to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.