Filter listbox via 2 text boxes

[MysticElaine]

Hello,

I am really new to Access, so I don't know much about coding except what I have read in a coding for dummies book ;D I have also tried to google this but can't get what I have found to work.

I have a listbox where the rowsource is a SQL statement. I didn't know if I should try that or just use a query for what I am doing. The listbox is based on our client table, which is a huge number of people, and is showing 3 out of 5 fields.

I have two text boxes, one for first name and one for last name.

I would like the listbox to filter as I search by either field OR by both. Right now, I have the on change event for each with a SQL statement with a Like '*" & Me.textboxname.text & "*' and then a Me.clientlist.rowsource= at the end. So I can get each text box to filter the listbox individually, but I can't get it to filter with both. I think it is a matter of the focus changing, but I could be wrong.

Please help. Thank you.

 

[Cronk]

Please supply the actual line of code that is used to set the rowsource, not extracts.

 

[MysticElaine]

my listbox, titled ClientList, has rowsource as:

[SELECT [Clients].[Client ID], [Clients].[First Name], [Clients].[Last Name], [Clients].[DOB], [Clients].[Client Name] FROM Clients ORDER BY [First Name], [Last Name], [DOB];

then for the first textbox on change event that searches the First Name field, I have

Dim ClSearchSQL As String
ClSearchSQL = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.[DOB], Clients.[Client Name] FROM Clients"
ClSearchSQL = ClSearchSQL & " WHERE (((Clients.[First Name]) Like '" & Me.[First Name].Text & "*'))"
ClSearchSQL = ClSearchSQL & " ORDER BY [First Name], [Last Name], [DOB]"
Me.ClientList.RowSource = ClSearchSQL

For the second textbox, I did the same thing. But I thought there should be a way to add the first name to the WHERE statement for the last name, but couldn't figure it out and then thought maybe it was a focus issue.

Private Sub Last_Name_Change()
Dim LNSearchSQL As String
LNSearchSQL = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.[DOB], Clients.[Client Name] FROM Clients"
LNSearchSQL = LNSearchSQL & " WHERE (((Clients.[Last Name]) Like '" & Me.[Last Name].Text & "*'))"
LNSearchSQL = LNSearchSQL & " ORDER BY [First Name], [Last Name], [DOB]"
Me.ClientList.RowSource = LNSearchSQL
End Sub

That was the first way I tried. I saw something online to try but it didn't work at all. It was:

Dim ClientSearchQry As String
Dim FNSearch As String
Dim LNSearch As String
Dim CustomCriteria As String

Private Sub BuildCriteria()
CustomCriteria = FNSearch & LNSearch

ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.[DOB], Clients.[Client Name] FROM Clients"
ClientSearchQry = ClientSearchQry & " WHERE ((" & CustomCriteria & "))"
ClientSearchQry = ClientSearchQry & " ORDER BY [First Name], [Last Name], [DOB]"
Me.ClientList.RowSource = ClientSearchQry
End Sub

Private Sub First_Name_Change()
If Me.[First Name].Text = "" Then
    FNSearch = ""
Else
    FNSearch = "((Clients.[First Name]) Like '" & Me.[First Name].Text & "*')"
End If

Call BuildCriteria
End Sub

Private Sub Last_Name_Change()
If Me.[Last Name].Text = "" Then
    LNSearch = ""
Else
    LNSearch = "((Clients.[Last Name]) Like '" & Me.[Last Name].Text & "*')"
End If

Call BuildCriteria
End Sub

 

[The_Doc_Man]

What I do is this: I start with a basic SQL query to populate my listbox. However, I keep it in parts - the "SELECT field-list FROM table" is essentially a constant but the listbox ROWSOURCE doesn't have to be. I keep that part of the query in a string. The FORM LOAD event just asserts the rowsource based on the constant plus a semicolon and then does a listbox.REQUERY

When I key something in to my modifier box, I have a LOST FOCUS routine for that box that computes the query in two stages.

First, it builds the component based on the box that just lost focus by putting a criterion clause (a WHERE clause without the word "WHERE") in a string. That string doesn't go into the rowsource... yet.

Second, it uses an IF statement to decide what to do with the strings associated with the two text boxes. (Actually, in my case I have as many as four filters and trust me, it starts getting really silly...) But what you do is look at the two strings that you have entered as WHERE clauses. You have three cases - both blank, one blank, or neither blank.

In the "both blank" case, you do nothing. Your row source is already correct.

In the "one blank" case, you can append the two strings (one of which is blank and one of which is part of a criterion clause) and then append THAT to the string that is the constant part of your query, with a WHERE separating the constant from the dynamic part.

In the "neither blank" case, tack the two partial clauses together separated by an AND, then add that to the end of your constant part with the requisite WHERE as the separator.

Reload the listbox ROWSOURCE, do a listbox.REQUERY, and exit the lost-focus routine.

Make each LOSTFOCUS do the first part of what I said as distinct routines, but make the second part a subroutine that you can call from EITHER (actually, BOTH) of the lost focus routines.

 

[Cronk]

You were so close to getting the solution, but that is learning.

I'll add/repeat what Doc has submitted.

Firstly, I prefer the AfterUpdate event (or sometimes OnChange with Timer) so the list box is not being queried every keystroke.

Use the event for both text boxes to call a subroutine, say SubUpDateList()

Generate the list box rowsource SQL using the content of both text boxes. Something like

...WHERE Clients.[First Name] Like '" & Me.[First Name] & "*' AND Clients.[Last Name] Like '" & Me.[Last Name] & "*'"

The only issue remaining is when a user wants to enter O'Donnell as a surname. You can get around this by substituting chr(34) for the single quote.

Incidentally, you would not need square brackets if your fields and controls did not contain spaces.

 

[MysticElaine]

Thanks Doc and Cronk for responding. Sorry for being slow, but I'm still a bit confused.

Cronk, it seems like you are saying something a bit different from Doc in that my second coding that I tried was doing what you said except maybe my If statements were a bit off.

Doc, I get most of what you are saying, except I don't understand how to do the IF statements...should I just put what I had in my second coding as a subroutine instead of part of the event. So... If box1.text="" then String="" Else If box2.text="" then String2="" Else String=criteria and String2=criteria?

Could either of you please attach coding that you use that I could see? I am a very visual person and it would help if I could see an example. Thanks again so much for helping.

 

[Cronk]

Elaine

You don't need IF statements.

If one or the other of the text boxes is blank the WHERE clause evaluates to, say,
[First Name] Like '*' AND [Last name] Like 'O'Donn*'

 

[MysticElaine]

I still can't seem to make it work and now it doesn't filter at all

I tried two different ways

Option A:

Option Compare Database
Option Explicit

Dim ClientSearchQry As String

Private Sub UpdateListbox()
ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name]"
ClientSearchQry = ClientSearchQry & " FROM Clients"
ClientSearchQry = ClientSearchQry & " WHERE (Clients.[First Name] Like chr(34)" & Me.[First Name] & "*Chr(34) AND Clients.[Last Name] Like chr(34)" & Me.[Last Name] & "*chr(34))"
ClientSearchQry = ClientSearchQry & " ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]"
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
End Sub

Private Sub First_Name_Change()
Call UpdateListbox
End Sub

Private Sub Form_Load()
ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name]"
ClientSearchQry = ClientSearchQry & " FROM Clients ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]"
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
End Sub

Private Sub Last_Name_Change()
Call UpdateListbox
End Sub

Option B:

Option Compare Database
Option Explicit

Dim FNSearch As String
Dim LNSearch As String
Dim ClientSearchQry As String

Private Sub UpdateListbox()
If FNSearch <> "" And LNSearch = "" Then
ClientSearchQry = ClientSearchQry & " WHERE (" & FNSearch & ")"
Else
    If FNSearch = "" And LNSearch <> "" Then
    ClientSearchQry = ClientSearchQry & " WHERE (" & LNSearch & ")"
    Else
        If FNSearch <> "" And LNSearch <> "" Then
        ClientSearchQry = ClientSearchQry & " WHERE (" & FNSearch & " AND " & LNSearch & ")"
        End If
    End If
End If
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
End Sub

Private Sub First_Name_LostFocus()
FNSearch = "((Clients.[First Name]) Like '" & Me.[First Name].Text & "*')"
Call UpdateListbox
End Sub

Private Sub Last_Name_LostFocus()
LNSearch = "((Clients.[Last Name]) Like '" & Me.[Last Name].Text & "*')"
Call UpdateListbox
End Sub

Private Sub Form_Load()
ClientSearchQry = "SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name]"
ClientSearchQry = ClientSearchQry & " FROM Clients ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]"
Me.ClientList.RowSource = ClientSearchQry
Me.ClientList.Requery
End Sub

I know this should be really easy, but I just can't seem to get it, sorry. Thanks for the help.

 

[Cronk]

You're so close.

However you need to learn debugging skills. Put a break point on the End Sub of your Update sub. Do this by putting the cursor on that line and press F9 function key.

Open the form and change one of the text boxes. The execution of the code will be paused at the break point line.

In the Immediate Window below the code window (press Ctrl+G if it is not showing) type

? Me.ClientList.RowSource

to show the SQL being generated.

You might see the problem but if you want, copy this into a new query and have Access give you the error.

Hint: The chr(34) has to outside the string quotes because it's literal value is "

 

[MysticElaine]

Ok, so after much research on the Chr(34), I got the WHERE code to work, I believe

ClientSearchQry = ClientSearchQry & " WHERE (Clients.[First Name] Like '" & Me.[First Name] & "*' AND Clients.[Last Name] Like " & Chr(34) & Me.[Last Name] & "*" & Chr(34) & ")"

with the immediate window for ? Me.ClientList.Rowsource giving:

SELECT Clients.[Client ID], Clients.[First Name], Clients.[Last Name], Clients.DOB, Clients.[Client Name] FROM Clients WHERE (Clients.[First Name] Like '*' AND Clients.[Last Name] Like "*") ORDER BY Clients.[First Name], Clients.[Last Name], Clients.[DOB]

However, now the listbox won't filter properly. I type in the first name text box and my listbox doesn't change. I click in the last name text box then click back into the first name textbox, backspace once, and then the listbox filters.

Any ideas? Thanks!

Edit: I believe it isn't filtering as I type because my WHERE clause doesn't have .text. Although I can add it to the Me.[First Name], I can't add it to the Me.[Last Name] as then I get the focus error.

Edit2: I got it to work!!! I just made a second subroutine so that the first text box calls the subroutine with Me.[First Name].text and Me.[Last Name] and then the second text box calls the other subroutine where it is just Me.[First Name] and Me.[Last Name].text. I don't know if there was a simpler way, but I will take it!

Thank you so so much for all of your help! If I could make you some goodies, I would