Get Filter By Form Data

[agibsonsw]

Hello. Access 2007.
If someone uses the in-built Filter by Form feature is it possible to then press a button to produce a report based on the same criteria?
Thanks, Andy.

[HansV]

Try something like the following. It assumes that you have already created a report based on the same table or report as the form.

Code: Select all

Private Sub cmdReport_Click()
  Dim strFilter As String
  On Error GoTo ErrHandler

  If Me.FilterOn Then
    strFilter = Me.Filter
  End If
  DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strFilter
  Exit Sub

ErrHandler:
  If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
  End If
End Sub

cmdReport is the name of the command button and rptMyReport is the name of the report.

[agibsonsw]

Thank you. That's looks good. Andy.

[agibsonsw]

Hello again.
If someone uses the Or tab the procedure doesn't seem to work. Should Me.Filter also pick up the Or criteria? Thanks, Andy.

[HansV]

It should. Here is a screenshot of filter by form with something entered in the Or tab:

x170.png

And this is the filter after applying it:

x171.png

[agibsonsw]

Thanks.

[agibsonsw]

Hello again.
If there are no records found when using Filter by Form is it possible to produce a MsgBox rather than
the empty resultset? Thanks, Andy.

[HansV]

You can use the On Apply Filter event of the form. Let's say the record source of the form is named tblProducts, as in the above example.

Code: Select all

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  If ApplyType = acApplyFilter Then
    If DCount("*", "tblProducts", Me.Filter) = 0 Then
      MsgBox "There are no records satisfying the filter conditions.", vbExclamation
      Cancel = True
    End If
  End If
End Sub

This version cancels applying the filter, i.e. Filter by Form remains open. If you prefer to return to the form itself, with the filter turned off, you could use

Code: Select all

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  If ApplyType = acApplyFilter Then
    If DCount("*", "tblProducts", Me.Filter) = 0 Then
      MsgBox "There are no records satisfying the filter conditions.", vbExclamation
      Me.Filter = ""
    End If
  End If
End Sub

[agibsonsw]

Thank you.