Show dropdown list when combobox has focus

Question

Is it possible to put a single bit of code on an Access form that would automatically show the dropdown list whenever any combobox on the form has focus?

I know I can automatically show the dropdown list whenever a combobox has focus by using the event below. But, if possible, I would prefer to not have to put that line of code on each combobox since I have so many.

Private Sub combobox_GotFocus()
    'When the combobox receives focus
    'display in drop down position
    Me.combobox.Dropdown
End Sub

My database has dozens of forms in it with dozens of comboboxes on each form.

No, there is not a 'single bit of code' that will accomplish this for all comboboxes. Each will need the event code. — June7, Jan 01 '19 at 18:39
Possible duplicate of [Creating a Class to Handle Access Form Control Events](https://stackoverflow.com/questions/23522230/creating-a-class-to-handle-access-form-control-events) — Storax, Jan 01 '19 at 19:10
Sorry @Storax but I'm not very good a VBA, I looked at the provided link but I'm not entirely sure where to start. — Kevin Kissell, Jan 01 '19 at 19:23

score 1 · Accepted Answer · answered Jan 01 '19 at 19:25

1

Create a class named cComboBox

Option Compare Database
Option Explicit

Private WithEvents mComboBox As Access.ComboBox

Private Sub mComboBox_GotFocus()
    mComboBox.Dropdown
End Sub

Public Function AddCtl(nCtl As Access.ComboBox) As Access.ComboBox
    Set mComboBox = nCtl
    mComboBox.OnGotFocus = "[Event Procedure]"
    'mComboBox.OnChange = "[Event Procedure]"
    Set AddCtl = mComboBox
End Function

and add the following code to your Form

Option Compare Database
Option Explicit

Dim myCBs As New Collection

Private Sub Form_Load()
Dim myCB As cComboBox

Dim ctl As Access.Control
    For Each ctl In Me.Controls
        If TypeName(ctl) = "ComboBox" Then
            Set myCB = New cComboBox
            myCB.AddCtl ctl
            myCBs.Add myCB
        End If
    Next

End Sub

answered Jan 01 '19 at 19:25

Storax

11,158
3
16
33

Thank you so much! That worked beautifully and saved me a ton of time! – Kevin Kissell Jan 01 '19 at 19:33
@Storax; Could you please explain the purpose of the line `Set AddCtl = mComboBox` in the `Function AddCtl()` inside the Class `cComboBox`? – Jan 01 '19 at 21:03
@Gene: No purpose at all, I just copied and modified the code from the link above. So, it is probably better to use a Set property instead. On the other hand it does not do any real harm. – Storax Jan 02 '19 at 07:29
@Storax; No, not that... I just thought it is not needed at all since there is no use for the function's return anyway: it matters not if `AddCtl` is assigned or not, whatsoever. – Jan 02 '19 at 16:04
@Gene: Right, you could remove the line `Set AddCtl = mComboBox` completely and everything would still work. – Storax Jan 02 '19 at 16:15

Show dropdown list when combobox has focus

1 Answers1