CODEWITHSUNDEEP
ms-accesscomboboxvbams-access-2010
Andrew Neeld
Andrew Neeld

Reputation: 113

Microsoft Access - combo box dropdown errors

I am new to VBA/Access so I have been using queries/VBA code I find online to help me build my current form. I experiencing weird behavior with my combo box and i think it may be due to the VBA/queries I have attached to it.

The code below is used to make the combo box a dynamic search tool: as you type each letter, it reruns the query to update the list with only the last names that match the letters typed.

However, pressing the arrow keys/tab/enter does nothing. The only way to navigate/select a value in the dropdown is a mouse click. And when I click the value I want, the form populates the data from the that record (yay!) but the dropdown menu stays visible until i click the background of the form.

I want the dropdown to disappear as soon as I select the record I want, and if possible I want to be able to use the arrow keys to navigate.

The "On Change" event has this code:

Private Sub Combo1397_Change()

Dim strText, strFind
Combo1397.SetFocus

strText = Me.Combo1397.Text

If Len(Trim(strText)) > 0 Then

strFind = "[Last Name] Like '"
For i = 1 To Len(Trim(strText))
    If (Right(strFind, 1) = "*") Then
        ' When adding another character, remove the
        ' previous "*," otherwise you end up with
        ' "*g**w*" instead of "*g*w*."
        ' This has no apparent impact on the user, but
        ' ensures that the SQL looks as intended.
        strFind = Left(strFind, Len(strFind) - 1)
    End If
    strFind = strFind & "*" & Mid(strText, i, 1) & "*"
Next
strFind = strFind & "'"  


strSQL = "SELECT tbl_RC_Main.pk_CandidateID, tbl_RC_Main.[Last Name],tbl_RC_Main.[First Name], tbl_RC_Main.Email FROM tbl_RC_Main Where " & _
strFind & " ORDER BY [Last Name];"


Me.Combo1397.RowSource = strSQL

Else
    ' Show the entire list.
    strSQL = "SELECT tbl_RC_Main.pk_CandidateID, tbl_RC_Main.[Last Name],tbl_RC_Main.[First Name], tbl_RC_Main.Email FROM tbl_RC_Main ORDER BY tbl_RC_Main.[Last Name]; "
    Me.Combo1397.RowSource = strSQL
End If

Me.Combo1397.Dropdown
End Sub

Also, On the "After Update" event I have a "Search for Record" macro

Upvotes: 0

Views: 851

Answers (1)

JCro
JCro

Reputation: 696

Because you have Me.Combo1397.Dropdown in the Change event, the combobox will drop down again upon selecting a value. You should instead move Me.Combo1397.Dropdown to the Enter event.

If you also remove Combo1397.SetFocus (I can't see why it's needed) then you will be able to use the Enter/Tab/Arrow keys as well.

Upvotes: 0

Related Questions