ComboBox Dropped Down State

In my previous article on overriding Access's default arrow key behavior on continuous forms, I wrote that there were still a couple final details to work out, including special handling for dropped-down combo boxes.

The weArrowKeyNav class

As a reminder, the purpose of the weArrowKeyNav class is to change the behavior of the up and down arrow keys to make continuous forms in Access behave more like Excel worksheets. For example, pressing the up arrow key moves to the previous record (instead of the previous control).

To do this, the class overrides the up and down arrow key behavior for each of the controls in the Detail section of a continuous form. When the user presses the up or down arrow, the code calls the form recordset's MovePrevious or MoveNext method, respectively.

This produces a more intuitive user experience; one that is much more in line with Excel's familiar behavior.

Dropped-down combo boxes

This does result in one case where the behavior is not what the user would expect. That situation is when a combo box is in its "dropped-down" state.

Pressing the up arrow key should switch to the "Weiler" option in the dropdown.

Consider the screenshot above. At the top of the screenshot are the four previous records. The current record is the one where the Supplier's name is "Kelley." The previous record is the one where the Supplier's name is "Sousa."

The combo box is in its dropped-down state. Within the dropdown, "Kelley" is highlighted. The immediately preceding option is "Weiler." One would expect that pressing the up arrow key on the keyboard would switch the current record's Supplier value from "Kelley" to "Weiler."

But that's not what will happen if the form has enabled FullArrowKeyNav.

Instead, the focus will jump to the previous record and the "Sousa" combo box will be highlighted.

Canceling the override

The solution is simple. If the combo box is open, then we exit immediately and allow the default up and down arrow key behavior to work normally. To do this, we add a guard clause that checks the combo box's dropped-down state:

Private Sub weComboBox_KeyDown(KeyCode As Integer, Shift As Integer)
    If weComboBox.IsDroppedDown Then Exit Sub
    KeyCode = KeyDown(weComboBox, KeyCode, Shift)
End Sub

I mean, there's a .Dropdown method on the combo box; surely that means there's a corresponding .IsDroppedDown property that returns its state...surely, there is...

Now, we'll just compile our code and call it a nigh–

Oh, come on, Access!

OK, so as it turns out, there is no combo box property that indicates its dropped-down status. It looks like we're going to have to go searching the interwebs...

fIsComboOpen()...thanks, Dev Ashish!

The search is long and arduous, but eventually the skies part, and Dev Ashish speaks to us from the dawn of the 21st century: API: Determining Combobox's Dropped state.

The code in the link above is so old...[audience]: HOW OLD IS IT?!?!...the code in the link is so old that it predates VBA7 and 64-bit support.

The good news for you is that I've updated the code (at least the Dev Ashish approach) to run in all versions of Access. Here it is:

The Code: fIsComboOpen() w/64-bit support

The code below is mostly Dev Ashish's original work. To the best of my recollection, the only changes I made were the additions of the conditional #VBA7 code blocks to provide 64-bit VBA compatibility.

'originally retrieved on 10/11/2011 @ 11:31 from: http://access.mvps.org/access/api/api0052.htm
'******* Code Start *********
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
' (Adapted for x64 usage by Mike Wolfe - nolongerset.com)
'
'
'  retrieves the name of the class to which the specified window belongs.
#If VBA7 Then
    Private Declare PtrSafe Function apiGetClassName Lib "user32" _
        Alias "GetClassNameA" _
        (ByVal hWnd As LongPtr, _
        ByVal lpClassName As String, _
        ByVal nMaxCount As Long) As Long
#Else
    Private Declare Function apiGetClassName Lib "user32" _
        Alias "GetClassNameA" _
        (ByVal hWnd As Long, _
        ByVal lpClassName As String, _
        ByVal nMaxCount As Long) _
        As Long
#End If

'  retrieves a handle to the specified child window's parent window.
#If VBA7 Then
    Private Declare PtrSafe Function apiGetParent Lib "user32" _
        Alias "GetParent" _
        (ByVal hWnd As LongPtr) _
        As LongPtr
#Else
    Private Declare Function apiGetParent Lib "user32" _
        Alias "GetParent" _
        (ByVal hWnd As Long) _
        As Long
#End If

'  retrieves information about the specified window. The function also
'  retrieves the 32-bit (long) value at the specified offset into the
'  extra window memory of a window.
#If Win64 Then
    Private Declare PtrSafe Function apiGetWindowLong Lib "user32" _
            Alias "GetWindowLongPtrA" _
            (ByVal hWnd As LongPtr, _
            ByVal nIndex As Long) _
            As LongPtr
#Else
    #If VBA7 Then
        Private Declare PtrSafe Function apiGetWindowLong Lib "user32" _
            Alias "GetWindowLongA" _
            (ByVal hWnd As LongPtr, _
            ByVal nIndex As Long) _
            As Long
    #Else
        Private Declare Function apiGetWindowLong Lib "user32" _
            Alias "GetWindowLongA" _
            (ByVal hWnd As Long, _
            ByVal nIndex As Long) _
            As Long
    #End If
#End If

'  retrieves a handle to the top-level window whose class name and
'  window name match the specified strings. This function does not search
'  child windows. This function does not perform a case-sensitive search.
#If VBA7 Then
    Private Declare PtrSafe Function apiFindWindow Lib "user32" _
        Alias "FindWindowA" _
        (ByVal lpClassName As String, _
        ByVal lpWindowName As String) _
        As LongPtr
#Else
    Private Declare Function apiFindWindow Lib "user32" _
        Alias "FindWindowA" _
        (ByVal lpClassName As String, _
        ByVal lpWindowName As String) _
        As Long
#End If

'  retrieves a handle to a window that has the specified relationship
'  (Z order or owner) to the specified window
#If VBA7 Then
    Private Declare PtrSafe Function apiGetWindow Lib "user32" _
        Alias "GetWindow" _
        (ByVal hWnd As LongPtr, _
        ByVal wCmd As Long) _
        As LongPtr
#Else
    Private Declare Function apiGetWindow Lib "user32" _
        Alias "GetWindow" _
        (ByVal hWnd As Long, _
        ByVal wCmd As Long) _
        As Long
#End If

'  The class name for an Access combo's drop down listbox window
Private Const ACC_CBX_LISTBOX_CLASS = "OGrid"
'  Class name for the Access window
Private Const ACC_MAIN_CLASS = "OMain"
'  Class name for an Access combo's drop down listbox's parent window
Private Const ACC_CBX_LISTBOX_PARENT_CLASS = "ODCombo"
'  class name for an Access form's client window
Private Const ACC_FORM_CLIENT_CLASS = "OFormSub"
'  class name for Edit controls in Access
Private Const ACC_CBX_EDIT_CLASS = "OKttbx"
'  class name for VB combo's drop down listbox's parent window (SDI)
Private Const VB_CBX_LISTBOX_PARENT_CLASS = "#32769" ' // Desktop
'  class name for VB combo's drop down listbox window
Private Const VB_CBX_LISTBOX_CLASS = "ComboLBox"
'  handle identifies the child window at the top of the Z order,
'  if the specified window is a parent window
Private Const GW_CHILD = 5
'  Retrieves the window styles.
Private Const GWL_STYLE = (-16)
'  flag denoting that a window is visible
Private Const WS_VISIBLE = &H10000000

Private Function fIsComboOpen() As Boolean
'  returns true if a combo box on the form is dropped down
'  only one combo can have the focus => only one drop down
'
Static hWnd As Variant  ' Long/LongPtr
Static hWndCBX_LBX As Variant  ' Long/LongPtr

   hWnd = 0: hWndCBX_LBX = 0

   '  Start with finding the window with "ODCombo" class name
   hWnd = apiFindWindow(ACC_CBX_LISTBOX_PARENT_CLASS, _
                                vbNullString)
   '  Parent window of ODCombo is the Access window
   If apiGetParent(hWnd) = hWndAccessApp Then
         '  Child window of ODCombo window is the
         '  drop down listbox associated with a combobox
         hWndCBX_LBX = apiGetWindow(hWnd, GW_CHILD)
         '  another check to confirm that we're looking at the right window
         If fGetClassName(hWndCBX_LBX) = _
                        ACC_CBX_LISTBOX_CLASS Then
            '  Finally, if this window is visible,
            If apiGetWindowLong(hWnd, GWL_STYLE) And WS_VISIBLE Then
               '  the Combo must be open
               fIsComboOpen = True
            End If
         End If
      End If
End Function

Private Function fGetClassName(hWnd As Variant)  ' Long/LongPtr)
Dim strBuffer As String
Dim lngLen As Long
Const MAX_LEN = 255
    strBuffer = Space$(MAX_LEN)
    lngLen = apiGetClassName(hWnd, strBuffer, MAX_LEN)
    If lngLen > 0 Then fGetClassName = Left$(strBuffer, lngLen)
End Function
'******* Code End *********

Dev Ashish's fIsComboOpen() code, updated for 64-bit compatibility.

And here is how we put that function to use in our ComboBox's KeyDown event handler:

Private Sub weComboBox_KeyDown(KeyCode As Integer, Shift As Integer)
    If fIsComboOpen() Then Exit Sub
    
    KeyCode = KeyDown(weComboBox, KeyCode, Shift)
End Sub

Don't override the default arrow-key behavior if the combo box is dropped down.