cascading comboxes to reset when changed (1 Viewer)

[JJSHEP89]

So I have a single pop up form with 4 cascading combo boxes. I've written the following code to auto populate if the combobox only returns a single list value, which makes it a little simpler for the user. (this is on in the after update event for each combobox)

Private Sub cboPartNumber_AfterUpdate()
    If Me.cboDieNumber.ListCount = 1 Then
        Me.cboDieNumber = Me.cboDieNumber.ItemData(0)
    End If
End Sub

This all works just the way I want it to. The issue now is that if the user goes back to the first combobox, after a selection has already been made in the others, the remaining controls don't update. I've tried to add a line to requery the whole form in the above code but no luck. I know this has got to be something simple that I'm missing here but I'm on a short deadline and have been stuck on this for hours. TIA!

 

[Gasman]

I believe you would need to requery the dependent combos on each change.?

 

[JJSHEP89]

Just as a quick check i set each control to requery individually, the next control in line requeries just fine, the others remain the same.

Private Sub cboPartNumber_AfterUpdate()
    Me.cboDieNumber.Requery
    Me.cboResource.Requery
    Me.cboOperationSequence.Requery
    If Me.cboDieNumber.ListCount = 1 Then
        Me.cboDieNumber = Me.cboDieNumber.ItemData(0)
    End If
End Sub

Ideally, i'd like to just make this a function i could call for later on (i make a lot of forms like this) that would loop through each combobox.

 

[Gasman]

Not an expert here you understand, but you need to requery all the dependent combos.
So if cb1 changes, requery 2, 3 & 4
cb2 changes, requery 3 & 4
cb3 changes requery 4?

4 relies on 3 which relies on 2 which relies on 1 ?
https://www.youtube.com/watch?v=SpMyGlEInGs

HTH

From https://answers.microsoft.com/en-us...-in/3ce61e3b-44bc-422e-9b5c-1bd2644fe8ca?db=5

The way to get things to work is to understand how they work. In the case of correlated combo boxes it's quite straightforward:

1. The first combo box's RowSource property is a query which simply returns rows for all the items in the top level of the hierarchy, categories in your case.

2. The second combo box's RowSource property is a query which returns rows for those items in the next level down of the hierarchy, types in your case, restricted to those rows where the category for the type is that selected in the first combo box. This is done by referencing the first combo box as a parameter for the relevant category column. You need to be sure that the value of the first combo box is that which the values in the category column will match, so if the value of the category column is a hidden number, not the text value, which it will be if, for instance, the dreaded lookup field wizard was used when designing the table, the value of the first combo box needs to be the primary key numeric column (usually an autonumber) of the categories table, which means the first combo box's BoindColumn property will be a hidden numeric column, thought you'll see the text values from a non-key column in the combo box.

3. In the AfterUpdate event procedure of the first combo box you need to do two things:

3.1 Set the second combo box to Null so that any current value which it might have is removed.

3.2 Requery the second combo box so that it lists only the types for the selected category.

Once you have a good understanding of the process involved, setting up correlated combo boxes is very straightforward. The issue of normalization of the form's underlying table, while important for the integrity of the database, is not directly related, so concentrate on getting your teeth around the principles behind correlated combo boxes as outlined above. My demo, to which I referred you earlier, can then be used as a paradigm for making any amendments necessary to use the controls in the context of a table normalized to Third Normal Form.

 

[JJSHEP89]

Not an expert here you understand, but you need to requery all the dependent combos.
So if cb1 changes, requery 2, 3 & 4
cb2 changes, requery 3 & 4
cb3 changes requery 4?


4 relies on 3 which relies on 2 which relies on 1 ?


HTH

Yes, exactly! the comboboxes have been working just fine when working from a fresh blank form. Its only when the comboboxes have already been populated and the user tries to change them that i run into problems.

3.1 Set the second combo box to Null so that any current value which it might have is removed.

3.2 Requery the second combo box so that it lists only the types for the selected category.

I think this is what i need here, I've added a few lines to set the subsequent controls to null before the requery and it seems to be working the way i want now.

here's a sample of the code, the other controls have similar code to update any other combo's after.

Private Sub cboPartNumber_AfterUpdate()
    Me.cboDieNumber = Null
    Me.cboDieNumber.Requery
    Me.cboResource = Null
    Me.cboResource.Requery
    Me.cboOperationSequence = Null
    Me.cboOperationSequence.Requery
    If Me.cboDieNumber.ListCount = 1 Then
        Me.cboDieNumber = Me.cboDieNumber.ItemData(0)
    End If
End Sub

Now I just need to clean this up a bit and turn it into a function to simplify my code.

 

[MajP]

Use the on enter event of each combo to requery itself.