Hello! First of all -- If anyone has a more efficient (SQL) way of doing
this, please let her know. Archgirl, I'm not very good with code at all, but
I can give you a simple roundabout way to do this. Let's see if I can
explain this clearly.
What you'll want to do is create 3 drop down fields. (You'll need to
utilize the Toolbox in the Form design view) Each field will have its record
source from a different Query.
Let's see if you can follow this:
Create QueryA:
Based off the appropriate Table.
Include only the field you want displayed in the first Combo Box
Utilize the GroupBy feature on this field (File Menu | View | Totals)
Create QueryB:
Based off the same Table as QueryA and QueryA
Connect the field in QueryA to the same field in the Table.
Modify the link so that the arrow is pointing towards the Table (Include
all records in the Query and only those records that match in the table.
Drag down the field from the Query.
Drag down the field from the Table that you want displayed in Combo Box2
Utilize the GroupBy feature on this field (File Menu | View | Totals)
Create QueryC:
Based off the same Table as QueryB and QueryB
Connect the field from the Query (not the same field as QueryA) to the
same field in the Table.
Modify the link so that the arrow is pointing towards the Table (Include
all records in the Query and only those records that match the table.
Drag down the field from the Query (Again not the one from QueryA).
Drag down the field from the Table that you want displayed in Combo Box3.
Utilize the GroupBy feature on this field (File Menu | View | Totals)
I hope you're with me so far...like I said it's roundabout.
Now in the Form, Create 3 Combo Boxes. Say you name them Combo1, Combo2,
and Combo3...OK? (If the wizard pops up, just click cancel).
In the Combo1 properties | Data Tab | Row Source Type: Table/Query and Row
Source: QueryA.
Combo2: Same thing except Row Source: QueryB.
Combo3: Same thing except Row Source: QueryC.
Now close and save your form. Say you call your Form, Form1. Open QueryB
in Design View.
In the criteria portion of the Query...Input this for the field from QueryA:
[Forms]![Form1].[Combo1] Save the Query.
Open QueryC. For the Criteria for the field you get from QueryB:
[Forms]![Form1].[Combo2] Save the Query.
In order for this to work you need to have all 3 Queries open when you
utilize the form. You can have them minimized or hidden. What you can do is
create a Macro, that Opens all 3 Querys in Hidden Window mode. Have this
Macro run whenever the form is opened.
In summary what you have is, each of these Combo Boxes is based off of a
Query. QueryA groups provides the choices for Combo1. When Combo1
populates, QueryB groups the choices for Combo2 for those records whose value
matches that of the value in Combo1. Make sense? Same thing with QueryC and
Combo3. This may sound confusing, let me know if you need more help with
this. If you want you can email me at Chaney34 "at" yahoo "dot" com, and
I'll provide you a phone number and gladly walk you through it over the
phone.
Just so you know, I tried this as I'm typing this down for you, so I know
it'll work. Hopefully though either you get it, or someone has an easier
solution.
Good Luck.
