Finding Records using cascading combo boxes

[Guest]

I have a query coming from 2 tables, with 5 fields Year, YearID,
ManufacturerID, ManufacturerName, and Amount.
I would like to be able to update the manufacturerName combobox and then
filter the year combo box based on the manufacturername and then find the
right information in the amount field for the right record based on the 2
combo boxes. in other words can I filter the second combo box based on the
first combo box and then find the right amount from the result of those 2
combo boxes?

 

[Guest]

For the manufacturer's combo, change the RowSource SQL to something along the
lines of
SELECT DISTINCT manufacturerName, manufacturerId FROM YourQuery ORDER BY
manufacturerName,
then change the BoundColumn to 2 and set the ColumnCount to 2
In it's AfterUpdate eventHandler, put in code along the lines of
cmbYear.RowSource = "SELECT DISTINCT [year], yearId FROM YourQuery WHERE
manufacturerId = " & cmbManufacturer.Value & " ORDER BY [Year];"
For the year combo, change its BoundColumn to 2 and set the ColumnCount to 2.

If the query is the RecordSource to the form and the Amount text box is
bound to the Amount column in the query, then you can put code into the year
combo's AfterUpdate eventHandler along the lines of
DoCmd.ApplyFilter , "manufacturerId = " & cmbManufacturer.Value & " AND
yearId = " & cmbYear.value

Hope This Helps
Gerald Stanley MCSD