Multi-Search Form & Subform (1 Viewer)

travismp · Dec 4, 2006

I have one main table. There are about 5 main fields that my users seach often. Usually they just open the table and filter. I do not like that.

I want to build a form that has the 5 main fields available as search fields.

Name
SS
Company
Date
COC

are the fields most often searched for. Can I make a form that will open with a blank subform on the bottom and these 5 fields empty across the top. Once a user enters into one of the fields and hits enter it will bring up the those records?

Thanks.

Keith Nichols · Dec 4, 2006

Hi Travismp,

Short answer, yes.

I think the obvious route is to use comboboxes to filter a subform. Basically, you have unbound comboboxes populated by queries and the subform is filtered and requeried using the criteria from the combobox in the after update event.

It get's more complicated if you want the contents of the comboboxes to be filtered based on selections in other comboboxes, but this can be done.

There are many threads covering this topic and a lot of sample databases. Search for Cascading Comboboxes or filter subform or search and you should find something.

Regards,

travismp · Dec 4, 2006

Thank you for the short answer. It is good to know it can be done. I will see if I can fnd a sample Thanks.

Travis

John Big Booty · Dec 4, 2006

Would the search tool discussed in this thread be of any use?

travismp · Dec 4, 2006

getting there. I really like that concept of the multisearch all in one field. I will try to make something exactly like that form when I get a little more time. That looks over my head, but I will try it and see if I can get it to work. Thanks so far.

Oldsoftboss · Dec 4, 2006

Will THis Help. there are 3 Db's here

travismp · Dec 4, 2006

I think so. I really like the final DB on that zipped file. This stuff is beyond me but with all of the examples I might be able to weed throu it and make something work, maybe sloppy, but if I can get it to work I will be a happy camper.

Oldsoftboss · Dec 4, 2006

If you could post a cut down version of your db, we might be able to help.

Dave

travismp · Dec 4, 2006

Thank you all...

I would love it if someone could make this work. Like I said this is way over my head. Once I see a working sample it might make more sense.

Here is my DB. The main form is frm_GENERAL_SEARCH.

That is all I have so far. This is the way I want it to look. I just do not now how to make it function.

The fields we search most often are:

EmpSS
COC
CompName
LastName
PoolName

Zipped DB.

Thank you to anyone who has time to help.

Oldsoftboss · Dec 4, 2006

Not too hard, but what do you want to do with the results?
The most common thing to do is to open a second form and display full details.

travismp · Dec 4, 2006

Can't they just show in that subform I have embedded already? I just want them to type a Social Security Number, hit the button and it filter those results undernieth. Then iuf they want to search for all people that work for ABC construction they type it in and hit enter then the results are filtered to show only the results. I can have multiple search boxes if that is easier too, it does not have to all be done throu that one box. There can be a empty search box for all 5 fields if need be.

thanks.

travismp · Dec 4, 2006

also your avatar is very intertaining. It is amazing how simple men really are at times.

Oldsoftboss · Dec 4, 2006

try this....

Also, on a side note, you should compact and repair Db.

Go to Tools -> Database Utilities -> Comact and Repair

Reduced the Db size from 10.8Mb to 260kb

Dave

travismp · Dec 4, 2006

THAT IS FREAKIN AMAZING! AWESOME. THANK YOU SO MUCH. Can I do anything to repay you for your time?

Oldsoftboss · Dec 4, 2006

travismp said:
also your avatar is very intertaining.

Also known to lead to Dyslexia

Oldsoftboss · Dec 4, 2006

Cheers mate, glad to help

Dave

travismp · Dec 4, 2006

Also known to lead to Dyslexia

Little things get me side-tracked. Now my entire day will be shot.

I uploaded your changes into my actual database and it worked GREAT. I looked over the VB script & the new query, and still do not under stand it yet.

I will try to make the same thing from scratch in a junk database to see if it makes sense as walk through the entire process step by step. THANK YOU again for your help today. If you ever need anyone to help you move I have strong legs so I can carry the heavy stuff.

Travis

Oldsoftboss · Dec 5, 2006

If you look at the query, in criteria section at the bottom, if you put criteria in the same row then the query reads it as "AND". So the query would read "This column, AND this column, AND that column."
But if you put the criteria in a new row it becomes and OR. "This column OR that column"

The criteria in your query simply reads
Like * (wildcard) & field on your form & * (wildcard)

The criteria could also read
Like * (wildcard) & field on your form
or
Like field on your form & * (wildcard)

the last criteria are simply a different way of filtering the data entered in your search field.

The OnChange event on the form is structured in such a way that the data remains in the field and copied to a hidden field. If you added the requery to the visible field, it highlights it all, then the next letter that is typed overwrites the last.

Dave

travismp · Dec 5, 2006

The last line is what I was missing. I followed the logic of the query, it was the hidden field that was throwing me off. I had never seen this used at all... very nice. I have so muchto learn and no real time to throw at it. I am hoping to learn as I use. Thanks again for everything.

I am working on one more form then I think I am done with all of the stuff I could not do on my own. : )

Multi-Search Form & Subform (1 Viewer)

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Attachments

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

AWF VIP

Attachments

Registered User.

AWF VIP

AWF VIP

Registered User.

AWF VIP

Registered User.

Similar threads

Users who are viewing this thread