Filtering Report Records

[streamranger]

I have a form in my database with a series of unbound combo boxes in the header to enter criteria to apply as a filter. It works very well, but the form is too wide to print on a single page. I have created a report using most of the same fields in the RecordSource, but formatted it to fit on a page width. My question is this, can I use the apply filter VBA code on my form to display the filtered records in my report?

 

[vbaInet]

1. How do you apply filter on the form?
2. Is the filter going to be the exact same filter you would want applied on the report?

 

[streamranger]

I have a button with an 'On Click' [Event Procedure] to filter the form, it is a multiple search criteria filter (see code below).
And yes, I want the report to apply the same filter. I was envisioning a third button on my form 'print filtered records' that would open the report, apply the filter and go to print preview.


the Apply filter code:


Private Sub ApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtFilterPermitNum) Then
strWhere = strWhere & "([Permit_Num] Like ""*" & Me.txtFilterPermitNum & "*"") AND "
End If

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

If Not IsNull(Me.txtFilterPermitDesc) Then
strWhere = strWhere & "([Permit_Desc] Like ""*" & Me.txtFilterPermitDesc & "*"") AND "
End If

If Not IsNull(Me.txtFilterStartDate) Then
strWhere = strWhere & "([Permit_AppRecDate] >= " & Format(Me.txtFilterStartDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtFilterEndDate) Then
strWhere = strWhere & "([Permit_AppRecDate] < " & Format(Me.txtFilterEndDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtFilterEng) Then
strWhere = strWhere & "([EngInit] Like ""*" & Me.txtFilterEng & "*"") AND "
End If
If Not IsNull(Me.txtFilterAPN) Then
strWhere = strWhere & "([Prop_APN] Like ""*" & Me.txtFilterAPN & "*"") AND "
End If
If Not IsNull(Me.txtFilterStNum) Then
strWhere = strWhere & "([Prop_StNum] = """ & Me.txtFilterStNum & """) AND "
End If

If Not IsNull(Me.txtFilterStreet) Then
strWhere = strWhere & "([Prop_Street] Like ""*" & Me.txtFilterStreet & "*"") AND "
End If
If Not IsNull(Me.txtFilterCity) Then
strWhere = strWhere & "([Prop_City] = """ & Me.txtFilterCity & """) AND "
End If
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

 

[vbaInet]

Pass the same filter in the WHERE argument of the OpenReport method.
By passing the filter I don't mean build the filter again, just use the Filter property of the form to return the filter already set and apply it in the OpenReport method.

 

[streamranger]

Thanks for your response. I know this should be a relatively easy thing to do, but I haven't done database work for MANY years and am feeling my way through this.

So, If I understand your suggestion, I can apply the filter property from the form to the WhereCondition of the OpenReport command. Do I have to name the filter?

What would the syntax of the WhereCondition be?

 

[vbaInet]

It's very simple, don't fret

So the filter you applied on your form is actually saved in the Filter property right? And the OpenReport method has a WHERE argument in it. This is what you do:

DoCmd.OpenReport "[COLOR="Blue"]ReportName[/COLOR]", acViewNormal, , Me.Filter

That's it! As long as the field names are the same and present.

 

[streamranger]

Perfect! Thank you. It works like a charm...now to do the same for five other search forms.

 

[vbaInet]

You're welcome!