Populate two fields with one combo box

Question

I want to populate two fields based on the result of a combo box. The combo box looks up a value from a master file and I want to be able to capture (and output to a record in a different table) both the ID and the name that is in the master file.

Master Table: List of Sources

      ID

      Name

Output table

     ID

     Name

     other date input on the form

Is this enough information?

Thanks, 

Merrikay

Answer 1

As John says, you almost certainly should not store the Name in the output table, as this would introduce redundancy and the table would not be normalized to Third Normal Form (3NF) due to the transitive dependency of the Name column on the key.  The table would consequently be open to the risk of update anomalies.

In your form set up a combo box bound to the foreign key ID column as follows:

ControlSource:   ID

RowSource:     SELECT ID, [Name] FROM [Master Table] ORDER BY [Name];

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

If you are unfamiliar with the principles of Normalization you might like to take a look at Normalization.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

BTW, I hope that Name is not the actual name of a column.  As the name of a built in property in Access it is a reserved keyword, and should not be used as an object name.

Answer 2

Well, you probably DON'T want to do this.

Relational databases (like Access) use the "Grandmother's Pantry Principle": "A place - ONE PLACE! - for everything, everything in its place". The Name field should exist once, and once only, in the master Names table (and it should not be named "Name", since Name is a reserved word for the Name of the table, the Name of the field, the Name of the form etc.) You should store the name once, and then use Queries, Combo Boxes, or the many other tools Access provides to link to and display it when it's needed. Just store the ID in the output table.

Answer 3

Thanks for the reminder John and Ken. I'll rethink my design.