Solved Detect whether a combobox selection has actually changed or just reselected?

[AOB]

I have a form with a number of "cascading" comboboxes, each with simple Table/Query RowSources.

When the user selects an option from the dropdown of the first combobox, the second combobox is requeried and revealed, with options relevant to the first selection.

And so on down the list; each successive combobox selection cascades down to the comboboxes below it.

I want to add code to the event handlers of each combobox, such that if any combobox selection changes, it "nulls" all the dependent comboboxes below and requeries them. Effectively, resetting the form from that point downward.

But - if the selection doesn't actually change - i.e. if the user clicks the dropdown, views the list, but just re-selects the same item that was already selected - I don't want them to lose any of the subsequent selections they may have already made further down the cascade.

So - the obvious thing to me would be to add code to the BeforeUpdate event handler for each combobox and compare the .OldValue with the .Value - if they're the same, do nothing (the selection hasn't actually changed) - if they're different, reset any of the dependent comboboxes as necessary. Something along the lines of :

Private Sub cboCombo1_BeforeUpdate(Cancel As Integer)
    If Not Nz(Me.cboCombo1.OldValue,"") = Nz(Me.cboCombo1.Value,"") Then
        Me.cboCombo2.Value = Null
        Me.cboCombo2.Requery
        Me.cboCombo3.Value = Null
        Me.cboCombo3.Requery
    End If
End Sub

What's weird is - when the BeforeUpdate event fires, the .OldValue is already set to the (new) .Value - I can't see what the previous selection actually was?

I've tried the Change and Dirty events as well, same behaviour. I can't seem to capture the previous selection in time to make a determination as to whether the dependent comboboxes should be reset or not?

(Have also tried using the Form_BeforeUpdate event but that doesn't fire at all; form isn't bound to anything)

Pretty sure I'm doing something dumb here but can't for the life of me figure it out

 

[Gasman]

Save the value on a GotFocus event?

 

[DHookom]

Is the combo box bound? According to documentation: "Microsoft Access uses the OldValue property to store the original value of a bound control"

 

[isladogs]

Have a look at my example app which has 5 cascading combo boxes:

Cascading Combo Boxes

This explains how cascading combo boxes are used to filter large datasets in Access

isladogs.co.uk

 

[AOB]

Have a look at my example app which has 5 cascading combo boxes:

Cascading Combo Boxes

This explains how cascading combo boxes are used to filter large datasets in Access

isladogs.co.uk

Thanks @isladogs but your app has the same problem I do - for example, if I select an Area from the first dropdown, then a District from the (cascaded) second, then a Sector from the (cascaded) third, Zone from the (cascaded) fourth etc.

And then at that point, I return to have another look at what my options were for Area (i.e. click the Area dropdown) but ultimately just re-select the same Area I had selected in the first place, I still lose all the previous selections below that. So even though I haven't actually "changed" the Area, I have to make all those selections again.

I'm trying to avoid that, I only want it to scrub the cascading comboboxes if the actual selection has been changed?

 

[AOB]

Is the combo box bound? According to documentation: "Microsoft Access uses the OldValue property to store the original value of a bound control"

Comboboxes are not bound, no (corrected my original post, sorry!) They are populated by queries via RowSource but not bound to anything.

 

[AOB]

Save the value on a GotFocus event?

Seems a bit clunky when all I want to do is flag that the selection has simply changed?

 

[MajP]

Private Sub Combo2_AfterUpdate()
  Static Selection As String
  If Selection <> Me.Combo2 & "" Then
    MsgBox "change from " & Selection & " to " & Me.Combo2
    Selection = Me.Combo2 & ""
  End If
End Sub

 

[CJ_London]

Depends on your downstream combo rowsource but before resetting them compare the ‘new’ rowsource with the existing one

If the rowsource references the previous combo so the sql is the same, then instead compare recordsets - perhaps to keep it simple the recordset count, or the first record

 

[Gasman]

I have a form with a number of "cascading" comboboxes, each with simple Table/Query RowSources.

When the user selects an option from the dropdown of the first combobox, the second combobox is requeried and revealed, with options relevant to the first selection.

And so on down the list; each successive combobox selection cascades down to the comboboxes below it.

I want to add code to the event handlers of each combobox, such that if any combobox selection changes, it "nulls" all the dependent comboboxes below and requeries them. Effectively, resetting the form from that point downward.

But - if the selection doesn't actually change - i.e. if the user clicks the dropdown, views the list, but just re-selects the same item that was already selected - I don't want them to lose any of the subsequent selections they may have already made further down the cascade.

So - the obvious thing to me would be to add code to the BeforeUpdate event handler for each combobox and compare the .OldValue with the .Value - if they're the same, do nothing (the selection hasn't actually changed) - if they're different, reset any of the dependent comboboxes as necessary. Something along the lines of :

Private Sub cboCombo1_BeforeUpdate(Cancel As Integer)
    If Not Nz(Me.cboCombo1.OldValue,"") = Nz(Me.cboCombo1.Value,"") Then
        Me.cboCombo2.Value = Null
        Me.cboCombo2.Requery
        Me.cboCombo3.Value = Null
        Me.cboCombo3.Requery
    End If
End Sub

What's weird is - when the BeforeUpdate event fires, the .OldValue is already set to the (new) .Value - I can't see what the previous selection actually was?

I've tried the Change and Dirty events as well, same behaviour. I can't seem to capture the previous selection in time to make a determination as to whether the dependent comboboxes should be reset or not?

(Have also tried using the Form_BeforeUpdate event but that doesn't fire at all; form isn't bound to anything)

Pretty sure I'm doing something dumb here but can't for the life of me figure it out

Really?
Does not do that for me?
No change
Change
Change back

 

[MajP]

Really?
Does not do that for me?
No change
Change
Change back

Is your combo bound? The OP corrected and said it was not bound. I believe the oldvalue on an unbound combo returns the new value since it has no way to retain the old value.
So my demo basically rolls your own "old value"

 

[Gasman]

Yes, my combo was bound.
So a simple, but clunky fix, would be to bind to a throwaway table?

 

[MajP]

Yes, my combo was bound.
So a simple, but clunky fix, would be to bind to a throwaway table?

I think what I did is simpler than that. It is three lines of code.

 

[isladogs]

Thanks @isladogs but your app has the same problem I do - for example, if I select an Area from the first dropdown, then a District from the (cascaded) second, then a Sector from the (cascaded) third, Zone from the (cascaded) fourth etc.

And then at that point, I return to have another look at what my options were for Area (i.e. click the Area dropdown) but ultimately just re-select the same Area I had selected in the first place, I still lose all the previous selections below that. So even though I haven't actually "changed" the Area, I have to make all those selections again.

I'm trying to avoid that, I only want it to scrub the cascading comboboxes if the actual selection has been changed?

In my example, if you go back to an earlier combo and re-select the same item, the next combo is indeed cleared. That is because re-selecting the same item triggers the after update event.
However, if you click the dropdown to check the options then press Esc, the later combos are not cleared as the after update event doesn't run again. That behaviour was intentional.

 

[AOB]

Private Sub Combo2_AfterUpdate()
  Static Selection As String
  If Selection <> Me.Combo2 & "" Then
    MsgBox "change from " & Selection & " to " & Me.Combo2
    Selection = Me.Combo2 & ""
  End If
End Sub

Nice! I like this - working nicely for me and easy to keep track of. Thanks @MajP !

 

[AOB]

In my example, if you go back to an earlier combo and re-select the same item, the next combo is indeed cleared. That is because re-selecting the same item triggers the after update event.
However, if you click the dropdown to check the options then press Esc, the later combos are not cleared as the after update event doesn't run again. That behaviour was intentional.

Thanks @isladogs - I totally get you and agree - but users are unlikely to press the Esc key to "cancel" the dropdown, especially when there's only a handful of options. Users have already clicked on the dropdown, realised that they had made the correct selection all along, and simply re-select it again (hand is already on the mouse, previous selection is already highlighted, natural behaviour / fastest action is to simply select that same option again, rather than switch from mouse to keyboard to "cancel" the change) It's that natural user behaviour I'm trying to mitigate against (UI/UX etc. etc.)

 

[isladogs]

Yes I can appreciate that and @MajP’s solution is very neat. Interesting that I never had anyone raise this as an issue in the postcode app where that form comes from.

 

[Gasman]

Yes I can appreciate that and @MajP’s solution is very neat. Interesting that I never had anyone raise this as an issue in the postcode app where that form comes from.

Perhaps those users can see what they have selected instead of clicking on the combo again?