Base several fields on 1 field in a form?

[Guest]

I would like a user to select one combo box and have the remaining combo
boxes in the Form reflex the change. Right now, I have several combo boxes on
the Form and a user must select each combo box. The source information is in
3 tables: Organization Number and Department fields are in the Organizational
Structure table, Crew field is in Crew List table and Location field is in
Location List table.

The Organization Number field is unique. If I know, let's say, that an
employee is in Organization Number 0400, I know his Crew, Department and
Location.

Thanks!

 

[Rick Brandt]

I would like a user to select one combo box and have the remaining combo
boxes in the Form reflex the change. Right now, I have several combo boxes on
the Form and a user must select each combo box. The source information is in
3 tables: Organization Number and Department fields are in the Organizational
Structure table, Crew field is in Crew List table and Location field is in
Location List table.

The Organization Number field is unique. If I know, let's say, that an
employee is in Organization Number 0400, I know his Crew, Department and
Location.

If that is the case then you don't need to copy and store any of those
other fields. Store ONLY the Organization Number and use lookup methods to
*display* the other data without copying it. That is the proper way to use
related tables.

 

[Steve Schapel]

John,

It seems to me that you need to make up a master table that shows the
relationship between Crew, Department and Location. If I understand you
correctly, you should be able to put fields for Crew and Location into
your Organizational Structure table.

Whatever the table that the form is based on, it is not clear to me
whether you have Crew, Department and Location fields in this table. If
so, it is almost certainly an incorrect design to do so, and you should
remove these fields altogether. The form can display this data by way
of reference to the Organizational Structure table.

There are 2 commonly used approaches to this....
1. Make your form based on a query which includes the existing main
table, plus the Organizational Structure table, joined on the
Organization Number field. This means you can include the Crew,
Department and Location fields into the query, and hence onto the form.
If so, whenever you enter an Organization Number, the corresponding
Crew, Department and Location will automatically be shown. In this
case, it is advisable to set the Enabled property of the Crew,
Department and Location controls (should be textboxes, not comboboxes)
on the form to No, and their Locked property to Yes.
2. Make the combobox for Organization Number a multi-column combobox,
with its Row Source of course set to the Organizational Structure table,
and then use unbound textboxes for the Crew, Department and Location,
with their Control Source properties set to, for example...
=[Organization Number].[Column](1)