Cascade Three Boxes over Three tables (1 Viewer)

[Zydeceltico]

Hi All -

Noob question.

My company has 3 manufacturing plants. At each plant we have multiple production lines. Each production line produces a unique product. Some production lines are able to produce many unique products. No production line can make a product that can be made on another production line - tooling if far too different. It will always be this way. One line produces 1 product. A different line produces as many as 20 products.

Every product can only be produced at a unique plant. In other words Plant -> ProdLine is a 1 to Many. Also, ProdLine -> Part is a 1 to Many.

I have tblPlant; tblProdLine; and tblParts. Currently they do not have an established relationship between them. They are unconnected - currrently.

I have frmInspection that ultimately will be used at all 3 plants.

I want to have a combo box (cboPlant) that requires the user to pick their plant which then delimits the choices in cboProdLine to only those parts that can be made on that ProdLine. The user's selection in cboProdLine then delimits cboParts to those parts that can be made on that specific production line.

As I mentioned - these are currently stored in three separate tables - unrelated.

My questions:
1)What is the best way to setup a structure to accommodate my desired functionality?
2) do I need to relate the tables across FKs? That is easy enough. Or should put all of the fields in one large table? DOesn't seem correct but would make this a lot simpler - for me in my naivete.
3) When inserting combo boxes on a form, how do I relate one to the next consecutively? Do I need to also connect the FK from one to the next?
4) etc., etc.

I AM A NOOB - Please don't assume that I'll know anything really. lol Details please.

Thank You!!

Tim

 

[pbaldy]

You've used the correct term, we call them cascading combo boxes:

http://www.baldyweb.com/CascadingCombo.htm

In your case, I'd expect the production lines table to include a field noting which plant it's in. I'd expect the product table to include a field for which production line produces it. That should get you your cascading combos.

In answer to your question, each table should only have the key value from the other table.

 

[Zydeceltico]

You've used the correct term, we call them cascading combo boxes:

http://www.baldyweb.com/CascadingCombo.htm

In your case, I'd expect the production lines table to include a field noting which plant it's in. I'd expect the product table to include a field for which production line produces it. That should get you your cascading combos.

In answer to your question, each table should only have the key value from the other table.

Thanks Paul. I do have it setup with foreign keys as you describe. I just haven't currently related one to the next. Is relating them necessary or not?

Also, I've tried to setup the cascading in the Query builder (to no avail) but I notice that what you have suggested appears to be done in VBA. Am I correct? If so - that adds a whole new level of "brain crush" for me.

In the Query builder I don't really get what to do as all of the video examples I've seen so far have all data fields in a single table and I also think I'm missing a piece of logic as to the order of expressions in the builder.

I've attached the db. The frmTestCascadeCBO is where I've been trying to put this together so I don't mess up my currently adequate other forms. Right now I have each cbo set to its corresponding table/field combination for clarity.

Any review and assistance is greatly appreciated.

Thank You,

Tim

 

[pbaldy]

This should get you started:

Private Sub cboPlant_AfterUpdate()
    Dim strSQL                As String

    strSQL = "SELECT Workstation_ID, Workstation " _
           & "FROM tblWorkstations " _
           & "WHERE Plant_FK = " & Me.cboPlant
    Me.cboWorkstation.RowSource = strSQL
End Sub

 

[Zydeceltico]

This should get you started:

Private Sub cboPlant_AfterUpdate()
    Dim strSQL                As String

    strSQL = "SELECT Workstation_ID, Workstation " _
           & "FROM tblWorkstations " _
           & "WHERE Plant_FK = " & Me.cboPlant
    Me.cboWorkstation.RowSource = strSQL
End Sub

Thanks Paul!

And this is where my True Noob Self comes into play.

Where do I place this code?

In the VBA module for cboPlant or the VBA module for cboWorkstation?

Otherwise I totally understand the logic of the code and will be able to finish the rest of the project. I am very grateful.

Thank You,

Tim

 

[Zydeceltico]

I think I figured it out. I placed your code on the AfterUpdate event of cboPlant and it appears to work perfectly as expected.

Thank You

 

[pbaldy]

Happy to help!

 

[Zydeceltico]

Happy to help!

Paul -

I worked out the rest of that cascade. Many, many thanks!

Super appreciated!

 

[pbaldy]

Excellent, glad you got it working!

 

[Zydeceltico]

Excellent, glad you got it working!

I may have spoke to soon. lol

It works great in my test form. Perfectly.

When I try the same code in frmMillInspect it doesn't work. When I go to cboWorkstation, it pops up a window asking for a parameter.

Thoughts?

 

[Zydeceltico]

Is it possibly because on my test form I used unbound cbo controls and my controls on frmMillInspect are bound?

 

[Zydeceltico]

Or is it because on frmMillInspect I define the Rowsource of cboPlant as:

SELECT tblPlantName.Plant FROM tblPlantName ORDER BY tblPlantName.Plant;

and I should actually leave that blank?

see frmMillInspection. Make selection from "Plant" then try to make selection from Workstation.

THANKS

 

[Zydeceltico]

Whoops- here's the DB

 

[Zydeceltico]

So - I am thinking that the VBA above is not going to work for me as I can only make it work with unbound combo boxes when in fact I need it for data entry.

Thoughts? I am sure I am missing something. I want to record every selection throughout the "cascading."

 

[JHB]

Database attached, I'll explain later :

 

[JHB]

Now I got the time to explain:
Finding the workstation name you need the Plant_Id number, but you control cboPlant was only containing the Plantname, therefore you need a hidden column with the Plant_Id.
To get that you set the Column Count and the Column Width as shown in the picture.

Then you use the value from the hidden column, to get the Workstation_ID and the Workstation (name).

strSQL = "SELECT Workstation_ID, Workstation " _
& "FROM tblWorkstations " _
& "WHERE Plant_FK = " & Me.cboPlant.Column(1)
Me.cboWorkstation.RowSource = strSQL

For showing the Workstation (name) in the control cboWorkstation you hide the first column (containing the Workstation_ID).

That's it!

 

[Zydeceltico]

Now I got the time to explain:
Finding the workstation name you need the Plant_Id number, but you control cboPlant was only containing the Plantname, therefore you need a hidden column with the Plant_Id.
To get that you set the Column Count and the Column Width as shown in the picture.

Thanks JHB!

I tried the same approach for the next dropdown down the chain (Plant->Workstation->Deck Type) without success.

I make a selection in cboDeckType and get the following error: Syntax error (missing operator) in query expression 'Workstation_FK =',

The code I'm using is:

Private Sub cboWorkstation_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Part_ID, DeckType " _
& "FROM tblPartsDrawings " _
& "WHERE Workstation_FK = " & Me.cboWorkstation.Column(1)
Me.cboDeckType.RowSource = strSQL
End Sub

I understand the logic and SQL statement of your code. I am guessing the Syntax error may have something to do with column count again but I've made sure my column count in cboDeckType is 2. If I reverse the hidden and visible columns I then get this error:

Syntax Error (missing operator) in query expression 'Workstation_FK = Mill 8'

I'm boondoggled. I'm pretty sure I have something pointing somewhere incorrectly - obviously.

Your insight is greatly appreciated. Thank You.

Tim

 

[JHB]

The first column, has the index number = 0.

 

[Zydeceltico]

The first column, has the index number = 0.

That Worked!

THX - Can move on! WOOT

 

[Zydeceltico]

So just to learn some more and make it more complex: tblWorkstation has fields Workstation and also WSType. WSType has three values: mill, weld, and assembly.

If I wanted to use cascading cbos for Plant and Workstation is it possible to add an additional criteria to the code on cboPlant that returns only those Workstations in tblWorkstation whose WSType = "mill"?

Would this be an "And" statement following the "Where" where statement in the following code?

Private Sub cboPlant_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT Workstation_ID, Workstation " _
& "FROM tblWorkstations " _
& "WHERE Plant_FK = " & Me.cboPlant.Column(1) _
& "AND WSType = "mill" _
Me.cboWorkstation.RowSource = strSQL
End Sub

Or something like that?