Synchronizing Combo Boxes

[shannaj via AccessMonster.com]

Help! I am having trouble synchronizing two combo boxes. The first one is
Departments and the 2nd one is models. I need for the 2nd combo box to
display the appropriate models when a selection in the Departments box is
made. I have read up and have been trying the model that is advised to use
on the Microsoft website, but it does not work. It pops up a perameter value
box. With the formula that is given, it is hard to tell when to insert a
table name or field name. Can someone please advise what I am doing wrong?
I am just not understanding this one.

Private Sub Departments_AfterUpdate()
Me.Models.RowSource = "SELECT Models FROM" & _
" tblModels WHERE DeptID =" & Me.Departments & _
" ORDER BY Models"
Me.Models = Me.Models.ItemData(0)

End Sub

 

[shannaj via AccessMonster.com]

Thank You so much! Worked Great.

There's no real need to assign an SQL string to the Models combo box's
RowSource property here. Simply set the RowSource property in the Models
combo box's property sheet so it references the Departments combo box as a
parameter:

SELECT Models
FROM tblModels
WHERE DepartmentID = Form!Departments;

You can also build this query visually of course via the 'build' button
beside the RowSource property (that's the button with 3 dots). Note how you
can use the Form property here to refer to the current form rather than
having to fully reference it.

In the Departments combo box's AfterUpdate event procedure you then merely
need to requery the Models combo box:

Me.Models.Requery

In the above the referenced table name would be tblModels, the column in it
would be named Models and the combo box names would be Departments and
Models. The first combo box would have DepartmentID as its BoundColumn,
which is presumably a hidden first column, with the Department name from the
second column of the RowSource showing in the control.

Ken Sheridan
Stafford, England

Help! I am having trouble synchronizing two combo boxes. The first one is
Departments and the 2nd one is models. I need for the 2nd combo box to

[quoted text clipped - 12 lines]

End Sub

 

[Ken Sheridan]

Glad to have helped. While on the subject of correlated combo boxes you
might like to take a look at my demo of some slightly more complex ways of
using them at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

Thank You so much! Worked Great.

There's no real need to assign an SQL string to the Models combo box's
RowSource property here. Simply set the RowSource property in the Models
combo box's property sheet so it references the Departments combo box as a
parameter:

SELECT Models
FROM tblModels
WHERE DepartmentID = Form!Departments;

You can also build this query visually of course via the 'build' button
beside the RowSource property (that's the button with 3 dots). Note how you
can use the Form property here to refer to the current form rather than
having to fully reference it.

In the Departments combo box's AfterUpdate event procedure you then merely
need to requery the Models combo box:

Me.Models.Requery

In the above the referenced table name would be tblModels, the column in it
would be named Models and the combo box names would be Departments and
Models. The first combo box would have DepartmentID as its BoundColumn,
which is presumably a hidden first column, with the Department name from the
second column of the RowSource showing in the control.

Ken Sheridan
Stafford, England

Help! I am having trouble synchronizing two combo boxes. The first one is
Departments and the 2nd one is models. I need for the 2nd combo box to

[quoted text clipped - 12 lines]

End Sub