cascading combo boxes - I'm missing something simple here!

Hi Folks
I've been experimenting with cascading combo boxes and using this link as a reference.
http://www.access-programmers.co.uk/forums/showthread.php?t=223980&highlight=cascade

In the attached database (Frm_workcentre_only) , the first combobox (cbo_work_order_01) works fine. It just filters for the correct work orders for that work centre. However when I get to Work order 2, I want that combo box (which is based on Qry_cbo_workOrder_02 which is to exclude the choice made in cbo_work_order_01 does not exclude the choice.  For example if I choose 642642001 as my work centre number, I get three choices in WorkOrder1. If I choose 609120 in WorkOrder1, I want to have only the other two numbers available to me in Workorder2. Alas, I'm getting all three. As per the link above, this is what I have done...
In the AfterUpdate event of WorkOrder 1 I have entered the following code:
Private Sub cbo_work_order_01_AfterUpdate()
Me.cbo_work_order_02.Requery
Me.cbo_work_order_03 = Null
Me.cbo_work_order_04 = Null
Me.cbo_work_order_05 = Null
End Sub

In the AfterUpdate event of WorkOrder 2 I have entered the following code..
Private Sub cbo_work_order_02_AfterUpdate()
Me.cbo_work_order_03.Requery
Me.cbo_work_order_04 = Null
Me.cbo_work_order_05 = Null
End Sub

I keep feeling as though I am missing something really simple here and I  just need fresh eyes! If I can get this one working, I'll then have a way to get the other combo boxes working :-) thanks again
Workorder_database_vers_06.accdb
LVL 1
agwalshAsked:
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.

als315Commented:
In your comboboxes bounded are first columns from query, so, when you are trying to compare with this value, you get wrong result. You can exchange column 1 and 2 in first combo, for example.
Workorder_database_vers_07.accdb
PatHartmanCommented:
Work orders should be a subform.  You should not have 5 combo boxes.  In a 1-many relationship "many" should be infinite unless there is a very good reason for constraining the number and even then, you would still use a subform.  You would just restrict the row count so you would not allow anyone to add more than 5 rows.  That also means that the Heat_Treat table should not include ANY work order numbers.  They need to be in a separate table.  The way you are developing the app, you could NEVER have more than 5 workorders for a Work_Center.  The Heat_Treat table also does not have a primary key so given your data, how would you know what record was "current".

Assuming this app is going to manage a schedule fore each work center, the tables need to be restructured to normalize them properly.  I don't know enough about your requirements to suggest something meaningful but if you want help, tell us a little more about your objective.
agwalshAuthor Commented:
hi Folks
This is my issue. I'm starting from a text file like the one attached. How can I set up this text file to feed into an Access database so the user can do the following. This will be pulled from another system.
1. Pick a workcentre number from a dropdown list
2. On the same form, the user can select a work order number (these are filtered to only show a work order number for that work centre.
3. The user picks the first work order number in a combo box e.g. Cbo01. Ideally, I'd like a text box beside that with a default entry of 1 - which shows it is the first choice.
2. They then choose a second workorder number from CBO02- these are now filtered so the choices EXCLUDE the choice made in Cbo01. Ideally, I'd like a text box beside that with a default entry of 2 - which shows it is the first choice.
3. They then choose a third workorder number - these are now filtered so the choices EXCLUDE the choice made in Cbo01 and CBO02. Ideally, I'd like a text box beside that with a default entry of 3 - which shows it is the first choice.  And so on for the next two choices - five choices in total. Then I also want a field that shows the date of posting and time of posting. This can be in the background. All these choices to then be added to another table. So this table shows the workcentre number, the workorder number, the choice number and the date of posting and time of posting. Thanks.
tbl_workorders_workcentre.txt
PatHartmanCommented:
Does this input file completely erase all existing data each time it is imported or does it add to it?

Why are the choices limited to 5?  Don't get yourself boxed in by an arbitrary number.  Just because a form has room to list the names of only 5 children, doesn't mean that a family might not have more and when you are using a printed form, people simply write in the margins.  You don't have that kind of flexibility in a database.  Once you pick 5, then you can't change that  if tomorrow you need 6 without a significant investment in programming.  You can create a structure that supports an infinite number and then apply a business rule that limits the selection to 5.  This serves the purpose but doesn't box you in since you can change a line of code far easier than you can change the database structure.

Are you talking about limiting the choices only within a set so in a set of 5, a work order can appear only once or are you saying that once a work order is chosen and assigned to a set, it can not be assigned to any other set?

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:
Well turns out now they've all changed their minds and want something else altogether.
@PatHartman - do appreciate the advice but apparently the way I have outlined was the way they wanted it...
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.