It is quite common to have a combo box from which you can choose a list of options, which then causes a second combo box to offer a list of options which are dependant on the first combo box.
The example below illustrates this.
Assume we have two simple tables as below:
TblManufacturers LngManufacturerId û Autonumber û PK StrManufacturerName
TblModels LngManufacturerId û Long ) PK LngModelId û Autonumber ) StrModelName
We have two combo boxes (cboManufacturer and cboModel) the two combo boxes are on a form (Form2) so
CboManufacturer RowSource: SELECT tblManufacturers.lngManufacturerId, tblManufacturers.strManufacturerName FROM tblManufacturers ORDER BY tblManufacturers.strManufacturerName;
Private Sub cboManufacturer_AfterUpdate() cboModel.Requery End Sub
CboModel RowSource: SELECT tblModels.lngModelId, tblModels.strModelName, tblModels.lngManufacturerId FROM tblModels WHERE (((tblModels.lngManufacturerId)=[Forms]![Form2]![cboManufacturer])) ORDER BY tblModels.strModelName;
Events: Private Sub cboModel_GotFocus() If Len(Trim(Nz(cboManufacturer, "") & "")) = 0 Then MsgBox "Please Specify Manufacturer first" cboManufacturer.SetFocus Else cboModel.Requery End If End Sub