Making a keyword search

[timmy]

I want to create a textbox that filters the records and shows matching ones based on whats typed.

Access already has on of these at the bottom of a form, i just want to have it on the actual form, just for better usability really.

Thanks a lot!

[ajetrumpet]

you can do this by writing sql that uses the keyword LIKE. for instance, something like this behind a form would work:

sql = "select * from table " & _
"where field LIKE '*' & '" & me.textbox & "' & '*'"

me.formOrSubFormSource.recordsource = sql
me.formOrSubFormSource.requery

[timmy]

Thanks Adam, ill slot that code in and play about a bit. Ill let you know what happens.
thanks again .

[timmy]

right... i put this in a button next to the textbox:

 
Private Sub Searchbutton_Click()
   SQL = "select * from table " & _
   "where field LIKE '*' & '" & Me.Searchtext & "' & '*'"
   Me.formOrSubFormSource.RecordSource = SQL
   Me.formOrSubFormSource.Requery
End Sub

im getting this error on the "SQL =" bit:

Compile Error:
Variable not defined

ive got a feeling im putting the code in the wrong place... could you help me out?

btw, my textbox is called "searchtext", so i changed the name in the code.

[PrintShopSup]

Should "SQL" be in small case?

[timmy]

just tried that, it gives me the same error and changes them back to upper case

Thanks tho, any other ideas?

edit: just tried the same code in the on change event of the text box, getting exactly the same error.

[ajetrumpet]

Should "SQL" be in small case?

ummmm..NO.

just tried that, it gives me the same error and changes them back to upper case

Tim,

it does that because vba's intellisense stores the strings in memory that have been declared as variables.

Furthermore, did you realize that part of my code was actually TELLING you what to put in as substitutes for what I wrote? You copied it verbatim. You need to substitute your own control names into my sample, bud.

also, it could've broken there because you didn't declare the SQL variable... how much experience do you have with vba?

[timmy]

oh god... slightly embaressing, reading back on the code i cant believe i just copy and pasted... Ill have a fiddle with it when im back in work on monday.

thanks for your help, hopefully i can sort this out .

[ajetrumpet]

oh god... slightly embaressing, reading back on the code i cant believe i just copy and pasted... Ill have a fiddle with it when im back in work on monday.

thanks for your help, hopefully i can sort this out .

let us know if you need more assistance.

[timmy]

 
Private Sub Searchbutton_Click()
   SQL = "select * from Customers " & _
   "where field LIKE '*' & '" & Me.Searchtext & "' & '*'"
 
   Me.formOrSubFormSource.Customers = SQL
   Me.formOrSubFormSource.Requery
End Sub

tried that... getting the same error. (customers is the table im using)

it could've broken there because you didn't declare the SQL variable...

how do i declare SQL as a variable? Also, im not sure where im supposed to put my tables / form names and where the code is supposed to say "field" or "table", sorry im really not very good at vba

edit: OT but i found a post where you mentioned a FAQ on distributing a database, cant find it now. could you gimme a link ajetrumpet? thanks.