Set Combo box value if only one value remains after requery 1

[Poduska]

I am trying to set a combo box value and eliminate the dropdown if there is only one value remaining after a requery.

Why make someone click if there is only one choice?

I have 2 combo boxes, Filter1 and Filter2. Filter 2 is a list of values dependant on what was selected in Filter1, for example, Tasks and SubTasks.

After Filter1 is updated I requery Filter2, blank out the value in Filter2 and open the dropdown, this code works fine

Private Sub Filter1_AfterUpdate()
Dim t As String

    Filter2.Requery

    Me.Filter2.SetFocus
        Me.Filter2 = ""
        Me.Filter2.Dropdown
End Sub

However, if the drop down only has one value I want to select the value and save the user the extra click (anything to stop their complaining !!!)

I have tried this and it works but I HATE to use SendKeys.

Private Sub Filter1_AfterUpdate()

    Filter2.Requery
    Me.Filter2.SetFocus
 
    If Me.Filter2.ListCount = 1 Then
         Me.Filter2.Dropdown
         SendKeys "{down}"
         SendKeys "{enter}"
    Else
        Me.Filter2 = ""
        Me.Filter2.Dropdown
    End If
End Sub

Is there a way to use VBA to select a line in a combo box by assigning the ListIndex value? I am coding in 97 but it will have to work in 2003 as we have both versions.

I have tried this per Microsoft Webpage but it gives me a 7777 error.

 Private Sub Filter1_AfterUpdate()
Dim t As String
    Filter2.Requery
    Me.Filter2.SetFocus
    Debug.Print Me.Filter2.ListCount
    If Me.Filter2.ListCount = 1 Then
         Me.Filter2.Dropdown
         'SendKeys "{down}"
         'SendKeys "{enter}"
        Forms("frmReportMenu").Controls("filter2").SetFocus
        Forms("frmReportMenu").Controls("filter2").ListIndex = 0

    Else
        Me.Filter2 = ""
        Me.Filter2.Dropdown
    End If
End Sub

Thanks for any insight!

 

[PHV]

Error in ac97 but OK in ac2003:

...
    If Me!Filter2.ListCount = 1 Then
         Me!Filter2.SetFocus
         Me!Filter2.ListIndex = 0
    Else
...

You may try this another way:

...
    If Me!Filter2.ListCount = 1 Then
         Me!Filter2.Value = Me!Filter2.ItemData(0)
    Else
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Poduska]

IT WORKS!!!!!!!


I could swear I tried EVERY permutaion of field name ListCount and ItemData before I broke down and posted........... but obviously not.

With all my research I had all the tools and at my disposal but never made the connection and put it all together.


Thanks again PHV!
Another star to what must now be a "GALAXY"!