Excluding previous choices from dropdowns based on a table.

Have attached a sample database. When you click on the main switchboard - click on Batch. This opens the Batch form which is based on the Heat treat table. How can I modify the dropdowns in this form so that when Workorder 1 is selected - it offers in the combo box a list of all the workorders that relate to that workcentre? Then when the user moves to Workorder2, the dropdown they select will only reflect the workorders that are left e.g. if I have workcentre 123. That has 5 workorders: 10,11,12,13,14. When I choose 10 from this dropdown in workorder 1, the only choices that are left in workorder 2 are: 11,12,13,14. And when I go onto Workorder 3, the only choices I have left are what is left i.e. 12, 13, 14.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
<fyi:, SFDLRAW is a linked table that we do not have Access to, ...so you may wish to attache a new sample db, with this as a local table>

You have a lot of design issues with this database.

You might consider cleaning up those issue first, ...before worrying about forms...
1. The HeatTreat table has no primary key
2. It also seems to have an "un-normalized" design...
3.  The HeatTreat table and the Batch table appear to have similar structures, ...can you explain?
4. You have no relationships established between the tables,..so it is difficult to see how they fit together

To me, this interface would be better if you used checkboxes instead of comboboxes.
This way the user has an obvious visual cue of what is already selected.
Show all the choices, each with a checkbox, ...then the user can select the ones they want.
Then it will be obvious what choices are left.

But again, I think you should have a closer look at your design here, ...and post a sample with SFDLRAW as a static table, ...so we can evaluate further...


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
agwalshAuthor Commented:
Take your point on board. I did ask about the normalised design. Apparently there will only ever be 5 choices so that's why it's done that way.  I also noticed the batch and heat treat stuff. Apparently the reason why the combo boxes were chosen was because they were to be prioritised i.e work order 1 to be done before work order 2. I have already suggested to him that maybe he add an extra field for each one e.g. work order 1 combo box has a work order 1 field assigned a default value of 1. But will follow up on the raw data question. Thanks for your help so far.
Jeffrey CoachmanMIS LiasonCommented:
But, FWIW, ...what you are asking for seems like a variation on "Cascading Comboboxes"

So have a look here at this popular resource:
agwalshAuthor Commented:
thanks for help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.