none
Dropdown already-populated combo box on first character typed RRS feed

  • Question

  • I have been using the code below for years to automatically drop down each combo box when the user types the first character. This allows the user to see the value list quickly without requiring a mouse click to get there:

    Private Sub MyComboBox_KeyPress(KeyAscii As Integer) 'combo box KeyPress event
     ControlDropDown (KeyAscii)
    End Sub

    Public Function ControlDropDown(Keystroke As Integer) 'public function that does the dropdown
     If Len(Screen.ActiveControl.Text) = 0 Then
      Select Case Keystroke
       Case 9, 13, 27 'ignore TAB, ENTER, ESC
       Case Else
        Screen.ActiveControl.Dropdown
      End Select
     End If
    End Function

    This code above runs only when the first character is typed into an empty combo box, so it does not work for combo boxes whose values need to be edited regularly. In these cases, the user simply begins typing over an entry that is highlighted, and my code logic does not kick in. But I cannot simply dropdown on every character (I cannot remember what that does, except that it is unpleasant and that I ruled that out years ago.)

    So I guess my question is 1) how to determine if the combo box is already dropped down (besides the long Windows API call I have seen) or 2) at least determine what character I am on in the control's text string. That is, if I could determine that I am at the first position, I could keep the dropdown only on the first character. I do not think I can check Dirty or Changed or any other property of the control itself, since I think those also would apply equally to successive keystrokes, not just the first.

    Tuesday, November 15, 2016 7:37 AM

Answers

  • Rather than using the length of the text entered as the criteria for dropping down the list, use a module level Boolean variable to track whether or not the list has been dropped down. 

    You can do this by declaring a Boolean variable at the top of your form's code (under Option Compare Database).  Set the Boolean variable to TRUE after dropping down the list, and reset it to FALSE in the exit event of the control, when the user leaves the combo.

    Option Compare Database
    
    ' Declare a flag to show whether or not the combo has been dropped down
    Dim mDone As Boolean
        
    Private Sub cboLastName_Exit(Cancel As Integer)
         ' Reset the flag to FALSE when the user is done with the combo
         mDone = False
    End Sub
    
    Private Sub cboLastName_KeyPress(KeyAscii As Integer)
       ControlDropDown (KeyAscii)
    End Sub
    
    Public Function ControlDropDown(Keystroke As Integer) 'public function that does the dropdown
      ' Check if the control has already been dropped down
      If mDone = False Then
      'If Len(Screen.ActiveControl.Text) = 0 Then
       Select Case Keystroke
        Case 9, 13, 27 'ignore TAB, ENTER, ESC
        Case Else
         Screen.ActiveControl.Dropdown
         ' Set the flag indicating that the control has been dropped down
         mDone = True
       End Select
      End If
     End Function


    Miriam Bizup Access MVP

    • Marked as answer by Brian D. Hart Wednesday, November 16, 2016 7:51 AM
    Tuesday, November 15, 2016 11:21 AM

All replies

  • Rather than using the length of the text entered as the criteria for dropping down the list, use a module level Boolean variable to track whether or not the list has been dropped down. 

    You can do this by declaring a Boolean variable at the top of your form's code (under Option Compare Database).  Set the Boolean variable to TRUE after dropping down the list, and reset it to FALSE in the exit event of the control, when the user leaves the combo.

    Option Compare Database
    
    ' Declare a flag to show whether or not the combo has been dropped down
    Dim mDone As Boolean
        
    Private Sub cboLastName_Exit(Cancel As Integer)
         ' Reset the flag to FALSE when the user is done with the combo
         mDone = False
    End Sub
    
    Private Sub cboLastName_KeyPress(KeyAscii As Integer)
       ControlDropDown (KeyAscii)
    End Sub
    
    Public Function ControlDropDown(Keystroke As Integer) 'public function that does the dropdown
      ' Check if the control has already been dropped down
      If mDone = False Then
      'If Len(Screen.ActiveControl.Text) = 0 Then
       Select Case Keystroke
        Case 9, 13, 27 'ignore TAB, ENTER, ESC
        Case Else
         Screen.ActiveControl.Dropdown
         ' Set the flag indicating that the control has been dropped down
         mDone = True
       End Select
      End If
     End Function


    Miriam Bizup Access MVP

    • Marked as answer by Brian D. Hart Wednesday, November 16, 2016 7:51 AM
    Tuesday, November 15, 2016 11:21 AM
  • I had not considered the rather obvious fact that only one control at a time could be dropped down, so a Boolean variable would work for this.

    Since I might use this on multiple forms, though, I declared the public Boolean variable for the flag in a standard module where my public function resides rather than in the form's module. I also had to set the value to False on irrelevant keystrokes, or after an ESC (to cancel change to current combo box), the next attempt to type will not drop down the box.

    In addition, if I press ESC, it undoes the current entry, and resuming typing does not re-trigger the dropdown. So I also had to add an explicit check for ASCII code 27 in the combo box KeyPress event and set the variable to false there.

    In my form's module
     Private Sub MyComboBox_Enter()
      ControlDropDownStatus = False
     End Sub

     Private Sub MyComboBox_KeyPress(KeyAscii As Integer)
     If KeyAscii = 27 Then ControlDropDownStatus = False
      If Not ControlDropDownStatus Then ControlDropDown (KeyAscii)
     End Sub

    In the standard module:

    Public ControlDropDownStatus As Boolean 

     Public Function ControlDropDown(Keystroke As Integer)
    Select Case Keystroke
        Case 9, 13, 27 'ignore TAB, ENTER, ESC
            ControlDropDownStatus = False
        Case Else
            Screen.ActiveControl.Dropdown
            ControlDropDownStatus = True
    End Select
     End Function



    Wednesday, November 16, 2016 6:52 AM
  • Putting the variable in the standard module with your function makes perfect sense. I missed the point that the function was NOT in the form's module the first time I looked at this question.

    The TempVars collection is also a great alternative to global/public variables if you are running Access 2007 or higher (.accdb format).  Two big benefits TempVars have over regular global variables:

    1.  They persist until the database closes, even through runtime errors which would cause public variables to lose their values.

    2.  You can refer to them in queries and property sheets, unlike public variables.

    If you are interested in more detail, this is a good article:

    Tempvars Collection


    Miriam Bizup Access MVP

    Thursday, November 17, 2016 10:53 AM