Hi Kathy,
I don't understand your structure, in particular the relationsihp
between buildings and supervisors.
If each building has one supervisor and each supervisor supervises one
building, you can just include supervisor fields in the Building record.
If each building has one supervisor and each supervisor may supervise
more than one building, you have a 1:M relationship between supervisors
and buildings, which would be implemented with a tblSupervisor and a
SupervisorID foreign key in tblBuilding.
Your structure has it the other way round, with a 1:M relationship
between buildings and supervisors: each supervisor supervises one
building, and each building can have multiple supervisors. Is that what
you intend?
If so, think in terms of a main form (in form view) bound to
tblBuildings, with a subform (in continuous view) bound to
tblSupervisor, linked on BuildingID.
Once that's working, make sure the "magic wand" button on the toolbox is
pressed, and put a combobox on the main form (probably in its header).
In the wizard, choose "Find a record on the form based on the value I
select...", and set it up to find one building from a list of all
buildings, using the BuildingID and Building fields.
When that's working, add a second combobox, without using the wizard.
Set its RowSource set to something like this:
SELECT DivisionID, Division FROM TblDivision ORDER BY Division;
and its RowSourceType to Table/Query, 2 columns, Bound Column 1, and
column width property set to 0.
Then put code in its AfterUpdate event procedure to alter the RowSource
of the first combobox to include only buildings in the selected
division. If the combos are called cboSelectBuilding and
cboSelectDivision, the code will be something like this:
Me.cboSelectBuilding.RowSource = _
"SELECT BuildingID, Building FROM tblBuilding " _
& "WHERE BuildingID=" & Me.cboSelectDivision.Value _
& " ORDER BY Building;"
On Thu, 03 Mar 2005 14:59:18 GMT, "Kathy" <no***@email.net> wrote:
What is the standard technique for handling the fields in the following
scenario on a continuous form?
Multiple Divisions. Each Division has multiple Buildings. Each Building has
a Supervisor.
Tables: (abbreviated)
TblDivision
DivisionID
Division
TblBuilding
BuildingID
DivisionID
Building
TblSupervisor
SupervisorID
BuildingID
SupervisorName
Relationships exist between the appropriate primary and foreign keys.
The data entry/edit form for Supervisor will be a continuous form.
BuildingID through the relationships defines a specific building in a
specific division. Including DivisionID in TblSupervisor would be redundant
and also conflicts would be possible between what is entered for DivisionID
and what is entered for BuildingID. If a combobox based on TblBuilding is
used alone, the drop down list will include every building in all the
divisions. Somehow it seems there should be a combobox to select a Division
and a combobox to select a
building in the selected division and then only the buildings in the
selected division would appear in the BuildingID drop down list. An unbound
combobox for DivisionID would not be able to show a different DivisionID for
each record on a continuous form.
Q1 Should DivisionID also be included as a field in TblSupervisor?
Q2 What is the standard technique for handling BuildingID (and
DivisionID???) on a continuous form?
Thanks for all input!!
Kathy
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.