Auto Complete Cascading Combo Box If Only Single Record Left

[ErikAE]

Hi.



I have several cascading combo boxes that filters data; one combo box filtering data for the following combo box.

In some situations however, there may only be a single record to choose from in a combo box.

Is there a way to automatically fill the combo box & any other following combo boxes when there is only one option to choose from?

Regards.
Erik.

[June7]

Bing: vba select first item combobox
http://support.microsoft.com/kb/105519

If you want conditional:

If Me.combobox.ListCount = 1 Then

[ErikAE]

Thanks.

It put me on the right track to solve the issue. Just in case this may be useful for anyone looking for a solution to auto-populating cascading combo boxes:

Private Sub FIRST_COMBOBOX_Change() <--- Place code in first or preceding combo box's 'On Change' property


Me.SECOND_COMBOBOX.Requery <--- Refresh 2nd cascading combo box


If Me.SECOND_COMBOBOX.ListCount = 1 Then <--- Checks to see if there is only a single record in the 2nd combo box


With Me.SECOND_COMBOBOX
SECOND_COMBOBOX.SetFocus
SECOND_COMBOBOX.Value = SECOND_COMBOBOX.ItemData(0)
End With
End If


End Sub

[June7]

I would use the first combobox AfterUpdate event.

If you are going to explicitly reference SECOND_COMBOBOX then the With End With lines accomplish nothing. Consider:

With Me.SECOND_COMBOBOX
.Requery
If .ListCount = 1 Then
.SetFocus
.Value = .ItemData(0)
End If
End With

[ErikAE]

Ok.

I used your snippet of code but it does not auto populate the second combo box; I still have to go in & select the record. Was that the intended effect?

The code I specified earlier works fine as it does what it intends to do but I welcome anything that makes it more efficient. Thanks.

[June7]

No, was not the intended effect. The code should work. I am just saying if you explicitly reference the second combobox on each line, the With End With lines don't serve any purpose - they don't hurt just don't really do anything.

[ErikAE]

Oh right; thanks for that. I'll play around with it & see how it turns out. Appreciate the help.

On a different note, I do have another couple of queries; should I create a new thread for these?

[June7]

Yes. New topic, new thread.