Querying (and populating) with combo boxes

[joltremari]

I have a table with 6 fields in it

Catagory
Name
State
City
Comments
Rating

On a Form I want to have my first combobox (Combo1) populate with the contents of the field -Catagory- this is done

When I select an item from Combo1 I would like to run a query that would populate Combo2 with the values from the Name field of the table Where Catagory is = Combo1

So if I select Banks from Combo1 then Combo2 would populate with the Name field of all the records with a Catagory of "Banks"

I started like this but this gives me a 'Type Mismatch' Error which I don't understand.

Private Sub Combo18_AfterUpdate()

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Name FROM CataQuery")  '<---Type Mismatch here

End Sub

If anyone has any suggestions on how to go about this I would greatly appreciate it.

JO

 

[madhouse]

There is another way of doing this which would be easier. Create 2 queries for both of the combo boxes so that the query for the first combo box would be something like:

SELECT Category
FROM tablename
ORDER BY Category;

Then the other query for the second combo box would be something like:

SELECT Name
FROM tablename
WHERE Category = Forms!formname!Combo1
ORDER BY Name;

Then on the AfterUpdate for Combo1 just put:

Me.Combo2.Requery


That should do the trick and it saves having open recordsets.

 

[joltremari]

Man did that do the trick! and it's much easier!!

Thank you,

JO