How to make combo box dropdown disappear

[DavidAustin]

Hi all,

I have a combo box on a form which drops down once the user has typed into it. I want the dropdown to "disappear" if the user backspaces to an empty string and I thought the best way to do so would be to set the focus to a random text box and then return the focus back straight afterwards. Unfortunately, I keep getting an error saying that Access can't move the focus to the random textbox. I'm convinced it is because I am trying to set the focus back to the combo box before the text box has gained focus but I dont know which event to place the combobox.setfoc us in order to switch back to the combobox.

At the moment, the barebones of the code is:

Private Sub cboLookup_Change()

Dim sNewLookup As String

        sNewLookup = Nz(Me.cboLookup.Text, "")
        If Len(sNewLookup) <> 0 Then
            Me.Text13.SetFocus
            Exit Sub
        End If
        Me.cboLookup.RowSource = sSQL
        Me.cboLookup.Dropdown
    End If
    bLookupKeyPress = False
End Sub

Private Sub Text13_GotFocus()
Me.cboLookup.SetFocus
End Sub

It gives the error in the cboLookup_Chang e on the SetFocus line which makes sense - I just can't work out how to give the text box focus before returning it back to the combo box. All help is most appreciated!

[DavidAustin]

Okay, I managed to solve it! Just in case anyone ever had the same problem, you don't need to have a separate text box, just need to disable the combo box then re-enable it e.g.

Private Sub cboLookup_Change()
    Dim sSQL As String
    Dim sNewLookup As String
 
    If Not bLookupKeyPress Then
        'If nothing entered, set to empty string
            sNewLookup = Nz(Me.cboLookup.Text, "")

'If length of string is not 0, do stuff
        If Len(sNewLookup) <> 0 Then
            Me.cboLookup.RowSource = sSQL
            Me.cboLookup.Dropdown
'If length of string is 0, disable the combo box
        Else
            Me.cboLookup.Enabled = False
        End If
    End If
    bLookupKeyPress = False

'Check status of combo box, re-enable if disable
    If Me.cboLookup.Enabled = False Then
        Me.cboLookup.Enabled = True
        Me.cboLookup.SetFocus
    End If
End Sub

[jforbes]

I remember this.

It would be nice if you could just call .Dropdown again to rollup the Dropdown list, but it doesn't work even though this would lead you to believe it would:

https://msdn.microsoft.com/en-us/library/office/ff836880.aspx

But it does say the .Dropdown is equivalent to pressing F4 on the Keyboard...

SendKeys "{F4}"

seems to work pretty well.

I think you could use this also:

Private Sub cboLookup_Change()
     Dim sSQL As String
     Dim sNewLookup As String
 
     If Not bLookupKeyPress Then
         'If nothing entered, set to empty string
             sNewLookup = Nz(Me.cboLookup.Text, "")
 
 'If length of string is not 0, do stuff
         If Len(sNewLookup) <> 0 Then
             Me.cboLookup.RowSource = sSQL
             Me.cboLookup.Dropdown
 'If length of string is 0, disable the combo box
         Else
             SendKeys "{F4}"
         End If
     End If
     bLookupKeyPress = False
 
 End Sub

[DavidAustin]

Even quicker and neater than my original fix! Thanks for the input jforbes!