Prevent duplicate forms

[Steven P via AccessMonster.com]

Hello,
I am constructing a database for a plant operation. Each department will
have identical inputs with the exception of the machine number. I first
constructed a form with a dropdown list of machines for the first department
and realized I would have to duplicate the form and table for each of the
other depts. How can I use the single form while being able to distinguish
the machine in several departments? A dropdown list of all machines combined
would be in excess of 300.

Thanks,
Steven

 

[Jeff Boyce]

Steven

Are you saying that each department has different machines (numbers)?

If you add [Department] to your data, you could add a combo box on your form
for selecting [Department]. Then, in the AfterUpdate event of that combo
box, you could requery the list of machines in a second combo box. If your
underlying query for that second combo box includes a selection criterion
that points to the first, you've effectively filtered the second dropdown.

Check for "Cascading Combo Boxes" at mvps.org or via Google.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Steven P via AccessMonster.com]

Jeff,
Thanks. Yes different machine numbers, as in 23, 1565, etc. In addition to my
first statement, if I add a form which specifies the department, when
selected the form is opened for machine/data input, is there a way to link
that decision to the proper machine listing for the different departments?
Your first solution will fix my problem, this may be a better way around it
if it's possible.

Thanks,
Steven

Steven

Are you saying that each department has different machines (numbers)?

If you add [Department] to your data, you could add a combo box on your form
for selecting [Department]. Then, in the AfterUpdate event of that combo
box, you could requery the list of machines in a second combo box. If your
underlying query for that second combo box includes a selection criterion
that points to the first, you've effectively filtered the second dropdown.

Check for "Cascading Combo Boxes" at mvps.org or via Google.

Regards

Jeff Boyce

Hello,
I am constructing a database for a plant operation. Each department will

[quoted text clipped - 9 lines]

Thanks,
Steven

 

[Guest]

Jeff gave you the best and correct answer. This is a very normal practice in
Access. What he described is known as Cascading Combo Boxes.

You need only one form for all departments. In addition to your current
machince combo, you need a department combo. The department combo should be
the first control on your form. Once the user selects a department, the list
of machines will be limited to those machines for the selected department.

The technique is to use the After Update event of the department combo to
filter the row source for the machine combo.

So you could do something like this:

Me.cboMachines.RowSource = _
"SELECT tblMachines.machine FROM tblMachines WHERE dept = '" & _
Me.cboDept & "';"
Me.cboMachines.Requery

Jeff,
Thanks. Yes different machine numbers, as in 23, 1565, etc. In addition to my
first statement, if I add a form which specifies the department, when
selected the form is opened for machine/data input, is there a way to link
that decision to the proper machine listing for the different departments?
Your first solution will fix my problem, this may be a better way around it
if it's possible.

Thanks,
Steven

Steven

Are you saying that each department has different machines (numbers)?

If you add [Department] to your data, you could add a combo box on your form
for selecting [Department]. Then, in the AfterUpdate event of that combo
box, you could requery the list of machines in a second combo box. If your
underlying query for that second combo box includes a selection criterion
that points to the first, you've effectively filtered the second dropdown.

Check for "Cascading Combo Boxes" at mvps.org or via Google.

Regards

Jeff Boyce

Hello,
I am constructing a database for a plant operation. Each department will

[quoted text clipped - 9 lines]

Thanks,
Steven