Multiple search criteria

[Jan Il]

Hi all - Access 2003 - XP Pro

I have a form on which the client would like to have a control in which they

The desire is that on the form they can select the category from the
dropdown or typing in the category Cable. Then when they click OK, it would
open the display form and show all the assorted cables.

However, if they need to narrow the search down further to a specific type
of cable they would like to be able to click an Advanced Search button with
a window for them to enter additional specific information or keywords such
as:

USB - mouse - optical - cordless - etc.

and then display results of that search. Never having used this process, I
am not sure which function or type of control would be needed to do this.
I've looked in the Help files under the various functions, and using
'multiple search criteria,' and found a few functions that could possibly be
the correct one to use, but, not sure if I am using the correct wording for
what I want to do.

What would be the correct function name or type that I need to use for this
kind of search? And where?

Jan
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.

 

[MacDermott]

Hi, Jan!

A few thoughts:
1. Your OK button will probably use DoCmd.OpenForm to open the form which
shows the cables (or whatever category you select). I'm assuming that
you've got all that data (for all categories) in a single table. The task
is then to filter the form, so that it shows only the items you want. You
can use the Criteria argument of OpenForm to do that; you just have to build
a string which shows which items you want.
2. You're presumably using a combobox to select the category. I'll call
the combobox cboCategory; your Criteria string can be as simple as this:
strCriteria="Category='" & cboCategory & "'"
3. Now for your advanced search; you could open another window (form) to do
this, but it might be simpler just to have another combobox on the same
search form. If you want to get fancy, you can play with its Visible
property in the AfterUpdate event procedure for cboCategory; in any case,
you'll probably want to requery it there. If you leave it on that same
search form, you can add another line to the code behind your OK button,
something like this:
if cboAdvanced & "" <>"" then ' this checks whether there is any
entry in the second combobox
strCriteria = strCriteria & " and Category2='" & cboAdvanced "'"
4. As I write this, and reread your post, I realize that I'm assuming you
have your data structured with a first Category field, which will show Cable
or something else, and a second Category2 field, which may show something
like USB, mouse, etc. (I've never heard of a mouse cable being sold
separately from the mouse, but so what?) If you have some different
structure, some of the above might need to change.

HTH
- Turtle

 

[Jan Il]

Hey Turtle! How are you!? ))

Hi, Jan!

A few thoughts:
1. Your OK button will probably use DoCmd.OpenForm to open the form which
shows the cables (or whatever category you select). I'm assuming that
you've got all that data (for all categories) in a single table. The task
is then to filter the form, so that it shows only the items you want. You
can use the Criteria argument of OpenForm to do that; you just have to
build
a string which shows which items you want.
2. You're presumably using a combobox to select the category. I'll call
the combobox cboCategory; your Criteria string can be as simple as this:
strCriteria="Category='" & cboCategory & "'"
3. Now for your advanced search; you could open another window (form) to
do
this, but it might be simpler just to have another combobox on the same
search form. If you want to get fancy, you can play with its Visible
property in the AfterUpdate event procedure for cboCategory; in any case,
you'll probably want to requery it there. If you leave it on that same
search form, you can add another line to the code behind your OK button,
something like this:
if cboAdvanced & "" <>"" then ' this checks whether there is any
entry in the second combobox
strCriteria = strCriteria & " and Category2='" & cboAdvanced "'"
4. As I write this, and reread your post, I realize that I'm assuming you
have your data structured with a first Category field, which will show
Cable
or something else, and a second Category2 field, which may show something
like USB, mouse, etc. (I've never heard of a mouse cable being sold
separately from the mouse, but so what?) If you have some different
structure, some of the above might need to change.

Yes...you are very much on the money with all the above. The tables that I
have are as you say, and the Category 1,2,3 and 4 are in place.

I had hoped that the procedure could be conducted from the same form so that
there would not be a need for a separate form process just to do the one
function. The Advanced window code should indeed check to see which
category has been selected in the primary combo box, then base the extended
search on the criteria added in the Advanced widow against that catefory as
you have it outlined.

<"I've never heard of a mouse cable being sold separately from the mouse,
but so what?) ">

W'll....there is the QVS PS/2 Keyboard/Mouse "Y" spliter cable.....
http://www.bluebonepc.com/catalog/detail.asp?id=CB-QV-CC321Y

Sheesh!.........I can't leave you alone for 5 months, can I?

..... ;o)

Thank you very much for the information. I'll give this a try and see how
it works. Got a last minute Access basic training class to conduct on Wed.
that I need to prepare for, didn't find out until Thursday it was my
gig...so I won't have time to test it until after Wed.

......"I'll be back! <vbg>

Jan
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.

 

[Jan Il]

Hi Turtle

OK..this worked ok.. Howevre, the game plan has now changed. The client has
decided that he wants to have multiple category choice fields in the
tblpartsdescription, as:

PartID
PartCode - PK
PartDescription
MfgCode
SuggestedLocation
CategoryChoice1
CategoryChoice2
CategoryChoice3
CategoryChoice4
MfgRetailPrice
WKSuggestedRetailPrice
DescComments

However, in the tblCategoryList table there is only the CategoryCode number
from the tblCategoryList table:

The tblCateforyList has the following:
CategoryID
Category
CategoryCode - PK

His idea behind this change is so that he can have a wider description of
the part when he calls it up for display or print a report. Also, when he
is entering inventory he can select the various categories that he wants a
part to relate to.

I have tried to explain that this can all be done in a much simpler manner
with queries and filters, but, he insists on these fields in this table.
So...I am left with the task of trying to
tie all this together, and I everything I have tried thus far on what I can
find has not worked. I would never do it this way, as too much of this
information can be done on the fly, so I don't know how I can tie the
CategoryCode to the tblpartsdescription to put the CategoryCodes into the
assorted 4 fields in the tblpartsdescription.

Any suggestions at this point?

Jan