combo box question

[Megatron-Lives]

my db is a little more involved, but I am using this for an example

i have two tables.
One table is Books, and contains the fields Title and AuthorID

The second table is Authors, and contains fields AuthorID, AuthorName, AuthorAddress.

I am making a form to enter books into the Books table.
'Book' is just a regular data entry box.
But I want 'AuthorID' to show a list of all the AuthorNames from the Authors table, so they can choose the AuthorName, but then have it enter the AuthorID for that AuthorName.

I used a combo box, and setting the Row Source, I can make it show a list of AuthorNames, and when I select an AuthorName, it posts that AuthorName to the record, but for normalization's sake, I want the AuthorID in the Books table, not the AuthorName.

How do I make the combo box show the AuthorNames in the list but when you select one, it actually enters the AuthorID linked to that AuthorName?

 

[SydneyGeek]

If you use the Wizard this happens automatically. Otherwise --
Go to the Properties of the Combo and edit its Rowsource. By the sound of things you probably have AuthorName as the sole field -- is that correct? If so, do these steps:
1. Drag AuthorID on top of AuthorName so you now have AuthorID and AuthorName, in that order. Close the query, save when prompted.
2. Make sure you are in the ALL properties tab or you won't see some of these properties: ColumnCount should be 2 (or the total # of fields in the query). ColumnWidths should be 0;3 (this hides the ID field). BoundColumn should be 1 (the ID field). The ControlSource should be AuthorID.

Save and check it out.

Denis