Refresh Listbox Query (1 Viewer)

[monika_V]

Hi,

I'm trying to filter the contents of a listbox dynamically so that as I type, the listbox is filtered with the contents of the textbox.

So far, I have a query (Query1):

PARAMETERS dbb Text ( 255 );
SELECT Customers.Customer, Customers.ID
FROM Customers
WHERE (((Customers.Customer) Like "*" & [dbb] & "*"));

The reason for the query being that I can reuse it on other forms too. Running this manually shows that this works.

The form consists of a textbox (txtCustomer), a button (cmdAdd) and a listbox (lstCustomers).

I'm using the following code:

Private Sub cmdAdd_Click()
    
    Dim dbs As DAO.Database
    Dim qdfMembers As DAO.QueryDef
    Dim rsMembers As DAO.Recordset
    
    Set dbs = CurrentDb
    Set qdfMembers = dbs.QueryDefs("Query1")
    
    qdfMembers.Parameters("dbb").Value = txtCustomer

    Set rsMembers = qdfMembers.OpenRecordset
    Set lstCustomers.Recordset = rsMembers
    
    Set qdfMembers = Nothing
    Set dbs = Nothing
    
End Sub

Private Sub txtCustomer_Change()

    Dim dbs As DAO.Database
    Dim qdfMembers As DAO.QueryDef
    Dim rsMembers As DAO.Recordset
    
    Set dbs = CurrentDb
    Set qdfMembers = dbs.QueryDefs("Query1")
    
    qdfMembers.Parameters("dbb").Value = txtCustomer
    
    Set rsMembers = qdfMembers.OpenRecordset
    Set lstCustomers.Recordset = rsMembers
    
    Set qdfMembers = Nothing
    Set dbs = Nothing

End Sub

The trouble is that everytime I type text, the listbox does not update. The first character typed in to txtCustomer causes the listbox to update albeit incorrectly but nothing happens after any subsequent characters are typed. Stepping through, I can see all the code executes but nothing changes the listbox. Clicking cmdAdd does update the listbox so at least the query side of things is working!!

What is not happening here?

Thanks

 

[pbaldy]

In the change event, you have to use the .Text property:

Value vs Text

 

[Gasman]

Perhaps search here for FAYT by @MajP and use that?

 

[arnelgp]

you may try this code (replace your code):

Private Sub cmdAdd_Click()
    
    lstCustomers.RowSource = fnChangeListSource("")
    
End Sub

Private Sub txtCustomer_Change()

    lstCustomers.RowSource = fnChangeListSource(Me.txtCustomer.Text & "")

End Sub

Private Function fnChangeListSource(ByVal sText As String) As String
    Dim sql As String
    sql = CurrentDb.QueryDefs("Query1").sql
    sql = Mid$(sql, InStr(1, sql, "SELECT"))
    sql = Replace$(sql, "[dbb]", "'" & sText & "'")
    fnChangeListSource = sql
End Function

 

[MarkK]

I would do the query like this....

SELECT CustomerID, Customer
FROM Customers
WHERE Customer Like [prm]
ORDER BY Customer

I would do the code like this...

Private qdf_ As DAO.QueryDef

Private Property Get qdfMembers() As DAO.QueryDef
    If qdf_ Is Nothing Then Set qdf_ = CurrentDb.QueryDefs("Query1")
    Set qdfMembers = qdf_
End Property

Private Sub cmdAdd_Click()
    ResetList Nz(txtCustomer)
End Sub

Private Sub txtCustomer_Change()
    ResetList txtCustomer.Text
End Sub

Private Sub ResetList(SearchText As String)
    With qdfMembers
        .Parameters(0) = "*" & SearchText & "*"
        Set Me.lstCustomers.Recordset = .OpenRecordset
    End With
End Sub

• Textbox.Text is a String. It will never be Null.
• Property qdfMembers() retrieves the query once for the lifetime of the module. This is the most efficient way to repeatedly re-run it.
• Your query is more flexible if you supply the wildcards in code. The "Like" operator acts as "=" if you specify no wildcards. That can be useful. If you bake the wildcards into the SQL, you can't un-bake them.
• Each time you change the query text, Access must rebuild the entire query. This is the least efficient approach.
• This...

sql = Replace$(sql, "[dbb]", "'" & sText & "'")

...will result in an error if the user searches for "O'Malley's Supply House". This problem is completely solved with a parameter query.

You can also do this to improve performance...

Private Sub ResetList(SearchText As String)
    If Len(SearchText) < 3 Then
        SearchText = "*"
    Else
        SearchText = "*" & SearchText & "*"
    End If
    
    With qdfMembers
        If .Parameters(0) <> SearchText Then
            .Parameters(0) = SearchText
            Set Me.lstCustomers.Recordset = .OpenRecordset
        End If
    End With
End Sub

Only run the query if SearchText is changing, and only if there are a reasonable number of characters.
hth

 

[arnelgp]

maybe make it more efficient:

Private Function fnChangeListSource(ByVal sText As String) As String
    Static sql As String
   If Len(sql) < 1 Then
         sql = CurrentDb.QueryDefs("Query1").sql
         sql = Mid$(sql, InStr(1, sql, "SELECT"))
    End If
    fnChangeListSource = Replace$(sql, "[dbb]", "'" & Replace$(sText, "'", "''")  & "'")
End Function

 

[monika_V]

Wow, great ideas

Many thanks, I've got something I can work with and use for the future.