Form to filter multiple criteria

[morganth]

Hi there - sorry for what is such a basic thing!

I have a large table with information about different cars, called "Car Metadata". In this, each car has a unique identifier ("Car Code"), the make ("Manufacturer Name") and the model ("Short Model").
What I want to do is create a form with dropdown menus.
I have a first combo box with all the makes in the Metadata table.
What I am having trouble with is that I want a second combo box underneath which would only show the models which correspond to the make selected in the first box.
E.g. someone could select Ford in the top box and the second box would only show Fiesta, Focus etc.
Once it has done that I want it to be able to write the Car Codes for the records which match up to the make & model selected to a new table. Just to complicate things further, there can be multiple records with the same make and model but different Codes, I want all these multiple codes to be written to the final table.

Thanks - hope that all makes sense!

 

[BlueIshDan]

So just to clarify:

[cboBrand] |--- Filters --> [cboMake] |--- Filters --> [cboModel]

 

[morganth]

There isn't a top level Brand, just [cboMake] |--- Filters --> [cboModel]
e.g.
Make = Ford
Model = Fiesta

morganth

 

[BlueIshDan]

Private Sub cboMake_OnChange()
      cboMake.RowSource = "SELECT DISTINCT make FROM cars WHERE make = """ & cboMake.Value & """"
      Me.RowSource = "SELECT * FROM cars WHERE make = """ & cboMake.Value & """"
End Sub

Private Sub cboModel_OnChange()
      Me.RowSource = "SELECT * FROM cars WHERE make = """ & cboMake.Value & """ AND model = """ & cboModel.Value & """"
End Sub

This is air code, but something like this.

 

[Frothingslosh]

What you're looking for is called 'cascading combo boxes'. Here's MicroSoft's explanation: http://office.microsoft.com/en-us/access-help/basing-one-combo-box-on-another-HA001173058.aspx

Basically, box 2's row source has a condition in it limiting it to values selected in box 1. Then, after box 1 is updated, you requery box 2.

There are also a few ways to do this via VBA.