Multiple tables within form

[dab1477]

Thanks in advance for any direction you can provide.
I've created a form to capture hourly production from 4 different
departments. The form was created to allow each department to select their
department name from a combo list box - as I am using the SAME form to input
data from all 4 departments. I would like to have a 2nd combo box view the
list of machines based upon the entry selected in the first box.

Ex: Upon entering Combo Box 1, the following selections appear; Drill,
Grinder, Shaver, Punch Press. If I choose Drill, I want the table associated
with Drill Machine Numbers to appear in Combo Box 2. If I choose Grinder in
Combo Box 2, I would like the table associated with Grinder Machine Numbers
to appear in Combo Box 2. The idea is that for the department selected in
Combo Box 1, an associated list of machine numbers appear in combo box 2.

I have created TABLES for each department's machine numbers. I am unable to
code such that I can select the table required based upon the selection in
combo box 1. Is this EVEN possible? If so, could you please propose an
alternative idea?

Thanks,
DAB1477

 

[Arvin Meyer [MVP]]

If I'm reading you correctly, you want a drill-down set of combo boxes.
Here's a sample with list boxes, but combos will work just as well:

http://www.accessmvp.com/Arvin/Combo.zip

 

[Tom Wickerath]

Hello DAB1477,

Ex: Upon entering Combo Box 1, the following selections appear; Drill,
Grinder, Shaver, Punch Press. If I choose Drill, I want the table associated
with Drill Machine Numbers to appear in Combo Box 2.
and

I have created TABLES for each department's machine numbers.

It sounds to me like you have not created a properly normalized database
design. You should not have separate tables for each department; instead, all
machine numbers should be entered into the same table. Add a field to allow
you to track the department. This field will likely end up being a foreign
key to a table of Departments, such that "one department can have many
machines" (a one-to-many [1:M] relationship).

Once you have a proper database design, then you can implement the idea of
synchronized combo boxes:

How to synchronize two combo boxes on a form
http://support.microsoft.com/?id=289670

Note: This KB article applies equally well to other versions of Access, so
ignore the part that reads "in Access 2002 or in Access 2003" in the title of
this KB article.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________