3 Combo boxes on a form. Should be easy?

  • Thread starter Thread starter klufkee
  • Start date Start date
K

klufkee

Hi all,

I have a DB with 6 tables. The tables are all related to each other. To
make it simple let's say one table 'tbl1' has the names of businesses
and 'tbl3' has the names of the companies that they deal with.
Essentially there are more records in 'tbl3' than 'tbl1' but this is
normal from what I have seen here. I have created a form that will show
the records as they are related to each other. The form will bring up
7157 'combined records' and as you scroll through them, you see that
the 'biz_name' field in 'tbl1' is repeated many times as the 'co_name'
in 'tbl3' changes. (I'm just trying to give the most basic example
here, please bear with me.) There are 3 criteria that I would like to
filter by on this form; 'biz_name', 'co_name' and 'policy_number'. I
have created 3 combo boxes on the form and they are showing the
requisite information. ('bix_name' combo box is showing the values from
that field in 'tbl1' and so on.) What I cannot seem to do is have the
results on the rest of the form filter to show only records that match
what was selected in the combo box (or boxes but I'd be happy with one
of them working for now.) When I click on a value from the combo box
nothing happens. It still shows me all 7157 records on the form.
Ideally, I would like to be able to select a value from the first box,
have the form results filtered by that, and be able to the select a
value from the next box and filter even further. So far, when selecting
from the first box it is not filtering the results or limiting the
values in the next combo box. I'm so new to this that it hurts, so if
you want to keep someone from beating their head against a wall (or
monitor,) please be somewhat non-technical in your replys. Any help is
more than greatly appreciated and will be repaid promptly with large
quantities of pure gold bullion. (Sorry but at this stage, humor is my
only respite.)

Thanks to any and all,
Bill
 
This is the standard old cascading combos situation. The concept is really
pretty simple.
Combo2 should have as its row source a query that is filtered on the value
in Combo1. Combo3 should have as its row source a query filtered on the
value in Combo2. It may require filtering on both 1 and 2, depending on your
data structure.
Then all you have to do is in the After Update event of Combo1, requery
Combo 2 and in the After Update event of Combo2, requery Combo3.
 
Thanks so much Klatuu.

I attempted to implement the solution you described but being very new
to this it seems that I'm not doing something right. You mentioned
creating queries for combos 2 and 3, I was looking at an example that
used the method you described but its two tables were directly related
to one another. I was able to create a query and it seems to look ok.
(By checking the results against the data I have.) However, combobox2
is not filtering itself to show only the names that go with the value
selected in combobox1. Something that may be important is that the
table that combo2 pulls from in my query is inderectly related to the
table that combo1 pulls from. (The relation passes through 1 other
table first.) Is there a way around this or should I be looing at
another way to accomplish what I'm trying to do? I was trying to use
some code I found in the example for combo boxes but I'm not good
enough in VB to make it work.

Thanks,
Bill
 
Back
Top