Trying to terminate a set of cascading comboboxs gracefully.

[Tegglet]

I have a set of five cascading combo boxes, the first of which gets its data from a table, say table1, with the rest getting their data from SQL queries run against table2, table3 etc. So if the first combo box lists item1, item2, item3, item4 etc., and item3 is selected then the second combo box will only list the items in table2 that the SQL query associates with item3. The third combo box lists only those items in table3 that the SQL query associates with the selected item in the third combo box.
This all works fine until there were no items available as a result of one of the SQL queries. I am trying to find a way of ending the process gracefully bit so far I cannot find a way of doing this if an intermediate query results in a zero length list.

I have tried to upload an example database but even my cut down version was too large!

If I could get the code below working I think it would solve my problem. I am using the Got focus event to check that the listcount is zero and if it is zero to set the Me.Classification combo box state to enabled = false. This works but returns to the after update sub and tries to set the focus which, of course, doesn't work because the state of the combo box in question is now set to enabled = false. The "if" statement shown on bold returns True irrespective of the enabled state i.e., if enabled is set to False the conditional statement is still executed hence an error is generated.

Any help would be appreciated.

Private Sub Classification_GotFocus()
MsgBox "Classification has got Focus"


With Me.Classification
    .Requery
    MsgBox "Number of items in the list= " & Me.Classification.ListCount
    If .ListCount = 0 Then
        .Enabled = False
    Else
        .Enabled = True
    End If
End With
End Sub

Private Sub Category_AfterUpdate()
vCategory = Category.Text: Debug.Print "Category = " & vCategory


With Me.Classification
        .Requery
        .Value = ""
        .Enabled = True
        
      If .Enabled = True Then    'If the listcount is zero then Me.Classification will have .Enabled set to False.
            MsgBox "Enabled = " & .Enabled
            .SetFocus
        End If
End With


With Me.Group
    .Requery
    .Value = ""
    .Enabled = False
End With


With Me.Topic
    .Requery
    .Value = ""
    .Enabled = False
End With


With Me.Subject
    .Requery
    .Value = ""
    .Enabled = False
End With


End Sub

[Welshgasman]

Ok, I am really puzzled.
You set Enabled to False in the GotFocus, then set it to True in the AfterUpdate (which does not get invoked if vhanged by VBA) and then immediately test if it is True?

[davegri]

I have tried to upload an example database but even my cut down version was too large!

Do a compact/repair on the db, then zip the accdb to compress it.

[Welshgasman]

Plus you only need enough to show the issue, not all the data.
Do some work and we can help you.

If it was me, I would have a sub in the form, where I would pass in a number and the state.
Then that sub would call itself as many times as needed.

So if we have 5 combos, and the 4th has no data, then I would pass in 4 and False.
That sub would do your enabled,set the value to 0, as I hope you would be using autonumbers, then call the next lower number on that sub.

The reason for me doing it that way, is I have a thing about duplicating code, and a simple case logic would involve duplicating a lot of code.

[moke123]

In the after update of your combos use the below, referencing the next combo in the sequence.
I would start off the sequence with all the combos disabled except the first one so that the user must follow the correct sequence.
You can also put code to clear combos in case the user goes backwards. for example if after using the 4th combo they go back and change the 1st combo it will null out and disable combos 3,4,5,etc.

Private Sub Combo0_AfterUpdate()

    Me.Combo2.Requery    ' the next combo in the sequence

    If Me.Combo2.ListCount < 1 Then   
        Me.Combo2.Enabled = False
    Else
        Me.Combo2.Enabled = True
    End If

End Sub

[Tegglet]

In the after update of your combos use the below, referencing the next combo in the sequence.
I would start off the sequence with all the combos disabled except the first one so that the user must follow the correct sequence.
You can also put code to clear combos in case the user goes backwards. for example if after using the 4th combo they go back and change the 1st combo it will null out and disable combos 3,4,5,etc.

Private Sub Combo0_AfterUpdate()

    Me.Combo2.Requery    ' the next combo in the sequence

    If Me.Combo2.ListCount < 1 Then   
        Me.Combo2.Enabled = False
    Else
        Me.Combo2.Enabled = True
    End If

End Sub

First, Thank you all for responding so promptly ;-)

I have entered the code from Moke123 into the example database and it works fine. I will now try to combine it with the suggestion from Welshgasman as I too have an aversion for duplicated code, it can lead to parallel development and thus to confusion.

With regard to attaching to example database, I had cut it to the bare bones with fewer combo boxes and done a compact/repair but forgot to zip it! :-(

Taking it one stage further for a graceful termination, is there a way of displaying an ad hoc message in the last combo box so the user knows the process has completed. Something like "No <combobox name> available. I know I can use a MsgBox but would prefer it if the message could be displayed in the last combo box and the process move on to the next stage automatically.

Many thanks for your prompt help; most impressive!
Tegglet

[Tegglet]

One further point, why does the if statement shown in bold not work?

[Welshgasman]

One further point, why does the if statement shown in bold not work?

It does work because of
With Me.Classification
.Requery
.Value = ""
.Enabled = True

[Tegglet]

In the actual database the VBA editor shows the Me.Classification control as having its enabled property set to false at the time of the check. The "Got focus" event sets it to false if the listcount is zero.

I suspect that I may be using the Got Focus event inappropriately!

[Welshgasman]

It does, but the after update sets it to true and then immediately tests it

[Tegglet]

I have finally found an acceptable way to display a message telling the user that no further options are available for the selections made. I have created an unlabelled text box for each combo box with the appropriate message and overlaid it on the corresponding combo box. its visible property is set to true or false as appropriate. It may not be the most elegant Way to do it but it works and I can't find any other way!

Many thanks to those who have responded. It was a good introduction to the forum for me.

[Welshgasman]

Many thanks to those who have responded. It was a good introduction to the forum for me.

To which you have now gone to another site and said

I have tried another forum but found that they were not particularly forthcoming! Here's hoping I will be more successful with this one.