Jackie:
Assigning values to another control like that is fine if the address in the
referenced table might change, but in the referencing table underlying the
current form you want to keep the 'historic' address at the time when the
record was created (in the jargon of the relational model it’s a question of
'functional dependency'). If however you'd want the address in the form's
underlying table to always reflect the current address then it would
introduce redundancy, so only the foreign key column which references the
primary key of the referenced table would be in the form's underlying table.
You can then show the other columns from the combo box in unbound controls
with a ControlSource such as:
= [LastName].Column(2)
for example, but names make bad keys as they can be duplicated, so a numeric
foreign key such as ContactID, referencing the numeric primary key of the
referenced table should be used, and the combo box based on this, hiding the
bound numeric column by setting its dimension to zero in the ColumnWidths
property.
Even in the first scenario, where you'd want to keep the 'historic' address,
storing the City and State in columns in the table introduces redundancy
unless these are a composite foreign key referencing the composite primary
key of a Cities table, in both cases made up of the City and State columns.
A more usual approach is to have a numeric CityID column in the referencing
table and a numeric CityID primary key of the Cities table, e.g. an
autonumber. The reason for the numeric key is that, like personal names,
city names can be duplicated. The Cities table can contain a State 'natural'
foreign key column referencing the primary key of States of course as state
names (in both full and abbreviated forms) are distinct. If natural keys are
used cascade updates should be enforced in the relationship, as, while its
hard to envisage a state name being changed, it is a theoretical possibility
which a good design should allow for no matter how remote it might seem.
Ken Sheridan
Stafford, England
Jackie said:
I have found an easy way to do this is to have the last name field a combo
box which would be based off the table which has the address information.
Your combo box would need all the fields in the source but you could make the
column width 0" so that the address fields would not be seen. Then, on the
AfterUpdate of the combo box you would have code similar to the following:
Me.Address1 = Me.LastName.Column(2)
Me.City = Me.LastName.Column(3)
Me.State = Me.LastName.Column(4)
etc.
Note that the column numbering starts at zero.
Hope this helps,
Jackie
