Trying to program a dynamic search box

[Hikari95]

I am trying to program a dynamic search box, wherein on the KeyPress event of a textbox, some SQL selects records from a table that are similar to whats in the textbox, however when I type something into the text box, it refreshes by nothing is displayed. When debugging, the textbox value is null, even though I have typed something in. Does anyone know why this might be happening? I have checked spelling countless times. My code can be found below. Thank you.

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
Dim db As DAO.Database
Dim rsSearch As DAO.Recordset
Set db = CurrentDb()
'Setting up db, selecting all records that are similar to what is in the textbox
Set rsSearch = db.OpenRecordset("SELECT * FROM tblCustomers WHERE Forename LIKE '" & txtSearch & "'", dbOpenDynaset)
Set listCust.Recordset = rsSearch
End Sub

 

[pr2-eugin]

Hello welcome to AWF..

Try the OnChange event rather than KeyPress..

 

[PeterF]

I'm guessing, you don't have "option explicit" on top of your code (near "option compare database")
txtSearch is a control on your form, but without telling VBA that, it's treatened as a variable you have to use "me.txtSearch" to use the control value. If "Option Explicit" is used the compiler would complain about not declaring a variable.

Depending on the type of control it may be nesseary tu use "me.txtSearch.value" or "me.txtSearch.text".

I assume listCust is the subform controlname of your form (use "me.listCust"), after setting the recordsource you need to requery the control: "me.listCust.requery".

 

[jdraw]

As Paul said, you may have chosen the wrong event.

You may get some more info at
http://office.microsoft.com/en-ca/access-help/order-of-events-for-database-objects-HP005186761.aspx

You may wish to do some research on the LIKE operator
http://www.techonthenet.com/access/queries/like.php

Good luck with your project.

 

[Hikari95]

Hello welcome to AWF..

Try the OnChange event rather than KeyPress..

I have tried it with the OnChange event also, unfortunately with the same results.

I'm guessing, you don't have "option explicit" on top of your code

I also do have Option Explicit at the top of my code, but no error is thrown up.

 

[jdraw]

Please show the procedure for the OnChange event.
Also with your LIKE, you should include (untested)

Set rsSearch = db.OpenRecordset("SELECT * FROM tblCustomers WHERE Forename LIKE '" & txtSearch & "*'", dbOpenDynaset)

 

[pr2-eugin]

Hmmm.. Have you checked out the method implemented by Peter? This is similar to what you are trying.. Dynamically searching multiple fields.

 

[Hikari95]

Thank you all for your advice, by looking at all of your answers I've managed to piece together a working solution, code found below

Private Sub txtSearch_Change()
Dim db As DAO.Database
Dim rsSearch As DAO.Recordset
Set db = CurrentDb()
'Setting up db, selecting all records that are similar to what is in the textbox
Set rsSearch = db.OpenRecordset("SELECT * FROM tblCustomers WHERE Forename LIKE '*" & txtSearch.Text & "*'", dbOpenDynaset)
Set listCust.Recordset = rsSearch
End Sub

 

[jdraw]

When you are searching for a name, you typically are searching "from the left".
I would remove the first asterisk (...Like '*" & txtSearch.Text & "*'" ....) which will make your search look for any substring starting with txtSearch.Text

You are (in my view) defeating the purpose of the ON Change - which repositions your list to only those records with Forename beginning with txtSearch.Text