After Update Actions

[DKM]

still quite new to VBA code so dont know if this is possible.

I have a combo box i use to filter a form using the after update action. What i want to do is to also use this to set the record source for another combo box as well. I can make the code run indiviudally, but can only run one or the other. What i want to do is combine them into one private sub so that when i update the combo box it filters the form and sets the record source for the next combo box.

Codes i currently have are

Filter Form:

Private Sub Combo12_AfterUpdate()
Me.Filter = "[ServiceUnit] = " & "'" & [Combo12].Column(0) & "'"
Me.FilterOn = True
End Sub

Set RecordSource for Combo box

Private Sub Combo12_AfterUpdate()
Combo12.RowSource = "Select TblSU.JERef " & _
"FROM TblSU " & _
"WHERE TblSU.ExGroup = '" & Combo14.Value & "' " & _
"ORDER BY TblSU.ServiceUnit;"

Any help wold be appreciated.

 

[Premy]

What i want to do is to also use this to set the record source for another combo box as well.

But Both ComboBoxes are named "Combo12".

Could you pls comment the code as to make your intentions somewhat more explicit?

 

[DKM]

Sorry didnt make things clear. Start over (renamed the items), hope this explains better.

I currently have 1 form working working off a query (QryMainForm). on the query i have a number of records for each service unit. i have a combo box (SuName) which i currently use to filter the form based on which service unit is selected using this code

Private Sub SUName_AfterUpdate()
Me.Filter = "[ServiceUnit] = " & "'" & [SUName].Column(0) & "'"
Me.FilterOn = True
End Sub

this will filter the records without a problem. What i want to do is to add a second action to this code that will set the rowsource for a second combo box (JERefFilter) (set up as a cascading combo box). using this code i can get the cascading combo boxes set up to work (i have done this on a different form to test the code). so where i select service unit A, it will then set the rowsource for the JERefFilter combo box.

Private Sub SUName_AfterUpdate()
On Error Resume Next
JERefFilter.RowSource = "Select QryMainForm.JERef " & _
"FROM QryMainForm " & _
"WHERE QryMainForm.ServiceUnit = '" & SUName.Value & "' " & _
"ORDER BY QryMainForm.JERef;"

End Sub

However when the selection is made in SUName i can only tell it to do one of these options. so i can either filter the records on the form, or i can use it to set the rowsource for a second combo box called JERefFilter what i want to do is combine these into one code so that when i make a selection in SUName it filters the form, and sets the rowsource for the JERefFilter combo box.

its the linking of these 2 into 1 that i dont know how to do.

 

[pono1]

However when the selection is made in SUName i can only tell it to do one of these options.

What happens when you combine the code in an attempt to perform both actions?

' Pseudo code
Private Sub SUName_AfterUpdate()

   FillOtherComboBox
   FilterForm

End Sub SUName_AfterUpdate()

Is an error thrown? Also, you may want to remove the On Error Resume Next line as you test things so you can see the error if and when it occurs...

Regards,
Tim