Combo Box Form Question

[ezykiwi]

Howdy Everyone,
Im just new to using access, so hope someone might be able to point me in the right direction.

Ive got 2 tables, ive used the form wizard to help set up a form for one of the tables, so I can input data, which works ok, but for one of the fields in the form I need to use values from another table,

the other table has information structured like this..

ID | NAME

1 | City1
2 | City2
3 | City3 etc etc

What Id like to be able to do is have a drop down list/combo box, so that it shows the Names of the Citys, but when the name is selected.... its ID number is put into that variable in the form....

so if someone clicks on City3, 3 would be its vaule,

Ive tried playing around with quieries and drop down lists etc, but it seems to want to show both the ID and Name in the drop down list, and I really only want it to show the name.

Any Ideas on what I should be doing? Oh, and im using Access 2003

Cheers Ezy

 

[ansentry]

I think this is what you want.

You will notice that in the table TblCustomers only the key from the tblCity is recorded (as it should be)

Good Luck with your project.

 

[ezykiwi]

Wow, Thanks very much mate, thats perfect,
I used the example from you and played around with it and now have it working brilliant now the next step is to have cascading ones, but ill do abit of research thru the forum first, I notice there is alot to read so that should get me started,

Cheers once again
Long live the ANZACS

 

[ezykiwi]

oh i do have a quick question,

Is it using the ID number because its the Key ?
What if I needed to use a value that wasnt a primary key?

Just wondering for down the line....

 

[ansentry]

Is it using the ID number because its the Key ?

In my example yes

What if I needed to use a value that wasnt a primary key?

Why would you want to do that?

Have a look at the UPDATED sample attached to this and you will see how linking tables with keys work.

Open the table tblCity and you will see that the CityID is stored in the customer table under fkCityID (look at the design view also).

Now run the query qryDataLink and you will see information that looks the same as the form. Open the query in design view and you will see how the two tables link to produce the data.

Long live the ANZACS
And they will for ever and a day!