Using Combobx In Continuous Form

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

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" <notmy@email.net> wrote:
[color=blue]
>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
>
>[/color]

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

"Kathy" <notmy@email.net> wrote in
news:aDFVd.258$CW2.90@newsread3.news.atl.earthlink .net:
[color=blue]
> What is the standard technique for handling the fields in the
> following scenario on a continuous form?[/color]
[color=blue]
> 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
>[/color]
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.

--
Bob Quintal

PA is y I've altered my email address.

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" <j.mapSoN.nurick@dial.pipex.com> wrote in message
news:aote21hg8em808ee7qs9nr0re4sd4kqgcg@4ax.com...[color=blue]
> 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" <notmy@email.net> wrote:
>[color=green]
> >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[/color][/color]
has[color=blue][color=green]
> >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[/color][/color]
redundant[color=blue][color=green]
> >and also conflicts would be possible between what is entered for[/color][/color]
DivisionID[color=blue][color=green]
> >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[/color][/color]
Division[color=blue][color=green]
> >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[/color][/color]
unbound[color=blue][color=green]
> >combobox for DivisionID would not be able to show a different DivisionID[/color][/color]
for[color=blue][color=green]
> >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
> >
> >[/color]
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.[/color]

Kathy,

I remain unsure about what you're trying to do.
[color=blue]
>1. Should TblBuilding have a field DivisionID so there can be cascading
>comboboxes to first select the division and then select the building[/color]

If each building is in one and only one division, TblBuilding should
include DivisionID.
[color=blue]
>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.[/color]

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.




On Fri, 04 Mar 2005 17:08:16 GMT, "Kathy" <notmy@email.net> wrote:
[color=blue]
>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" <j.mapSoN.nurick@dial.pipex.com> wrote in message
>news:aote21hg8em808ee7qs9nr0re4sd4kqgcg@4ax.com.. .[color=green]
>> 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" <notmy@email.net> wrote:
>>[color=darkred]
>> >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[/color][/color]
>has[color=green][color=darkred]
>> >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[/color][/color]
>redundant[color=green][color=darkred]
>> >and also conflicts would be possible between what is entered for[/color][/color]
>DivisionID[color=green][color=darkred]
>> >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[/color][/color]
>Division[color=green][color=darkred]
>> >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[/color][/color]
>unbound[color=green][color=darkred]
>> >combobox for DivisionID would not be able to show a different DivisionID[/color][/color]
>for[color=green][color=darkred]
>> >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
>> >
>> >[/color]
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.[/color]
>[/color]

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.