Using Combobx In Continuous Form

[Kathy]

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]

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;"

 

[Bob Quintal]

What is the standard technique for handling the fields in the
following scenario 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

The recordsource for the form should be a query that contains the
relevant data, instead of just the table. You may need to change
the recordsettype property to Dynaset (incosistent updates, but
since you are only looking up the values, that's not a problem.

 

[Kathy]

John,

Thank you for your response!

I have to aplogize. I was trying to create a scenario to illustrate my
problem. Consider that there are multiple supervisors for each building
making TblSupervisor necessary for a 1 - m relationship with TblBuilding.
Also consider that the continuous form my question is about is a subform.
What I want to know is:
1. Should TblBuilding have a field DivisionID so there can be cascading
comboboxes to first select the division and then select the building
2. How to set up cascading comboboxes so that each record can display a
different division and different building.

The continuous form will have the fields:
DivisionID BuildingID SupervisorID
Where all three are comboboxes and DivisionID limits the selections in
BuildingID.

Kathy

 

[John Nurick]

Kathy,

I remain unsure about what you're trying to do.

1. Should TblBuilding have a field DivisionID so there can be cascading
comboboxes to first select the division and then select the building

If each building is in one and only one division, TblBuilding should
include DivisionID.

2. How to set up cascading comboboxes so that each record can display a
different division and different building.

The continuous form will have the fields:
DivisionID BuildingID SupervisorID
Where all three are comboboxes and DivisionID limits the selections in
BuildingID.

It sounds as if you want a situation where the user can create a new
record, select a Division from the first combobox, select a Building
from a filtered choice in the second combobox, and assign a Supervisor
to the building by selecting from the third combobox.

This is a less simple scenario that it seems because of the way Access
controls work. On a continuous form it appears that each visible record
has its own set of controls. In fact (for good historical reasons) they
are fakes. Only the controls in the current record are "real" (or
rather, behave as if they are). The rest are merely pictures of how the
control in question would look if it contained the data in the record in
question.

A side-effect of this is most of the control properties, including a
combobox's RowSource, don't change unless the current record changes.
This means that if you filter the RowSource to suit the current record,
the same filter applies to all other visible records - so if you filter
for one Division, records relating to other divisions won't be displayed
correctly.

One approach would be to filter the Building combobox's RowSource in the
AfterUpdate event of the Division combobox ("filter" by assigning an the
relevant SQL statement as in my previous post), and then remove the
filter in (a) the AfterUpdate event of the Building combobox and (b) the
subform's Current event.

John,

Thank you for your response!

I have to aplogize. I was trying to create a scenario to illustrate my
problem. Consider that there are multiple supervisors for each building
making TblSupervisor necessary for a 1 - m relationship with TblBuilding.
Also consider that the continuous form my question is about is a subform.
What I want to know is:
1. Should TblBuilding have a field DivisionID so there can be cascading
comboboxes to first select the division and then select the building
2. How to set up cascading comboboxes so that each record can display a
different division and different building.

The continuous form will have the fields:
DivisionID BuildingID SupervisorID
Where all three are comboboxes and DivisionID limits the selections in
BuildingID.

Kathy