CODEWITHSUNDEEP
ms-accessfiltermodulesubform
Alex
Alex

Reputation: 113

MS Access - Creating a module that filter subform by different comboboxes using VBA

I have a form with "some" comboboxes and 1 subform that is currently filtered just by the combobox1 with the following VBA code:

Private Sub cmbType_AfterUpdate()

Dim strSQL As String

strSQL = "SELECT [qryStore].[Type], [qryStore].[Model], [qryStore].[SN], " _
& "[qryStore].[ID], [qryStore].[Position], " _
& "FROM qryStore " _
& "WHERE (((qryStore.Type)='" & Me.cmbType & "'));"

Me.subfrmStore.Form.RecordSource = strSQL
Me.subfrmStore.Form.Requery

End Sub

I want to turn this code in a module so i can Call the module once for all the comboboxes of the form instead of duplicate this code for each individual combobox..

How can i achieve that?!

Upvotes: 1

Views: 105

Answers (1)

Doug Coats
Doug Coats

Reputation: 7117

Here:

Public Sub UpdateSubFormFromControl(ByRef ControlName as String)

    Dim strSQL As String
    strSQL = "SELECT [qryStore].[Type], [qryStore].[Model], [qryStore].[SN], " _
    & "[qryStore].[ID], [qryStore].[Position], " _
    & "FROM qryStore " _
    & "WHERE (((qryStore.Type)='" & Forms!MyFormName.Controls(ControlName).Value & "'));"

End Sub

This allows you to pass the control name to a sub and do the same exact thing. However, you'll notice I had to fully qualify the form. I think its a better practice to always fully qualify (specifically for reasons such as yours).

I also left out the requery logic - either add it to this or leave it in the code that calls it.

Enjoy!

Upvotes: 2

Related Questions