Multi-Field Propagation using List or Combo boxes in a Table 1

[pete1505]

I have a table containing several foreign keys from other linked tables. In particular I have a list of countries and their corresponding number that has been assigned to them. I want to set a the "number" field to be a picklist in the datasheet view where the user can click the dropbox arrow and pick which number they want to enter for their new record. I already know how to show two columns of information (I.E. Number and Name) in the dropbox, however, I also have the "country" field right next to the "number" field in my new table andI want the country name to appear in the next column of the datasheet when the corresponding country number is chosen in the first column.

FYI - The Number and Country fields are foreign keys from the same table. The Number field is also part of a concatenated primary key in my new table. However, the other part of this concatenated primary key in the new table will not need to automatically changed if the number field is changed in the new table. I only want the country name field to change to reflect the country number field that was selected.

Is there any way to do this in the design view of my new table, or do I need some VBA code? Any help would be much appreciated!

 

[jjlogan]

Pete- Some thoughts: 1) If you have two fields in a table that are both related to the same country (number and name), then your table is not in normal form for relational database; if true, then remove name field because number identifies the country (name). 2) Consider using a form instead of table datasheet view for user entry. You can create a query (with main table linked to country table / lookup table) as row source to your drop down box and thereby have the columns in the order you want in drop down.
Jeff

 

[pete1505]

jjlogan,

Ahhhh.....normalization. I got so caught up in how my table would look to the users that I forgot that I don't even need that information in this new table. I'll create a view or report later that shows the name with the number. But you are absolutely correct that this would violate normal form and I don't need both fields in this table. Thanks for the 10,000 foot view. I've spent too much time 6 inches away from this stuff lately!