One Combo Box and Another

[GendoPose]

Hi all.

I would like to know how to make a combobox select a specific value based on another combo box's selection.

Basically, I have a box called Production Department and one called Machine. In the Production Department there are things like Machine, Bench, Fulfilment, etc and the Machine there is FPS1, FPS2, CMC1, etc.

When we choose Machine in the Production Department, we must then assign the job to specific machine, e.g FPS1. However I want the form to show Bench (which is included in the list of possible choices) in the Machine combobox when Bench is chosen in the Production Production department combobox, but I still want freedom of choice if anything other than Bench is selected.

Any ideas?

Thanks guys.

 

[pr2-eugin]

What you are looking for is called as Cascading ComboBoxes. Several example should be floating on the Internet, this is one of my favorite. http://www.fontstuff.com/access/acctut10.htm

 

[GendoPose]

Thanks for that, but I can't for the life of me figure this out. I have a Single Row Source table, and I've entered in the code exactly as the site says, substituting the names for my comboboxes and tables, yet I get an error saying the record source doesn't exist?

If it helps, my Row Source table is called "ENCLOSING MACHINES" with columns; "ID", "Enclosing Machine" and "Type".

The combo boxes in my form are called "PRODUCTION DEPT" and "MACHINE"

Thanks

EDIT: I'm not entirely sure this is the right way to do it, because the choice of Production Department comes from a different table to the choice of Machine. Production Deparment has it's own seperate table which the combo box chooses from, as does Machine.

Ideally, I'd like a simple VBA code that says If Production Department is "BENCH" then Machine chooses "BENCH", but it's a combo box so you can't just enter in a value like that I believe.

 

[pr2-eugin]

I have a Single Row Source table,

This line intrigues me. Can you please Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[GendoPose]

The form is called "Planner Query" and I want it so when "BENCH" is chosen in PRODUCTION DEPARTMENT, "BENCH" should show in MACHINE, but if the production department is anything else, then I want MACHINE to be my choice.