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 "cmdLocatio nID" 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 programmaticall y? 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 & "*' " &