Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland

asked on

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.

User generated imageShoppingTaxi-EE.accdb
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Massimo Scola

ASKER

I tried changing the query first .. adding the .Text propery .. but it didn't work.
Working with the variable .. that worked

Thanks a lot
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.