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!
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!
you can do this by writing sql that uses the keyword LIKE. for instance, something like this behind a form would work:Code:sql = "select * from table " & _ "where field LIKE '*' & '" & me.textbox & "' & '*'" me.formOrSubFormSource.recordsource = sql me.formOrSubFormSource.requery
Thanks Adam, ill slot that code in and play about a bit. Ill let you know what happens.
thanks again .
right... i put this in a button next to the textbox:
im getting this error on the "SQL =" bit:Code:Private Sub Searchbutton_Click() SQL = "select * from table " & _ "where field LIKE '*' & '" & Me.Searchtext & "' & '*'" Me.formOrSubFormSource.RecordSource = SQL Me.formOrSubFormSource.Requery End Sub
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.
Should "SQL" be in small case?
Last edited by PrintShopSup; 03-11-2011 at 04:42 AM. Reason: wrong post
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.
ummmm..NO.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?
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 .
tried that... getting the same error. (customers is the table im using)Code:Private Sub Searchbutton_Click() SQL = "select * from Customers " & _ "where field LIKE '*' & '" & Me.Searchtext & "' & '*'" Me.formOrSubFormSource.Customers = SQL Me.formOrSubFormSource.Requery End Sub
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 vbaCode:it could've broken there because you didn't declare the SQL variable...
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.