Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Combo Box - dependant on another Combo Box

Combo Boxes

Combo Box - dependant on another Combo Box

by  KenReay  Posted    (Edited  )
Combo box dependant on another combo box

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.

Assumptions

Assume we have two simple tables as below:

Tables

TblManufacturers
LngManufacturerId û Autonumber û PK
StrManufacturerName

TblModels
LngManufacturerId û Long ) PK
LngModelId û Autonumber )
StrModelName

Combo Boxes

We have two combo boxes (cboManufacturer and cboModel) the two combo boxes are on a form (Form2) so

CboManufacturer
RowSource: [color red] SELECT tblManufacturers.lngManufacturerId, tblManufacturers.strManufacturerName
FROM tblManufacturers
ORDER BY tblManufacturers.strManufacturerName;[/color]

ColumnCount: 2

ColumnWidths: 0;2.54cm

LimitToList: Yes

Events:

Code: 
Private Sub cboManufacturer_AfterUpdate()
    cboModel.Requery
End Sub

CboModel
RowSource: [color red]SELECT tblModels.lngModelId, tblModels.strModelName, tblModels.lngManufacturerId
FROM tblModels
WHERE (((tblModels.lngManufacturerId)=[Forms]![Form2]![cboManufacturer]))
ORDER BY tblModels.strModelName;[/color]

ColumnCount: 3

ColumnWidths: 0;2.54;0

LimitToList: Yes

Events:
[color red]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[/color]
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top