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,
       tblanreden.anrede,
       [nachnameoderfirma] & "" & [vorname]                       AS Kunde,
       [strasse] & "" & [strassennr] & "," & [plz] & "" & [ort] AS Adresse,
       tblkunden.telefon,
       tblkunden.handy,
       tblkunden.email,
       tblkunden.info,
       tblkunden.aktiv,
       tblkunden.isteinmalkunde
FROM   tblanreden
       INNER JOIN tblkunden
               ON tblanreden.id = 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] !
                                       [txtcustomer]
                                       &
                                       "*"
                ))
        OR (( ( tblkunden.email ) 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.

screengrab.PNGShoppingTaxi-EE.accdb
Massimo ScolaAsked:
Who is Participating?
 
Dale FyeCommented:
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

with:
[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
    me.lstCustomers.Requery

Open in new window

0
 
Massimo ScolaAuthor 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
0
 
Dale FyeCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.