Save reports according to choice from a sub form

Question

I have a main form called (frmcarSearch) that displays table data called (tblCar).

The form contains three drop-down menu (cmbCar, cmbType, cmbGroup) that allow user to filter data and display them in a sub-form called (frmCarSub) and there are three buttons to save the filtered data btnPrint, btnPDF, btnExcel.

The question is: How to write a code for each button so that the report displays (or save) the data in the sub-form according to the choice from each drop-down menu?

---

The code for each combo box:

Private Sub cmbCar_AfterUpdate()
    Me.cmbGroup.Value = ""
    Me.cmbType.Value = ""

    Me.frmCarSub.SetFocus
    Me.frmCarSub.Form.Filter = "[CarNum]= '" & [cmbCar] & "'"
    Me.frmCarSub.Form.FilterOn = True
End Sub

Private Sub cmbType_AfterUpdate()
    Me.cmbGroup.Value = ""
    Me.cmbCar.Value = ""

    Me.frmCarSub.SetFocus
    Me.frmCarSub.Form.Filter = "[TypeName]='" & [cmbType] & "'"
    Me.frmCarSub.Form.FilterOn = True
End Sub

Private Sub cmbGroup_AfterUpdate()
    Me.cmbCar.Value = ""
    Me.cmbType.Value = ""

    Me.frmCarSub.SetFocus
    Me.frmCarSub.Form.Filter = "[CarGroupName]= '" & [cmbGroup] & "'"
    Me.frmCarSub.Form.FilterOn = True
End Sub

I used this code for btnPrint button

Private Sub btnPrint_Click()
    If IsNull([cmbCar]) Then
        DoCmd.OpenReport "rptCar", acViewPreview
    Else
        DoCmd.OpenReport "rptCar", acViewPreview, , "[CarNum]='" & [cmbCar] & "'"
    End If
End Sub

But the problem with this code is that I have to use three buttons for the three menus and this is illogical.

Thank you.

Answer 1

You could define a function such as the following with the module for your form:

Function FilterString() As String
    If Not IsNull(cmbCar) Then FilterString = " AND [CarNum]= '" & cmbCar & "'"
    If Not IsNull(cmbType) Then FilterString = FilterString & " AND [TypeName]= '" & cmbType & "'"
    If Not IsNull(cmbGroup) Then FilterString = FilterString & " AND [CarGroupName]= '" & cmbGroup & "'"

    FilterString = Mid(FilterString, 6)
End Function

Then, define another function such as:

Function SetFilter()
    Me.frmCarSub.SetFocus
    Me.frmCarSub.Form.Filter = FilterString
    Me.frmCarSub.Form.FilterOn = True
End Function

Then, the event handlers for each of your comboboxes become:

Private Sub cmbCar_AfterUpdate()
    SetFilter
End Sub

Private Sub cmbType_AfterUpdate()
    SetFilter
End Sub

Private Sub cmbGroup_AfterUpdate()
    SetFilter
End Sub

Finally, the Print button event handler can become:

Private Sub btnPrint_Click()
    If FilterString = vbNullString Then
        DoCmd.OpenReport "rptCar", acViewPreview
    Else
        DoCmd.OpenReport "rptCar", acViewPreview, , FilterString
    End If
End Sub

And the user also has the ability to filter by more than one field.