multiple comboboxes to filter specific record

  • Thread starter Thread starter leon
  • Start date Start date
L

leon

I have table called tbl_all_data in which a record is uniqely defined
by four columns being:

Date, Name, Account and Counterparty. What i need to build is a form
(which is bound to the earlier mentioned table) in which a user can
navigate to every specific record he wants by using comboboxes. This
means that there should be four comboboxes, to be able to get to every
single record. I now how to do this if there is only one combobox
needed but with 4 comboboxes this is a whole different ball game since
the number of available choices in every combobox should be limited by
the choice made in a previous combobox. Further more, I only want to
see every value once: for example there are only two possible names
--> therefore in the names combobox dopdown i only want to see each
name ones and not a 100 times if there a 100 records with the same
name.

Does anyone know how to do this, which code i have to enter in the
four comboxes?
 
Leon

FYI - Access treats the words "Date" and "Name" as reserved words, and may
produce results other than what you intended if you use these as column
names.

Are you saying that each of your records is a unique combination of the four
fields?

Take a look at "cascading combo boxes". The general idea is that you will
use four unbound combo boxes to narrow your search down.

In the first combo box, the underlying query returns all unique values of,
say, your "date" field. In the AfterUpdate event of this first combo box,
you will requery the second combo box and set the focus there.

The second combo box is based on a query that includes, as a criterion, the
value in the first. When you select a value in the first and requery, the
second combo box will list ONLY those rows with the value selected in the
first. Add an AfterUpdate procedure in the second to requery the third.

In the third combo box, base the underlying query on criteria from what was
selected in both first and second. Add an AfterUpdate procedure ...

In the fourth, ... (the same approach applies).
 
Back
Top