Search function for a form

[david.paton]

Hi,

I need help making a search function for a form. I have a form with details about telephone counselors and want to be able to place a text box at the top that allows me to search through all the fields in the tables that the form relates to or displays. Could someone help me please?

Thanks,
Dave

 

[david.paton]

I have tried putting a text box called txtSearch at the top of the form . I then have a query that I run that looks at what is in the text box and tries to match any fields to it. In the criteria for one of the fields, I have

Like "*&[forms]![frmCrisisSupportWorkers]![txtSearch]&*"

where frmCrisisSupportWorkers is the name of the form with the search box. I wanted it to search anywhere in each of the fields and when I get one working, I will add the criteria to other fields on different lines to make it an OR statement.

This doesn't appear to be working.

 

[vbaInet]

Whenever I see your name it reminds me of another David Paton whom I know.

It goes like this:

Like "*" & [forms]![frmCrisisSupportWorkers]![txtSearch] & "*"

And if you want something more robust you can look at this:

http://allenbrowne.com/ser-62.html

 

[david.paton]

Great, thanks. Now that I have that bit working, I wanted to have a button that when I press it on the form, it will search through the fields but then when it finds results, it will post the records that have the results in the form fields.

How would I do this?

 

[vbaInet]

If the search textbox is unbound and placed in the header section of your form, all you need to do in the Click event of the button is Requery the form.

 

[david.paton]

But how do I search for a letter or word and have every record returned that contains that letter or word in a field somewhere in the record, but in form view, so that you can see all of the related information?

 

[vbaInet]

You said you got that working so what's the difference between what you got working and what you want to achieve?
It's the same method you use to search a form.

 

[david.paton]

Sorry, my message made sense to me at the time but looking at it now, I can see it wouldn't make sense to others.

I meant that when I type it txtSearch and run the query, it returns the records that are similar to the search box. So that is the bit I got working, although, to search for something different, I need to enter it then hit a button that has requery loaded into it then open the query but I will get to that bit later.

I then wanted to have the results returned in the form view instead of just the table view so I can see related information to the items I searched for, for instance, if I search for all names with a B in them, it will also show me where they were from. Using this method, also allows me to see information from related tables that would not be apparent in a table view as using a table view returns the single table results. I hope that makes sense.

And finally, I wanted it to search as you typed.




Thanks

 

[vbaInet]

Bind the query to the form and ensure the textbox is unbound. And like I said before to re-run the search on the form, requery it.

To be honest, you're better off looking at the example in the link. It has all the instructions you need.

 

[david.paton]

How do I bind it as I thought you just put the query in the record source for the form but I tried that and that doesn't work?

 

[vbaInet]

Yep that's all you do. I'll re-iterate, to re-run the search you Requery the form behind the button's Click event.

 

[david.paton]

With binding the query to the form, I can't as it has the table bound to it that has all the data. Is a good solution to copy the form and bind the second to the query and when you press on the search button, it just opens the new form that is bound to the query?

 

[vbaInet]

I'm still going to say go through the link provided. This is what lots of people use as a basis for a search form and everything is explained within.

 

[jdraw]

David,
I agree with vbaInet regarding the Allen Browne link.

However, there is a modified universal search that will search through all text fields looking for a number of "search terms/fragments". When it completes the search, it lists the tables and records found, and allows you to click an item in the list to expand the record. It deals with multiple search terms and all text fields in all tables --more than you are asking. I mention it just for a reference in future.
Good luck.