Dlookup

[carolinera]

I have a membership form that has all the usual contact information. As we have a lot of members who are from other countries, next to the area code I have a field called "Country Code". I also have a table called "tbl CountryCodes" which lists all the countries that most of our membership use along with the country codes i.e.: England = 44. I have the following dlookup code but in the field I only get the first number on the list for all the members or I get #Error - =dlookup "[Country Code]","tbl CountryCodes","[Country] = & Country"
Why does this happen?

 

[Jack Cowley]

Try:

=DLookup("[Country Code]","tblCountryCodes","[Country] = '" & Me.Country & "'")

I am assuming that you have a field on your form called "Country" and that is why the Me.Country part.

 

[carolinera]

Hi Jack, Thank you for your advice however, in the field I now have #Name? Would it be better for me to try the Select expression instead?
Again, thank you!

 

[Pat Hartman]

Change the form's recordsource query so that it includes a LEFT join (an INNER join will work provided CountryCd is a required field) to the country table. Include the country name field in the Select statement. Set the Locked property of the country name field on the form to Yes since you do not want it to be accidentally updated from this form. Using this method NO code is required. When a country is selected from the combobox, CountryName is automatically changed by Access. This technique is referred to as "AutoLookUp" and you can find it described in help.