Filtering a report issues

[allie357]

Okay this code will work. However I need to add code so that it will
accept 3 more strings from 3 more fields txtStartDate, txtEndDate, and
txtNumber. Two of the fields need to be dates and filter on a field
called DateEntered. The other field needs to take the number entered
and filter for results >= than the CountofPolicy field.

Can somebody please help?


Also is there some way I can run the filter before running the report
instead of opening it?



Private Sub Apply_Filter1_Click()

Dim strRCName As String
Dim strDeptName As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rpt_Violations_by_RC_x
Violations") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for RCName field
If IsNull(Me.CboRCName.Value) Then
strRCName = "Like '*'"
Else
strRCName = "='" & Me.CboRCName.Value & "'"
End If
' Build criteria string for DeptName field
If IsNull(Me.cboDeptName.Value) Then
strDeptName = "Like '*'"
Else
strDeptName = "='" & Me.cboDeptName.Value & "'"
End If
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[RCName] " & strRCName & " AND [DeptName] " &
strDeptName
' Apply the filter and switch it on
With Reports![rpt_Violations_by_RC_x Violations]
.Filter = strFilter
.FilterOn = True
End With


End Sub

Private Sub CboRCName_AfterUpdate()
Me!cboDeptName.Requery
End Sub



Private Sub Form_Open(Cancel As Integer)
Me!CboRCName.Value = ""
Me!cboDeptName.Value = ""
End Sub

 

[J. Goddard]

Reports are not like forms - once they are open, you can't interact with
them (much).

My suggestion is to first make the record source of the report a query
or table containing all the data, unfiltered, including all fields that
might be used in criteria, and without grouping.

The report can do the grouping and summing for you.

Then use a form to enter the record selection criteria (as you are
doing), generate a criteria string (as you are doing), as use that
string as the linkcriteria in the docmd.openreport command; all you need
to do is put that command in your code (for your Apply filter button),
after you have put together the criteria string strFilter. You don't
need to check for report open or not, and you don't need to worry about
the filter properties.

John