Filter Report based on forms filter

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I made a filtering form based on Allen Brown's "search form" example. I want
to use the string built for the form's filter to filter a report opened by a
command button on the form. I have an "and" and an "or" filter buttons on
the form. Right now my code will open the report but I can't get it to apply
the filter. Here's part of my code. Thanks.

Private Sub And_Search_1_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.MLO) Then
strWhere = strWhere & "([MLO] = """ & Me.MLO & """) AND "
End If

'... There's alot more If statements to build the filter string but I
didn't include them all.

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub
Private Sub Or_Report_Click()
On Error GoTo Err_Or_Report_Click
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "rptMLOBOOK", acViewReport, , Me.Filter
End If

Exit_Or_Report_Click:
Exit Sub

Err_Or_Report_Click:
MsgBox Err.Description
Resume Exit_Or_Report_Click

End Sub
 
Test the Form's FilterOn property: the Filter string is not destroyed even
with the filter is off.

Private Sub Or_Report_Click()
On Error GoTo Err_Or_Report_Click
Dim strWhere As String

If Me.FilterOn Then
strWhere = Me.Filter
End If
debug.print strWhere
DoCmd.OpenReport "rptMLOBOOK", acViewPreview, , strWhere

Exit_Or_Report_Click:
Exit Sub

Err_Or_Report_Click:
MsgBox Err.Description
Resume Exit_Or_Report_Click
End Sub
 
Thanks Allen. It worked.
Marcie

Allen Browne said:
Test the Form's FilterOn property: the Filter string is not destroyed even
with the filter is off.

Private Sub Or_Report_Click()
On Error GoTo Err_Or_Report_Click
Dim strWhere As String

If Me.FilterOn Then
strWhere = Me.Filter
End If
debug.print strWhere
DoCmd.OpenReport "rptMLOBOOK", acViewPreview, , strWhere

Exit_Or_Report_Click:
Exit Sub

Err_Or_Report_Click:
MsgBox Err.Description
Resume Exit_Or_Report_Click
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

bymarce said:
I made a filtering form based on Allen Brown's "search form" example. I
want
to use the string built for the form's filter to filter a report opened by
a
command button on the form. I have an "and" and an "or" filter buttons
on
the form. Right now my code will open the report but I can't get it to
apply
the filter. Here's part of my code. Thanks.

Private Sub And_Search_1_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.MLO) Then
strWhere = strWhere & "([MLO] = """ & Me.MLO & """) AND "
End If

'... There's alot more If statements to build the filter string but I
didn't include them all.

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub
Private Sub Or_Report_Click()
On Error GoTo Err_Or_Report_Click
If Me.Filter = "" Then
MsgBox "Apply a filter to the form first."
Else
DoCmd.OpenReport "rptMLOBOOK", acViewReport, , Me.Filter
End If

Exit_Or_Report_Click:
Exit Sub

Err_Or_Report_Click:
MsgBox Err.Description
Resume Exit_Or_Report_Click

End Sub
Click to expand...
Click to expand...
 
Back
Top