Dlookup - probably a basic question

[ferijen]

Hi there

I'm much happier in Excel but have found myself building an Access database. I would like a Form (inputdata) to feed into the table (Maindataset).

Within the form (which will be for non specialist users) I would like a user to select a country from a drop down box. There is another table called 'country & region' and I would like the country selected from the drop down box to auto fill the field on the form (and table) with the associated region. So, user selects UK, region is Europe, user selects Hong Kong, region is China etc.

I'm almost there... I think... but its only returning 'China' as region no matter what country is selected. China is top of the 'country & region' table.

My code is

=DLookUp("[Country]","[Country & region]","'[Country]=[Country]'")

(where the first and last 'Country' comes from the 'Country & region' lookup table... I'm guessing that this ought to have a different name to the 'Country' which goes into the main table, but even when I've changed the names, its not helped)

(changing the code again if I changed the field names, it would be
=DLookUp("[Countrylookup]","[Country & region]","'[Country]=[Countrylookup]'") )

Can anyone help? I'm in access 2003 if it makes a difference.

 

[Swift_74d]

I'm not sure how 03 works, but you may need to but an embedded macro on the dropdown to do something like requery after update.

 

[Montez659]

Swift, when i first read this post, I thought the problem was the DLookup. I just reread the OP and see that a better solution might be cascading comboboxes (should work with listboxes as well if you need to). Check this post and the link in it out to see if this is what you are trying to do.

http://mymsaccessblog.blogspot.com/2008/03/how-to-synchronize-two-combo-boxes-on.html

 

[ferijen]

Since my last message, a colleague has come in who knows nothing about DLookup but has set me up with multiple combo boxes, so it does what it needs to do...

Thank you (sorry I couldn't get the Dlookup working though!)