Sequential Multiple Filtering

[nytwodees]

I am using Access 2000.

My form has 2 different option groups for filtering my records. Currently I
can filter my records on different conditions either way. I would like to
use both filtering groups either together or singularly (as I have now). For
example: I would press the "D" button on the "A-Z option group to select only
companies with names beginning with "D" followed by pressing the "Supplier,
Member" button in the MemberType option group to further filter the "D" named
companies who are only Supplier Members. How can I do this?

Thanks in advance for your help!

 

[Jeff Boyce]

I'm not there, and I don't know how you've structured your data, so the
following may not be applicable...

A commonly-used way to progressively narrow down choices is to use
"cascading combo boxes". Making a choice in the first combo box limits
what's available in the second. In your situation (as I understand it), you
could start with a combo box that lists MemberType. In the AfterUpdate
event of the first combo box, you could requery the second combo box. The
second combo box would list company names, but would use the MemberType in
combo box 1 to limit the list of names.

By using the AutoExpand property = Yes in both combo boxes, the user could
type the first (or first few) letters of what they're looking for and the
combo box would go to the first row with that combination.

This approach would not work, however, if you need to generate a list based
on only one or the other selection.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[nytwodees]

Hi Jeff:

Thanks for your suggestion.

I am not yet familiar with "cascading combo boxes." However, I will
investigate this option.

I would prefer to use the 2 option groups conjointly, if possible!

My data is structured very simply using these tables (plus others):

tbCompany - Has ID#(primary key), company name, address, contact, member
type, etc.

tbMemberType - Has the various member types (used as the Lookup Table
source for the MemberType field in tbCompany.

Thanks again,

Dan