Cascading Combo Boxes

Question

Hello everyone,

I am attempting to create cascading Combo boxes on my DB. And it for the most part works. However, When I select the next combo box I receive the "Enter Parameter Value" Box. The Parameters box shows the value of the previous box in it.

1st Combo Box is "cboMFG"

2nd Combo Box is "ItemID"

Below is what I have in the Row Source of the second Combo Box:

SELECT tblCatalog.ItemID, tblCatalog.Item FROM tblCatalog WHERE (((tblCatalog.MFG)=[Form]![cboMFG])) ORDER BY tblCatalog.Item;

Answer 1

I do have another issue with this though. Now if I make a selection and then change the selection. My second Combo box still shows the data from the previous selection. Is there a fix for this?

In the AfterUpdate event procedure of the first combo box, in addition to requerying the second combo box you should also assign a Null to its Value property.  Note also that, if the combo boxes are being used to enter data into a table via a form, where the two controls represent a fixed hierarchy, only the second control should be bound to a column in the table.  As this functionally determines the value in the first, to bind the first control to a column in the table would introduce redundancy, and the table would not be Normalized to Third Normal Form (3NF), with the consequent risk of update anomalies.

For an example take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'entering data via a form/subforms' includes a contacts form in with three correlated combo boxes are used for entering a country, region and city.  Of these only the last is bound.

Answer 2

Private Sub cboMFG_AfterUpdate()
Me.ItemID.RowSource = Null
End Sub

Answer 3

You have two options:

1. Refer to the name of the form. Let's say your form is named frmMyForm. Change the Row Source of the second combo box to:

SELECT tblCatalog.ItemID, tblCatalog.Item FROM tblCatalog WHERE (((tblCatalog.MFG)=[Forms]![frmMyForm]![cboMFG])) ORDER BY tblCatalog.Item;

2. Leave the Row Source of the second combo box blank in design view.

Change it in the After Update event of the first combo box.If MFG is a number field:

Private Sub cboMFG_AfterUpdate()
Me.ItemID.RowSource = "SELECT ItemID, Item FROM tblCatalog WHERE MFG=" & Nz(Me.cboMFG, 0) & " ORDER BY Item"
End Sub

If it is a text field:

Private Sub cboMFG_AfterUpdate()
Me.ItemID.RowSource = "SELECT ItemID, Item FROM tblCatalog WHERE MFG='" & Nz(Me.cboMFG, "") & "' ORDER BY Item"
End Sub

Answer 4

Awesome that helped out a lot! Turns out that both codes in there. One in the before update and the other in row source. I deleted the before update and it works! Thank you !

I do have another issue with this though. Now if I make a selection and then change the selection. My second Combo box still shows the data from the previous selection. Is there a fix for this?