Form Searching

T

Tatakau

I have a table with all sorts of customer information in it (name, address,
city, state, phone, etc.). A main form can enter in the customer information
or search for records via the built-in search function. However, the
built-in function can only search by one criteria though.

I would like to create a form with multiple fields that allows the user to
search by multpile criteria, hit the search button, and open the main form
with the search results as the SQL source.

For example: Find all customers living in the zip code 55555
or: Find all customers with the last name Jones living in
Kerplackistan

The more I think about it, the more complicated it seems to get though. I
am a novice with SQL, and don't know what to code in when the user clicks the
Search button (like, what do I do about Null values?)

I am running Windows 98 and Access 2000... on an archaic Pentium 2. :p

Thanks!

Nick
 
6

'69 Camaro

Hi, Nick.
However, the
built-in function can only search by one criteria though.
Click to expand...

You may be interested in using the built-in filter functions instead. It
would save you a lot of coding. With the form in Form View, select the
"Filter by Form" button (icon looks like a silver filter at the top left
corner of a form) on the CommandBar. In the Zip Code field, use the combo
box to select 55555. At the bottom left of this filtered form are two tabs.
One is "Look for" and the other is "Or." Select the "Or" tab. You have
another new slate to make selections. In the last name field, use the combo
box to select "Jones." In the City field, use the combo box to select
"Kerplackistan."

The form will build a SQL statement for you with a WHERE clause that means:

"All records with Zip Code = 55555" OR "All records with last name = 'Jones'
and City = 'Kerplackistan'"

You'll never see the actual SQL statement, but to execute it just select the
"Apply Filter" button (icon looks like a silver funnel) on the CommandBar.
All of the records meeting your selection criteria will be displayed in the
form. It's a lot better than the built-in Search function, since Search only
finds one record at a time and, as you mentioned, can only search on one
criteria.

And to remove the filter, select the "Remove Filter" button on the
CommandBar. (It's actually a button that can be toggled between "Apply
Filter" and "Remove Filter.")

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
T

Tatakau

Excellent! This is exactly what I was looking for!

Sorta anyway :p I would still like to tweak it a bit.

My main concern is about the automatically generated drop-down boxes for
every single text field. With thousands upon thousands of records, and
everyone running archaic systems, I don't think the drop-downs are a very
good idea. Is there a way to turn this off (preferably with code that can
autoexec)?

Thanks again!

Nick
 
6

'69 Camaro

Glad it helped!
I don't think the drop-downs are a very
good idea. Is there a way to turn this off (preferably with code that can
autoexec)?
Click to expand...

No. However, there's nothing stopping the user from typing the actual value
in the combo box (as if it had been selected in the combo box), and then
applying the filter. As long as they spell it correctly, the appropriate
records will be displayed.

But allowing users to input data instead of selecting a value already in the
database means that typos can be made and the appropriate records won't be
displayed. For example, if someone typed 5555 for the Zip Code instead of
55555, then no records for this Zip Code would show up when the filter was
applied. The user needs to be on the ball and notice that something is
wrong and correct it. Unfortunately, wrong answers aren't always obvious.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
 
T

Tatakau

Ah well. I suppose the drop downs have some advantages. ^^

Last question:

I've been trying to create buttons directly on the form to mimc the Filter
by Form buttons. I found the command for applying a filter
(doCmd.ApplyFilter), but I can't get it to work right... and I can't find the
commands for anything else.

Here are the buttons I am trying to create:
* Apply/Remove Filter - Toggles whether or not the filter is being applied.
Coding a dynamic label ("Apply Filter" or "Remove Filter", depending on the
conditons) would be great, but I don't know how to check whether or not a
filter is active in VB Code. Or if there is even a filter in place that can
be applied/removed.
* Modify Filter - Opens the Filter by Form window to allow users to set
filter conditions.
* Clear Filter - Clears all the fields in the Filter by Form window.

Gunny, thank you so much for your help with this - I really do appreciate it!

Thanks a million!

Nick
 
6

'69 Camaro

Hi, Nick.

One of the drawbacks to the Filter By Form feature is that command buttons
are disabled while the user is selecting fields to filter on, so you can't
get all your wishes. While the Filter By Form is active, you'll either need
to use the CommandBar buttons or create a form with command buttons that
mimic the same functionality and have that form active when this form is
being filtered. (The CommandBar buttons are the way to go, unless you get
paid by the hour instead of by features added.)

You can create a form "Search" (modify filter) button, "FilterOff" button,
and an "ApplyFilter" button. A "Clear" button would be disabled while the
Filter By Form feature is active, so there's no sense creating one. The
FilterOff button, which turns off the current filter, should be invisible
unless the search feature is activated, so each time the search button is
clicked, this button should become visible.

In the Search button's OnClick( ) event:

Me!FilterOffBtn.Visible = True
RunCommand acCmdFilterByForm
RunCommand acCmdClearGrid

In the FilterOff button's OnClick( ) event:

Me.FilterOn = False
Me!SearchBtn.SetFocus
Me!FilterOffBtn.Visible = False

In the ApplyFilter button's OnClick( ) event:

RunCommand acCmdApplyFilterSort ' I'm guessing here, but I believe
it would work.
I don't know how to check whether or not a
filter is active in VB Code.
Click to expand...

Check the form's FilterOn Property:

If (Me.FilterOn) Then


HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
 
Top