Search as you type not working

I would like to to search a listbox that has been populated by a query (which I saved in Access as qyrKunde_Suchen):

SELECT tblkunden.kundenid,
       [nachnameoderfirma] & "" & [vorname]                       AS Kunde,
       [strasse] & "" & [strassennr] & "," & [plz] & "" & [ort] AS Adresse,
FROM   tblanreden
       INNER JOIN tblkunden
               ON = tblkunden.tblanreden_id
WHERE  (( ( [nachnameoderfirma] & "" & [vorname] ) LIKE
                  "*" & [formulare] ! [frmsuchen] ! [txtcustomer] & "*" ))
        OR (( ( [strasse] & "" & [strassennr] & "," & [plz] & "" & [ort] )
              LIKE "*"
                   [formulare] ! [frmsuchen] ! [txtcustomer] & "*" ))
        OR (( ( tblkunden.telefon ) LIKE "*" & [formulare] ! [frmsuchen] !
                                         [txtcustomer] &
                                             "*" ))
        OR (( ( tblkunden.handy ) LIKE "*" & [formulare] ! [frmsuchen] !
        OR (( ( ) LIKE "*" & [formulare] ! [frmsuchen] !
                                       [txtcustomer] &

Open in new window

So the form is called frmsuchen - and the the search textbox is called txtcustomer.
The listbox is called lstCustomer.

I would like to requery every time the user types something in the textbox. I used the following code:

Private Sub txtSearch_Change()
    Me.lstCustomers.RowSource = "qyrKunden_Suchen"
End Sub

Open in new window

But nothing happens.
Isn't this query supposed to put the appropriate search text into the WHERE clause of the query?

I am using a German version of Access - hence the [formulare] and not [forms] in the SQL query.

Massimo ScolaInternshipAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
It is because your reference in your query syntax by default refers to the .Value property of the txtCustomer control, but that value does not change until you leave the control.  I've never tried to address the .text property of a textbox in a query, but you might try replacing:
[formulare] ! [frmsuchen] ! [txtcustomer]

Open in new window

[formulare] ! [frmsuchen] ! [txtcustomer].Text

Open in new window

Otherwise, create a tempvar and use that your query and in the Change Event of the textbox, something like:
WHERE ( [strasse] & "" & [strassennr] & "," & [plz] & "" & [ort] )
              LIKE "*" & [tempvars]![Customer] & "*" ))

Open in new window

And in the Change event:
    Tempvars!Customer = me.txtCustomer.Text

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Massimo ScolaInternshipAuthor Commented:
I tried changing the query first .. adding the .Text propery .. but it didn't work.
Working with the variable .. that worked

Thanks a lot
Dale FyeOwner, Developing Solutions LLCCommented:
Yeah, I've never tried using the text property, but now that I think about it, that property is only available from within the Change event.

Glad you got it working.

I've gotten to the point where I user tempvars almost all of the time rather than form references, because I can set the tempvar value in the immediate window and test the query without the form being open.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.