Cascading combo boxes on form. (1 Viewer)

[beckie1234]

I am back. Sorry, but I still can't get this code to work for a cascading combo box. The tables are:
tblLine = lineID (pk), lineName
tblLineMach = lineID, machID (composite PK)
tblMachCent = machID (pk), machName
tblMachCat = machID, catID (compostite PK)
tblCategory = catID (pk), catName

The line feeds the machine center and the machine center feeds the category. The two tables tblLineMach and tblMachCat are bridge tables with composite PK tables to get rid of redundant data. When I had just tblLine, tblMachCent, tblCategory it worked great but I had redundant data, so I put in the bridge tables to get rid of the extra data and now it doesn't work. PLease help I am pulling my hair out.

Private Sub Line_AfterUpdate()
Me.MachineCenter.Enabled = True
Me.MachineCenter = Empty
Me.MachineCenter.RowSource = " SELECT tblLine.lineID, tblLineMach.lineID, tblLineMach.machID, tblMachCent.machID" & _
" FROM tblLine, tblLineMach, tblMachCent" & _
" WHERE tblLine.lineID = tblLineMach.lineID and tblLineMach.machID = tblMachCent.machID;"


End Sub

Private Sub MachineCenter_AfterUpdate()
Me.Category.Enabled = True
Me.Category = Empty
Me.Category.RowSource = " SELECT tblMachCent.machID, tblMachCat.machID, tblMachCat.catID, tblCategory.catID" & _
" FROM tblMachCent, tblMachCat, tblCategory" & _
" WHERE Between tblMachCent.machID = tblMachCat.machID AND tblMachCat.catID = tblCategory.catID;"

End Sub

Private Sub Category_AfterUpdate()
Me.Category.Requery
End Sub

 

[boblarson]

I think you're going about this the hard way. Why not just put the query part (the SQL statements) in as the rowsource of the combo boxes and have the criteria set as the other combo and do a requery on AfterUpdate?

See my sample here:
http://downloads.btlarson.com/AWF/Samples/MultiComboSample.zip

When I had just tblLine, tblMachCent, tblCategory it worked great but I had redundant data...

You probably just needed to use the grouping button:

 

[beckie1234]

Do you have a second to show me the kind of query you are talking about. I can get a query that list all the choices in each box and I put the requery in the after_update, but it gives me all the choices and all I want is if they choose line 1 then only the machine centers for that line show up and when they choose a particular machine center for a line only the categories for that should show up.
Any suggestion would greatly be appreciated.

 

[boblarson]

Can you post your database here? Remember to go to Tools > Database Tools > Compact and Repair to compact and then zip it with WinZip or something like it.

 

[beckie1234]

Here is a copy of the DB. Please anything will help at this point.

 

[boblarson]

Okay, first things first. I gotta ask some questions as it looks like your tables and relationships are not correctly set up.

1. Does the foreman have responsibility for more than one crew?
2. Can the crew have more than one foreman?
3. How is the line associated with Machine Center and category?
4. Each record in the downtime table is meant to store information about the downtime of 1 piece of equipment at a time, correct?
5. For any one downtime is only one crew and foreman associated with it?

That'll do to start. I believe some of your problems stem from the fact that your relationships are not really what you need and as such you have some junction tables that may, or may not, be required or are in the wrong place. I do see several links that are not correct. But, I need to understand your business and what you are trying to accomplish in order to give you good guidance as to how to fix it.

 

[beckie1234]

1. The foreman are in charge of more than one crew.
2. The crew only have one foreman at a time.
3. There are 2 lines. Line 1 has 11 Machine Center areas and line 2 has 10. The only difference is line 1 has backline, other than that they both have the same areas.
4. Yes. Each record is recording one incident of downtime.
5. Yes. Only one crew and one foreman for each specific incident of downtime.
Hope this helps answer a few things.

 

[beckie1234]

Any more good advice? I could really use it.

 

[boblarson]

Okay here's some more info:

1. Your tables and relationships are not correct. You aren't using the keys correctly. As an example, you should have your Primary Key be an autonumber (not text) and then it becomes the Foreign Key in the related table and it would be set as Long Integer.

2. I tried to do some work around that but I am still confused about your business so I'm reposting your database with just some changes for you to get the idea and hopefully you can redesign your machine category and machine center tables (I think there was one more too) so that they are properly using the right keys.

3. When you created your queries for the combo boxes, you included more tables than required. Normally, you would only use the table or tables that are necessary to get the ID and name for the drop down. It really isn't necessary to include more than just the one table (normally) as your look up table would have the id number and then the description of what you are selecting. Then, you also have to make sure to set the combo properties to the correct number of columns and the correct column widths.

4. The Crew / Foreman tables needed the junction table which you had partially complete. I added a primary key and then linked the relationships for you. Also, since you will set the crew/foreman relationships in the form that I created (I did random pairings so you'll need to modify them to the correct values) then you just select the crew on the downtime form and the foreman is populated automatically from the combo box.

I wasn't able to fix your machine combo boxes until you get those tables straightened out and if you can get the idea from what I've already done (and my sample that I posted in the previous post), hopefully you can get that working. If not, let's revisit it after you fix the tables (which need to be done before doing the combos).

Hopefully this will help you. I just don't have any more time to devote to this project and get my stuff done and assist others too.

 

[beckie1234]

Thanks for your help. I will look over the database and see what I need to do, then I will be back.