Auto Update of fields in secondary form

[Guest]

I want to be able to type in the primary key of a related primary table into
the secondary table and all the information that coincides with that key to
auto update in the secondary table. For example: I have an Event
Informationtable with information about particular events for specific
companies. I have fields such as Company ID, Company name, and Company
Phone. I also have a table called Company Information which contains this
information. When I am in the Event Information table, I want to be able to
type in the Company ID and the other information to update by itself. Is
this possible in Access 2003?

 

[Brian]

I want to be able to type in the primary key of a related primary table into
the secondary table and all the information that coincides with that key to
auto update in the secondary table. For example: I have an Event
Informationtable with information about particular events for specific
companies. I have fields such as Company ID, Company name, and Company
Phone. I also have a table called Company Information which contains this
information. When I am in the Event Information table, I want to be able to
type in the Company ID and the other information to update by itself. Is
this possible in Access 2003?

This is poor data design. You shouldn't duplicate data in more than one
table. The Event Information table should contain the Company ID (a foreign
key), but the other company data should be looked up from the Company
Information table whenever you need to display or report on it. For
example, if you have a form which shows the details of events from the Event
Information table, you could have a text box which has the following
ControlSource:

=Dlookup("[Company Name]", "[Company Information]", "[Company ID]=" &
[Company ID])

This will look up and display the company name without you needing to copy
it to the Event Information table.