Changing Combo Box list when form field changes

[David Portwood]

I thought this would be easy but evidently it's not. I have a table of
Employees sorted by Division and Name. When the Division field changes on my
data entry form I want the Name combo box entries to reset with the
employees from the selected Division.

Can I do this? Any help would be greatly appreciated.

 

[ruralguy via AccessMonster.com]

The RowSource for the ComboBox needs a reference to the Division control and
the AfterUpdate event of the Division control needs to Requery the ComboBox.

 

[Ken Snell \(MVP\)]

See this article:
http://www.mvps.org/access/forms/frm0028.htm

 

[Hunter57]

I thought this would be easy but evidently it's not. I have a table of
Employees sorted by Division and Name. When the Division field changes on my
data entry form I want the Name combo box entries to reset with the
employees from the selected Division.

Can I do this? Any help would be greatly appreciated.

Hi David

Sure you can do it!


For illustrative purposes let's say cboDivision is the combo box where
you select the Division.
Also, cboName is the Combo Box of Employee Names you want to change
based on the value of the cboDivision Combo Box.

Assuming you know some VBA, if you have several Divisions this is one
solution:

Let's say your Division Combo Box lists the following Divisions:
Managment, HumanResources, Shipping, Sales

Here is some sample VBA Code to use in that Combo Boxes After Update
Event:

Private Sub cboDivision_AfterUpdate()

Select Case Division
Case Managment
Me.cboName.RowSource = "qryManagement"
Case HumanResources
Me.cboName.RowSource = "qryHumanResources"
Case Shipping
Me.cboName.RowSource = "qryShipping"
Case Sales
Me.cboName.RowSource = "qrySales"
End Select

End Sub

You can use as many Cases as you need.

You have to create the queries you need for the RowSource of the Combo
Box.
qryManagement, qryHumanResources, etc.



If you have only 2 Divisions, this is another way to do it:


Private Sub cboDivision_AfterUpdate()

If Me!cboDivision = Division1 Then
Me!cboName.RowSource = "qrymyDivision1"
Else:
Me!cboName.RowSource = "qrymyDivision2"
End If
End Sub

If you are not familiar with VBA or creating queries let me know and I
can help.

Hunter57
http://churchmanagementsoftware.googlepages.com

 

[David Portwood]

Thanks, guys. The Requery solved my problem. I'm sure the other methods
would work, too.