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

DLookup Syntax

P: 44
I have a form called BasicInfo.
On the form there is a field called text box called "County Name" and a ComboBox called "County Code"
There is a table called "CountyCodes" that has two fields; they are "County Code" and "County Name"
The combo box allows you to pick a county from a list tied to the "County Codes" table, and stores the "County Code" in the ComboBox field.
I want it to then automatically put the county name into the "County Name" text box.
I use the following syntax and I get no error message, but nothing happens. What is wrong with my syntax?

[County Name] = DLookup("[County Name]", "CountyCodes", "[County Code]=[Forms]![BasicInfo]![County Code]")
Oct 4 '07 #1
Share this Question
Share on Google+
2 Replies


Jim Doherty
Expert 100+
P: 897
I have a form called BasicInfo.
On the form there is a field called text box called "County Name" and a ComboBox called "County Code"
There is a table called "CountyCodes" that has two fields; they are "County Code" and "County Name"
The combo box allows you to pick a county from a list tied to the "County Codes" table, and stores the "County Code" in the ComboBox field.
I want it to then automatically put the county name into the "County Name" text box.
I use the following syntax and I get no error message, but nothing happens. What is wrong with my syntax?

[County Name] = DLookup("[County Name]", "CountyCodes", "[County Code]=[Forms]![BasicInfo]![County Code]")

Assuming your are placing what you wish to do in the afterupdate event of the combobox then the syntax is

Me![text box] = DLookup("[County Name]", "CountyCodes", "[County Code]='" & [Forms]![BasicInfo]![County Code] & "'")

Some advice... pay attentiion to field and control naming conventions. Spaces and the like become a pain to read where there is a space and not and vice versa

Incidentally why are you populating this way why not LEFT JOIN in a query between the table servicing your form and the county code table and then drag the County name to the query grid and use either an SQL statement as the recorsource for the form or a saved query. Access will AUTOLOOKUP the county name for you

As it is it seems to me you are breaking normalisation principles and creating redundancy potential there, because county name 'on its own' in your BasicInfo table will be 'standing alone' and totally reliant on any changes from a pick value from the drop down. Not 'efficient' should the County codes ever get changed on masse. County name will not be 'updated' as it is 'on its own'....if you understand me and if I am reading your situation correctly.

Regards

Jim
Oct 4 '07 #2

P: 44
Assuming your are placing what you wish to do in the afterupdate event of the combobox then the syntax is

Me![text box] = DLookup("[County Name]", "CountyCodes", "[County Code]='" & [Forms]![BasicInfo]![County Code] & "'")

Some advice... pay attentiion to field and control naming conventions. Spaces and the like become a pain to read where there is a space and not and vice versa

Incidentally why are you populating this way why not LEFT JOIN in a query between the table servicing your form and the county code table and then drag the County name to the query grid and use either an SQL statement as the recorsource for the form or a saved query. Access will AUTOLOOKUP the county name for you

As it is it seems to me you are breaking normalisation principles and creating redundancy potential there, because county name 'on its own' in your BasicInfo table will be 'standing alone' and totally reliant on any changes from a pick value from the drop down. Not 'efficient' should the County codes ever get changed on masse. County name will not be 'updated' as it is 'on its own'....if you understand me and if I am reading your situation correctly.

Regards

Jim
Thanks for your answer on this. I will experiment with changing my approach the way you describe.
Oct 18 '07 #3

Post your reply

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