Combobox Stop Autofilling Text portion from selected item

[DavidAustin]

Hi all,

I have a combobox on my data entry form. When a user types, the list section of the form auto-refreshes to display items containing the text. However, when a user then selects an item using the arrow keys, it automatically updates the text region. This isn't good for my purpose as it doesn't allow users to select items in the list box section beyond the top option. Is there a way to prevent this from happening?

I will post my code below for the auto-refreshing of the combobox to see if the problem lies in there but I don't think it's related to that.

Private Sub cmdLocationID_Change()
 
Me.cmdLocationID.RowSourceType = "Value List"
 
Dim db As Database
Dim qdf As QueryDef
Dim rs As DAO.Recordset
Dim intX As Integer
 
Set db = CurrentDb()
Set qdf = db.QueryDefs("qrySearchType")
qdf.Parameters("SearchTXT") = Me.cmdLocationID.Text
Set rs = qdf.OpenRecordset()
 
'remove all items in the combo box before querying to add them
With rs
    With Me.cmdLocationID
        For intX = .ListCount - 1 To 0 Step -1
            Call .RemoveItem(intX)
        Next intX
    End With
End With
 
'any results = add to list box
If rs.RecordCount <> 0 Then
    With rs
        .MoveFirst
        While Not .EOF
            Me.cmdLocationID.AddItem .Fields("LocationID") & ";" & .Fields("FileLocation")
            .MoveNext
        Wend
    End With
End If
 
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
 
If Nz(Me.cmdLocationID.Text, "") <> "" Then
    Me.cmdLocationID.Dropdown
End If
 
End Sub
 

[twinnyfo]

David,

please explain this a little bit better:

When a user types, the list section of the form auto-refreshes to display items containing the text. However, when a user then selects an item using the arrow keys, it automatically updates the text region. This isn't good for my purpose as it doesn't allow users to select items in the list box section beyond the top option.

I'm not sure I understand what is happening. What is meant by

it automatically updates the text region

?

Additionally, is "cmdLocationID" your Combo Box? It is named as a Command Button, which is very confusing to me (as well as anyone else who might have to troubleshoot/modify your database in the future). You also refer to it in your code as a "List Box". It might better be named "cboLocationID".

However, I am even more thoroughly confused by your code itself. Apparently, after a user changes the value of the combo box, you then reset the list of items in the combo box based on the selection of the combo box???????

Regardless, lines 15-33 of your code are completely unnecessary, and in fact your code might be cut down significantly. I would recommend changing your code to reflect building a query and then simply assigning that query as the Row Source for your Combo Box:

Private Sub cmdLocationID_Change()
    Dim strSQL As String
    strSQL = "SELECT * FROM qrySearchType " & _
        "WHERE SearchTXT ='" = Me.cmdLocationID.Text & "';"
    Me.cmdLocationID.RowSource = strSQL
    Me.cmdLocationID.Requery
End Sub

Hope this helps.

[DavidAustin]

The combo box has a list of locations, so when the user types, the list of options should only contain the locations which contain the string that the user has typed.

When the user pressed the down arrow, it selects the first option in the list portion, then updates the text in the textbox region as the same as the listbox portion and then updates the listbox... essentially not working as expected.

e.g. currently happening

Typing New in text portion -> filters results to New Amsterdam, New England, New York -> user presses down arrow wanting to select New England -> the combobox immediately updates the text portion to New Amsterdam as this is the selected option -> list box updates to contain just New Amsterdam

what i need to happen:

Typing New in text portion -> filters results to New Amsterdam, New England, New York -> user presses down arrow wanting to select New England -> textbox stays just saying New so the listbox part isn't requeried -> user highlights New England and presses enter -> job done!

Why I called it cmd rather than cbo... that is anyone's guess!

I have tried to implement your new code, the row source is just blank and has no options. I guess I have probably done something wrong!

The SQL for the query is:

SELECT tblLocation.LocationID, tblLocation.FileLocation
FROM tblLocation
WHERE (((InStr(1,[tblLocation].[FileLocation],[SearchTXT]))>0))
ORDER BY tblLocation.FileLocation;
 

[twinnyfo]

First, I would change the Combo Box Row Source Type to Table/Query and make sure (I think that was causing the error--and I'm not sure the .Requery is required in this case).

Then, in this case, I would recommend using a separate text box for a search field. Then, in the Text Box's AfterUpdate event:

Private Sub txtSearch_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT LocationID, FileLocation FROM qrySearchType " & _
        "WHERE SearchTXT ='" = Me.txtSearch & "' "
        "ORDER BY FileLocation;"
    Me.cmdLocationID.RowSource = strSQL    
End Sub

Then you can use the values of cmdLocationID once the user selects the value (again, I would recommend the AfterUpdate Event of that control).

Hope this gets you closer....

[DavidAustin]

I'm sorry Twinnyfo but now I'm completely lost. Nothing works now XD It comes up saying that the RecordSource false specified on this form or report does not exist.

So I have to have an unbound text box which the user types into. This should then filter the combo box to just have the items which contain the string that is entered into the text box, correct?

[twinnyfo]

Yes, txtSearch should be an unbound text box.

Also, if you could post the SSSQL for qrySearchType, that would help us troubleshoot the error on the Query side of things....

[DavidAustin]

Query SQL

SELECT tblLocation.LocationID, tblLocation.FileLocation
FROM tblLocation
WHERE (((InStr(1,[tblLocation].[FileLocation],[SearchTXT]))>0))
ORDER BY tblLocation.FileLocation;
 

SearchTXT Function

Function SearchTXT() As String
    SearchTXT = "Like *" & glSearchTXT & "*"
End Function
 

And the textbox after update sets glSearchTXT=Me.txtSearch

glSearchTXT = txtSearch
 
Dim strSQL As String
    strSQL = "SELECT LocationID, FileLocation FROM qrySearchType " & _
        "WHERE SearchTXT ='" = Me.txtSearch & "' " & _
        "ORDER BY FileLocation;"
    Me.cmdLocationID.RowSource = strSQL
 

[twinnyfo]

So, let's streamline the whole thing:

Using the basics of Post #4 above, plus the information you just provided, we can do this all using the After Update Event of your text box (with no necessary global variables):

Private Sub txtSearch_AfterUpdate()
    Dim strSQL As String
    strSQL = "SELECT LocationID, FileLocation " & _
        "FROM tblLocation " & _
        "WHERE FileLocation Like '" = Me.txtSearch & "*' "
        "ORDER BY FileLocation;"
    Me.cmdLocationID.RowSource = strSQL    
End Sub

Do you see how we are simply creating the entire SQL string programmatically? This can be very useful when you have many different controls affecting the parameters of your queries.

[DavidAustin]

I understand that, but it's still not working. It's still saying the same error:

"The record source 'False' specified on this form or report does not exist.

The name of the record source may be misspelled, the record source was deleted or renamed, or the record source exists in a different database.

In the Form or Report's Design View or Layout View, display the property sheet by clicking the Properties button, and then set the RecordSource property to an existing table or query"

[twinnyfo]

In the code above in Post #8,

insert the following between lines 6 & 7:

Debug.Print strSQL

and post what is shown in the immediate window. If the Immediate window is not displayed, hit Ctrl-G.

[DavidAustin]

The debug print just says: False

[twinnyfo]

Could you cut and paste the code you have in the AfterUpdate Event? Something is obviously not right. It should be a String, not a Boolean value.

[DavidAustin]

 
Private Sub txtSearch_AfterUpdate()
 
Dim strSQL As String
    strSQL = "SELECT LocationID, FileLocation " & _
        "FROM tblLocation " & _
        "WHERE FileLocation Like '" = Me.txtSearch & "*' " & _
        "ORDER BY FileLocation;"
    Debug.Print strSQL
    Me.cmdLocationID.RowSource = strSQL
End Sub
 

[twinnyfo]

And the error is occurring at line 10?

Are there any records that "should" match the search criteria?

This is really strange.....

[jforbes]

This part is getting evaluated instead of concatenated:

"WHERE FileLocation Like '" = Me.txtSearch & "*' " & 

The Equal sign should be an Ampersand:

"WHERE FileLocation Like '" & Me.txtSearch & "*' " & 

[twinnyfo]

@jforbes,

Thanks for the catch! That's what I get for freehanding code! I knew it had to be something simple.

@DavidAustin,

It should work now.........

[twinnyfo]

And I had that wrong all the way back in Post #2!!!

[DavidAustin]

Haha, it's always the simple things.

This updates the combobox as I had hoped, but I'm guessing there is no way to do it while the user is typing in the textbox so you can see the combobox being filtered while you type? This is why I tried it in the change event to begin with, but I'm guessing the combobox has no way of being dropped down when it doesn't have focus?

[twinnyfo]

Ahhhhhhhhhhhh! Now I finally understand what you are trying to accomplish! However, when I try to model this here, whenever I begin typing any value, because Combo Boxes select the first available values from their list as you are typing, my search always results in just one record (the first one that matches the letters typed).

Using your example, if my list has New Amsterdam, New England, New York, once I type in "N", "New Amsterdam" pops up in my combo box (because it is the first value that matches the "N". At this point, the combo box has "changed" and now holds the value for the LocationID associated with "New Amsterdam".

Now, in the OnChange Event, I am now creating a SELECT Statement that results in:

SELECT LocationID, FileLocation
FROM tblLocation
WHERE FileLocation Like 'New Amsterdam*'
ORDER BY FileLocation;

And, one can never continue typing to even get to "New England" or "New York", because those values are no longer in the list.

Plus, when you use the txtSearch and try to either set the focus on the combo box or force a dropdown, Access won't allow it.

So, I guess one of my questions would be why this methodology for manipulating a Combo Box when the inherent, built-in functionality of the combo box is to move to the correct item in the list, based on your typing?

Yes, in your example, one would have to type "New Y" to get to "New York", which is only five key strokes, or one could type "N" and use the dropdown (or Alt-DownArrow) and then select "New York", which would be third on the list. Unless you have thousands and thousands of potential list items, many of which all have similar beginnings, I see no "real" advantage.

To illustrate, open any dictionary, which may contain 100,000 different words. How many of them are so similar in spelling that one must type seven or eight letters before the list is whittled down to less than a handful?

Although trying to manipulate your combo box may be possible, we must weigh the usefulness of the method. The txtSearch method will "work" but certainly not in the way you desire. But it is an option at this point.

This was also a good exercise for me (not only in proofreading), as I have learned a few things that may just not be possible to do in Access.

I hope we have at least explored some possibilities together--although your problem is still, technically speaking, unsolved.

[jforbes]

This looked like a fun problem, so I mocked up a Form with just a ComboBox and tried to get it to work. I did, but it is a little quirky as Access really, really wants to autocomplete this for the user. I also tapped into the KeyDown Event on the ComboBox to ignore an Up or Down keypress. Mouse Clicks were throwing it off, so I added that to the ignore list also.

Hopefully, the code will get you closer to what you want:

Option Compare Database
Option Explicit
 
Private bLookupKeyPress As Boolean
 
Private Sub cboLookup_Change()
    Dim sSQL As String
    Dim sNewLookup As String
 
    If Not bLookupKeyPress Then
        sNewLookup = Nz(Me.cboLookup.Text, "")
        sSQL = "SELECT Field2 FROM [tblBunchOfStuff] "
        If Len(sNewLookup) <> 0 Then
           sSQL = sSQL & " WHERE Field2 LIKE '*" & sNewLookup & "*'"
        End If
        Me.cboLookup.RowSource = sSQL
        Me.cboLookup.Dropdown
    End If
    bLookupKeyPress = False
End Sub
 
Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
    Select Case KeyCode
        Case vbKeyDown, vbKeyUp
            bLookupKeyPress = True
        Case Else
            bLookupKeyPress = False
    End Select
End Sub
 
Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    bLookupKeyPress = True
End Sub
 

[twinnyfo]

@jforbes,

How did you get the sub to look only at what was typed so far, rather than the first value that pops up? That is where I was running into snags.....

[jforbes]

By using the ComboBox.Text property instead of the ComboBox.Value.

[twinnyfo]

I thought I had tried that without success (note the head scratching....). Nice work!

[jforbes]

Ahh, also make sure the AutoExpand Property of the Combobox is set to No. It fixed the quirky behavior that I was experiencing with the above code.

How to configure autofill option for ComboBox

[twinnyfo]

I am still getting the same result. By thte time the code moves to the OnChange Event, the Combo box has already populated with the first list item.

What am I missing here?

Still scratching head..... :-)

[twinnyfo]

Bingo! AutoExpand was the key.

Now I know how to do this!

Thanks much!

[DavidAustin]

Thank you so much guys! This is perfect :D
I wasn't sure it was going to be possible for a while but once again you've come up trumps!

[jforbes]

I really like the way that works. I'll probably end up using it in choice places. Thanks for the Question and Hepp.