filter report with sub-reports

Question

I have a report with 2 sub-report.

I would like to be able to allow 2 behaviors with the same report.

1. Open it, show all records
2. Open it using a date criteria form (From, To), filter the subreports based on the selected dates

I am using VBA to open the report, and thought I could use the where clause, but the main report has no recordset, it is merely a blank container to house the other 2 sub-reports, so no WHERE to apply.

How can I do this?

QB

Answer 1

That can be pretty tricky.  If the subreports are always invoked via the criteria form, then you could use criteria for the date field in their record source query:

   Between Forms!thecriteriaform.From And Forms!thecriteriaform.To OR Forms!thecriteriaform.To Is Null

But if the form is not open, you will be prompted the enter values for the parameters.

A different way that is not dependent on the form would be to use the OpenReport method's OpenArgs argument to pass the from and to values to the report:

   DoCmdOpenReport "the main report", _

      OpenArgs:= Format(From, "#yyyy-m-d#") & "~" & Format(To, "#yyyy-m-d#")

Then add code to each subreport's Open event procedure to set the subreport's Filter property.  A subreport's Open event can be tricky because setting most(?) report level properties can only be set one time and not every time the subreport appears in the main report.  I think the code could be something like this untested air code:

Dim Args() As Variant

Dim Initialized As Boolean

If Not Initialized And Not IsNull(Parent.OpenArgs)) Then

   Args = Split(Parent.Openargs, "~")

   Me.Filter = "datefield Between " Args(0) & " And " & Args(1)

   Me.FilterOn = True

   Initialized = True

End If

Answer 2

So you end up redefining the report's recordsource at runtime.

Is this an issue with database bloating? or performance?

I would like to investigate this option further.

Thank you for your help.

QB

Answer 3

Interesting, I tried something very similar but always got an error.

I tried your code (switching Dim Args() As Variant  to Dim Args As Variant - to make it work), and I get the same error:

"Record cannot be saved at this time. Complete the entry, or press <ESC> to undo."

I then assumed your code and my code had to be right (Hey this is advice from Marshall!) and finally managed to track down that my entire afternoon has been wasted because of my ErrorLog routine trapping Error 2101 improperly.  I added an error trap for that specific error and your code worked like a charm!!!

Thank you.

QB

Answer 4

You can temporarily change the where clause of the query for the subreports record source in the code that opens the main report.

In the code to open main report, first modify the query record source for the subreports then open the main report.

Here is some code that I use to modify the query record source for a subreport.

'----------------------start code

Private Sub SetRecordSourceCurrentFortnightSubrpt()

'sub report is called rsubLeaveHours

'- modify the SQL property of the subreport's query before you open report,

'e.g.:

'CurrentDb.QueryDefs("MySubreportsQuery").SQL = "SELECT ..."

On Error GoTo Err_Handler

'Last mod date 11/03/2011

'tested

    Dim strWHERE As String

    Dim strSQLModified As String

    Const cstrStub As String = "SELECT (DatePart('ww',[DateWork])+1)\2 AS Expr1, qryTimeSheetSickLeaveP1.ClientID, " _

        & "qryTimeSheetSickLeaveP1.DateWork, qryTimeSheetSickLeaveP1.WorkTypeName, " _

        & "Sum(qryTimeSheetSickLeaveP1.HrsWorked) AS SumOfHrsWorked, DatePart('yyyy',[DateWork]) AS Expr2 " _

        & "FROM qryTimeSheetSickLeaveP1 "

    Const cstrGroup = "GROUP BY (DatePart('ww',[DateWork])+1)\2, qryTimeSheetSickLeaveP1.ClientID, " _

        & "qryTimeSheetSickLeaveP1.DateWork, qryTimeSheetSickLeaveP1.WorkTypeName, " _

        & "DatePart('yyyy',[DateWork]) "

    strWHERE = " Where " & CurrentFortnightStartEnd

    strSQLModified = cstrStub & strWHERE & cstrGroup

    'Debug.Print strSQLModified

    CurrentDb.QueryDefs("qryTimeSheetSickLeave").SQL = strSQLModified

Exit_Handler:

    Exit Sub

Err_Handler:

    Debug.Print Err.Number, Err.Description, "SetRecordSourceCurrentFortnightSubrpt", pstrMdl, Now

    Resume Exit_Handler

End Sub

'---------------------end code

Note that CurrentFortnightStartEnd is a function that supplies the correct dates as a where clause to the sub called SetRecordSourceCurrentFortnightSubrpt.