Trying to program a dynamic search box (1 Viewer)

Hikari95

New member
Local time
Today, 08:29
Joined
Jul 16, 2012
Messages
5
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.

Code:
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

Super Moderator
Local time
Today, 08:29
Joined
Nov 30, 2011
Messages
8,494
Hello welcome to AWF.. :)

Try the OnChange event rather than KeyPress..
 

PeterF

Registered User.
Local time
Today, 09:29
Joined
Jun 6, 2006
Messages
295
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".
 

Hikari95

New member
Local time
Today, 08:29
Joined
Jul 16, 2012
Messages
5
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

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Jan 23, 2006
Messages
15,361
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)
 

Hikari95

New member
Local time
Today, 08:29
Joined
Jul 16, 2012
Messages
5
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 :)

Code:
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

Super Moderator
Staff member
Local time
Today, 04:29
Joined
Jan 23, 2006
Messages
15,361
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
 
Last edited:

Users who are viewing this thread

Top Bottom