Best way to get search parameters? 1

[BaudKarma]

I'm setting up a search form for my database. Users will be able to enter various paramters, such as date range, record type, record status, etc. I'll put everything together into one great big SQL statement and apply that filter to my data.

I'm wondering about the best way to get the data from the user to the query. I can create an unbound field with a bunch of variables, or create a table to hold the search parameters and then base the search form on that table. Are there any good reasons to prefer one method over the other?



I try not to let my ignorance prevent me from offering a strong opinion.

 

[PHV]

Do a search in Access fora and FAQs for QBF (Query By Form)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[FancyPrairie]

I usually create a Report Criteria form that, depending on the data, contains single and/or mulit-select listboxes, comboboxes, date ranges, textboxes, option groups, and checkboxes. So I don't have to code the Where clause for each db I create, I created a function that builds the Where clause for me. I have since modified that routine for Tek-Tips. It can be found in the FAQ faq181-5497 It's really pretty simple to use. You only have to do 3 things to make it work.

1. Create a new module and copy and paste the code from the FAQ into the new module
2. Then set your tag properties as specified in the FAQ
3. Then open your report as specified in the FAQ

Most people that use the BuildWhere function in the FAQ make a mistake in defining the tag property for each of the controls. So I would suggest getting one of your controls to work first (before attempting the others). Then, instead of opening your report, issue this command:

Msgbox BuildWhere(Me)

Say, for example, your SQL statement (with a valid where clause) looks like this:

Select * From tblEmployees Where DateHired Between #1/1/2004# and #1/1/2005#.

Then you would have two text boxes on your form that represent the data ranges. The First text box would represent the Begin Date and the second box would represent the end date. Note (as explained in the FAQ), the first box would be named something like StartDate_BeginR and the 2nd box would be named StartDate_EndR. Note that the prefix is the same for both boxes (StartDate) and the suffix for the first one is _BeginR and the 2nd one is _EndR.

The tag property for StartDate_BeginR would then be (based on the Select statement above)

Where=tblEmployees.DateHired,Date;

That's all you need to do to make the date range work.

 

[BaudKarma]

Wow.

Here I'm working on reinventing the wheel, and you give me a monster pickup with 4-wheel drive and dual shocks all around.

That's a sweet routine. Thank you for writing it and making it available.

I try not to let my ignorance prevent me from offering a strong opinion.

 

[FancyPrairie]

BaudKarma,

Thanks for the compliment.

One thing you might want to consider doing (if you haven't already done so) is to create a library database and insert the code from the FAQ into your library database. Then you can call the BuildWhere routine from within any database you create.

To create a library database, simply create a new database. Then create a new module (as you did before) and copy and paste the code from the FAQ into the new module. Close the new database and open a module in your original database. Now goto Tools | References and set a reference to your new (library) database. Note that you may have a conflict now because you have routines with the same names (i.e. BuildWhere in both your original db and the library db). Just get rid of the one in your current database.

I would also suggest using some kind of naming convention for your library routines. For example, if you work for IBM then change the name of BuildWhere to IBM_BuildWhere. And never use the prefix "IBM_" in any of your dbs except the library db.

You'll find the library db to be a real benefit as you add stuff you find here and elsewhere.