473,703 Members | 3,670 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Nov 13 '05 #1
4 3170
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 cboSelectBuildi ng and
cboSelectDivisi on, the code will be something like this:

Me.cboSelectBui lding.RowSource = _
"SELECT BuildingID, Building FROM tblBuilding " _
& "WHERE BuildingID=" & Me.cboSelectDiv ision.Value _
& " ORDER BY Building;"


On Thu, 03 Mar 2005 14:59:18 GMT, "Kathy" <no***@email.ne t> 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

TblSuperviso r
SupervisorID
BuildingID
SupervisorNa me

Relationship s 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.
Nov 13 '05 #2
"Kathy" <no***@email.ne t> wrote in
news:aD******** ******@newsread 3.news.atl.eart hlink.net:
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.

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #3
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.************ *@dial.pipex.co m> wrote in message
news:ao******** *************** *********@4ax.c om...
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 cboSelectBuildi ng and
cboSelectDivisi on, the code will be something like this:

Me.cboSelectBui lding.RowSource = _
"SELECT BuildingID, Building FROM tblBuilding " _
& "WHERE BuildingID=" & Me.cboSelectDiv ision.Value _
& " ORDER BY Building;"


On Thu, 03 Mar 2005 14:59:18 GMT, "Kathy" <no***@email.ne t> 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 hasa Supervisor.

Tables: (abbreviated)

TblDivision
DivisionID
Division

TblBuilding
BuildingID
DivisionID
Building

TblSuperviso r
SupervisorID
BuildingID
SupervisorNa me

Relationship s 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 redundantand also conflicts would be possible between what is entered for DivisionIDand 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 Divisionand 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 unboundcombobox for DivisionID would not be able to show a different DivisionID foreach 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.

Nov 13 '05 #4

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.


On Fri, 04 Mar 2005 17:08:16 GMT, "Kathy" <no***@email.ne t> wrote:
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.************ *@dial.pipex.co m> wrote in message
news:ao******* *************** **********@4ax. com...
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 cboSelectBuildi ng and
cboSelectDivisi on, the code will be something like this:

Me.cboSelectBui lding.RowSource = _
"SELECT BuildingID, Building FROM tblBuilding " _
& "WHERE BuildingID=" & Me.cboSelectDiv ision.Value _
& " ORDER BY Building;"


On Thu, 03 Mar 2005 14:59:18 GMT, "Kathy" <no***@email.ne t> 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 Buildinghas >a Supervisor.
>
>Tables: (abbreviated)
>
>TblDivision
>DivisionID
>Division
>
>TblBuilding
>BuildingID
>DivisionID
>Building
>
>TblSuperviso r
>SupervisorID
>BuildingID
>SupervisorNa me
>
>Relationship s 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 beredundant >and also conflicts would be possible between what is entered forDivisionID >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 aDivision >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. Anunbound >combobox for DivisionID would not be able to show a different DivisionIDfor >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.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
9365
by: B | last post by:
I know there are several ways to speed up combo boxes and form loading. Most of the solutions leave rowsource of the combo box blank and set the rowsource to a saved query or an SQL with a where clause after users typed in one or several letters/digits. My problem is as follows Most of the time I need to display form in continuous format, that means the combo box will appear in each record. For example I have a form to let users view...
5
10115
by: Deborah V. Gardner | last post by:
I would like to use "Yes" and "No" checkboxes on a subform. The problem is that when I click the Yes checkbox on the subform, all of the checkboxes are checked. Currently, I have a field "Refused." It is defined as a text field. A "Y" means that the service has been refused; an "N" indicates it has been accepted. If the field is Null, the question has not been answered, i.e. we don't know if it has been refused or not. I want the user...
3
15405
by: Typehigh | last post by:
I am a good programmer, but this one stumps me! I have a form with a continuous subform. The continuous subform contains records of data and may reach a depth of 1000's of entities. I have created a search button on my form and it does its job quite well. It presents the user with an inputbox, prompting for the string to search. Then the code returns with a record number of the record which contains the string. Here's the problem. I...
11
3560
by: Doug Bell | last post by:
Hi, I am trying to create form that displays data like an Access continuous dataform rather than using a data grid. I am thinking that I can achieve this by creating a row of text boxes, one for each field I need to display. The creating a controls array for the first n records I show and populate the text boxes. Then setting up a vertical scroll bar for the number of records in the data table. As the scroll bar is clicked or dragged,...
20
10819
by: Robert | last post by:
Need some help to stop me going around in circles on this one.... Have a nested subform (subform2) which simulates a continuous form for the record on the parent subform. Subform2 has rows of either an option button plus two text fields or a checkbox plus two text fields Am wanting to save the user entries into an underlying table. Tag property for each option button, check box or text field has the value of the key
1
2776
by: tizmagik | last post by:
I have a combobox on a continuous form that has a recordsource that is set upon Form_Load event via VBA (based on initial form data and external form data entered). For data entry purposes the Combobox's value is saved to *another* field (text-field) in the appropriate table (this text-field is not visible in the Continuous Form); so the combobox is pretty much just for data-lookup purposes (so the user does not have to type anything). ...
2
4447
by: Steve | last post by:
I have a continuous form showing Product Code and Product Name. Product Code is five digits and is sequential. I have a textbox in the form header. What is the code to scroll the continuous form so that as I type in the the first two or three numbers of the product code in the textbox, the first product code record appears at the top of the form. I would like the continuous form to act like a combox with auto expand. Thanks!
5
3414
by: fauxanadu | last post by:
I have a continuous form and a button in the header of the form. When I click the button, the following code is executed: Private Sub cmdTest_Click() Dim objControl As Control For Each objControl In Me.Controls If (objControl.ControlType = acTextBox) Then Debug.Print objControl.Value
8
11648
by: Steffen Beck | last post by:
Hi NG I need some help with a problem on my forms. If I have 2 related tables, for instance companies and employees, and want to display all companies on a continuous form with their employees on a sub form as another continuous form, as shown below
0
8761
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8674
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9262
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9125
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8970
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5923
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4434
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4690
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2070
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.