Passing user selected date in query to multiple sub-reports

[riggsdp]

I have a report with four sub-reports, one for each type of delivery. On a form, the user selects what type of report they are needing, select a date, then press a button to generate the report. The "main" report has no query or record source associated with it and the sub-reports are not linked to the main report.

I know how to pass the query results to one form, but not sure how to pass the four queries to the correct sub-report.

Can I set the on load record source for each sub-report to a different query based on the option selected? And if so, how exactly do I pass the query for the record source?

For example, for srptItemDelivery on load:

    'get report to generate
   Select Case Forms.frmMainMenu.grpReportType
        Case 1 'items due prior to
            strSQL = Select variables from tblDeliveredItems where Date_Due < selectDate
        Case 2 'items due after
            strSQL = Select variables from tblDeliveredItems where Date_Due > selectDate
        Case 3 'items due on
            strSQL = Select variables from tblDeliveredItems where Date_Due = selectDate
    End Select

Me.Recordsource = TBD

-- Yes, I know the select code isn't written as it should be, just paraphrased it.

and for srptResubDelivery on load:

    'get report to generate
    Select Case Forms.frmMainMenu.grpReportType
        Case 1 'items due prior to
            strSQL = Select variables from tblDeliveredItems where Date_Due < selectDate and DeliveryIs = "Resubmission"
        Case 2 'items due after
            strSQL = Select variables from tblDeliveredItems where Date_Due > selectDate and DeliveryIs = "Resubmission"
        Case 3 'items due on
            strSQL = Select variables from tblDeliveredItems where Date_Due = selectDate and DeliveryIs = "Resubmission"
    End Select

Me.Recordsource = TBD

and for srptCommentsReceived on load:

    'get report to generate
    Select Case Forms.frmMainMenu.grpReportType
        Case 1 'items due prior to
            strSQL = Select variables from tblDeliveredItems where Date_Comments_Received < selectDate
        Case 2 'items due after
            strSQL = Select variables from tblDeliveredItems where Date_Comments_Received > selectDate
        Case 3 'items due on
            strSQL = Select variables from tblDeliveredItems where Date_Comments_Received = selectDate
    End Select

Me.Recordsource = TBD

and, finally, for srptDispositionDelivery on load:

    'get report to generate
    Select Case Forms.frmMainMenu.grpReportType
        Case 1 'items due prior to
            strSQL = Select variables from tblDeliveredItems where Date_Disposition_Sent < selectDate
        Case 2 'items due after
            strSQL = Select variables from tblDeliveredItems where Date_Disposition_Sent > selectDate
        Case 3 'items due on
            strSQL = Select variables from tblDeliveredItems where Date_Disposition_Sent = selectDate
    End Select

Me.Recordsource = TBD

Or is there a better/easier way to do this?


Also, how do I set the sub-report captions based on the report being run?

[pbaldy]

Generally speaking that type of thing should work. If you're asking how to get the date, concatenate the form reference in, using # as a delimiter:

strSQL = "Select variables from tblDeliveredItems where Date_Disposition_Sent = #" & Forms!FormName.selectDate & "#"