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_AfterUpd
Me.cbo_work_order_02.Reque
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_AfterUpd
Me.cbo_work_order_03.Reque
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
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_AfterUpd
Me.cbo_work_order_02.Reque
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_AfterUpd
Me.cbo_work_order_03.Reque
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Workorder_database_vers_07.accdb
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.
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.
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
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
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?
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial