using lookup tables

[Qamar]

when using lookup tables do i have to link the the autonumber or the field. ex.

tblCountry
CountryID
Country

tblPerson
personID
firstname
lastname
country ... when i select lookup as datatype, and i follow the wizard, and i select tblCountry, then do i have to choose CountryID or Country as the lookup ?

Thx.

 

[monkeytunes]

Hey Qamar,

What you're using isn't a "lookup table", it's a "lookup field". Lookup Fields are generally frowned upon, as they're not very stable, they're tough to update if/when your DB structure changes, and they're not upgradeable if you have to bump up to a different database type than Access someday. You can read more here: http://www.mvps.org/access/lookupfields.htm

HOWEVER, "lookup tables" are very handy, but it's tough to get your mind around them until you get into queries and especially forms.

In your case, you'd have a "CountryID" field in your tblPerson tables, as a number datatype. In the Relationship window, you'd link the "CountryID" field from "tblCountry" to "CountryID" field in tblPersons, and Access should make automatically make it a 1-to-Many link.

 

[Qamar]

thank you for the reply and the link.

Usually, when i will be using a combo box on a form, for example to input the country, i like creating a Country table with just 2 fields countryid and country and then in the main table when i create the country field, i will make lookup wizard as datatype and select the country.

then when i am in the combobox on the form, i use the onNotInList event to add more countries to the table.

Is this a wrong way of doing things, because i use it often ?

Thx

 

[monkeytunes]

Usually, when i will be using a combo box on a form, for example to input the country, i like creating a Country table with just 2 fields countryid and country and then in the main table when i create the country field, i will make lookup wizard as datatype and select the country.

That's how I do it...as long as your countryID is what's being stored in the other table (Foreign Key), and not the actual name.

then when i am in the combobox on the form, i use the onNotInList event to add more countries to the table.

You know, I've never tried that. I'll have to give it go. In my case, I've always made special pop-up forms for users if they want to change/add/edit lookup tables. I always figured that if someone wanted to change something like tblCountry or tblLocations or what-have-you, that it was a rare and special administrative task, and that they should have to go to a rare and special form - this prevents users from rushing through data entry (like they do on familiar forms they use every day).