Set Listbox Filter w/ Multiple Textboxes

[Ganymede]

I'm trying to create a search form for my database with the following three fields: Claimant (name), Claimant ID and Claim ID. Ideally, I'd like it to look something like this:





(I put a 3 in the claimant field so no names would appear on the image).

Essentially, I'd like the listbox to display search results based on the criteria of all three textboxes (or less if some are left blank). Thus far, I can only get the listbox to display criteria from one of the textboxes.

The claimant textbox = txtPropertyClaimant
The listbox = lstSearchResults

Change event for txtProperty (when it works):

Private Sub txtPropertyClaimant_Change()
On Error GoTo Err_txtPropertyClaimant_Change
Dim strSource As String
strSource = "SELECT Claimant" & _
"FROM ADRSearchQ " & _
"Where Claimant Like '*" & Me.txtPropertyClaimant.Text & "*' " 
Me.lstSearchResults.RowSource = strSource

Exit_txtPropertyClaimant_Change: Exit Sub
Err_txtPropertyClaimant_Change: MsgBox Err.Number & " " & Err.Description
Resume Exit_txtPropertClaimanty_Change
End Sub

I'm aware that I could program txtPropertyClaimant to recognize data from either of Claimant, ClaimantID and/or ClaimID and consolidate all three into a single textbox. However, I really want to be able to search by more than one criteria at a time. What I attempted was something like this:

The claimant textbox = txtPropertyClaimant
The claimantID textbox = txtPropertyClaimantID
The claimID textbox = txtPropertyClaimID
The listbox = lstSearchResults

And I would put the following code as a change event for txtPropertyClaimant, txtPropertyClaimantID and txtPropertyClaimID:

On Error GoTo Err_<NameofTextbox>_Change
Dim strSource As String
strSource = "SELECT Claimant, ClaimantID, ClaimID " & _
"FROM ADRSearchQ " & _
"Where Claimant Like '*" & Me.txtPropertyClaimant.Text & "*' " _
& "Or ClaimantID Like '*" & Me.txtPropertyClaimantID.Text & "*' " _
& "Or ClaimID Like '*" & Me.txtPropertyClaimID.Text & "*' " _
Me.lstSearchResults.RowSource = strSource

Exit_<NameofTextbox>_Change: Exit Sub
Err_<NameofTextbox>_Change: MsgBox Err.Number & " " & Err.Description
Resume Exit_<NameofTextbox>_Change

When I try this, I get error code 2185 "You can't reference a property or method for a control unless the control has the focus."

I assume that this is appearing because I reference the "property or method" for all three textboxes in the macro. Is there any way around this?

[pdebaets]

Try this when setting the value of strSource:

strSource = "SELECT Claimant, ClaimantID, ClaimID " & _
"FROM ADRSearchQ " & _
"Where Claimant Like '*" & Me.txtPropertyClaimant & "*' " _
& "Or ClaimantID Like '*" & Me.txtPropertyClaimantID & "*' " _
& "Or ClaimID Like '*" & Me.txtPropertyClaimID & "*' "