search/query box

[bholiday]

I would like to have a search box in a form that queries a table and returns the results. I would like it to work much like the filter except to use a text box. Oh and one more catch if it is possible I would like to have the * you use to search already in the box so when they don't have to type it in everytime. I'm not the greatest with access so if anyone can help I would appreciate it.

 

[jimbrooking]

bh-

One possibility is to have a text box into which the user enters the search word, and calculate and apply a new filter in the AfterUpdate event of the text box. The code for this might look like:

Private Sub tbxSrch_AfterUpdate()
If Nz(Me.tbxSrch, "") = "" Then
Me.Filter = ""
Me.FilterOn = False
Else
Me.Filter = "([Text] Like ""*" & Me.tbxSrch & _
"*"") Or ([Definition] Like ""*" & Me.tbxSrch & "*"")"
Me.FilterOn = True
End If
Me.Requery
Me.tbxSrch.SetFocus
End Sub

(This presumes you are searching a table with (at least) two fields: Text and Definition, and you want your filter to show records containing the search term in either field.)

The Filter uses the Like operator, and places a * before and after the search term, so the search term can occur anywhere in the field and still cause a "hit".

You could have the * placed in the search box when the form is open, using a default value for the text box, but the user(s) would surely mess it up, so it's best to add it in code so you know where it is.

Jim

 

[bholiday]

thanks

Thanks it works great. One more question you may have the answer for. I want the text box to now filter a subform in datasheet view. i changed all the context so it will work however I can't figure out how to get the code to run the textbox filter in the subform from what is typed in the main form.

 

[bholiday]

thanks

Thanks it works great. One more question you may have the answer for. I want the text box to now filter a subform in datasheet view. i changed all the context so it will work however I can't figure out how to get the code to run the textbox filter in the subform from what is typed in the main form.

 

[jimbrooking]

bh-

Send me your email address and I'll return a small zipped database with examples of both a plain search form and one using a subform. The trick in the subform is to get the reference to the subform's filter property to work. A handy reference page for this is

http://www.mvps.org/access/forms/frm0031.htm

Jim