Multiple RecordSource Header and Sub Report

[DeeMark]

Have a Report with the Parent Report's recordsource set to a table containing company header style info. This populates the Report Header text boxes and labels.
A Sub Report Detail Section has been embedded into the PArent Report's Detail Section.
I am trying to assign the sub report with a secondary Record Source. Is this supported? I am using MS Access 2007.
Currently, struggling with the following code. Have run out of ideas / forums etc at this stage. If supported, I must be overlooking some syntax or other. My SQL skills are suspect I fear.

Private Sub SetUp()

' Set up the Report's Filter
Dim frm As Form_frmReportCriteria
Dim strSQL As String
Dim strWhere As String
Dim ptOption As Integer
Dim billingOption As Integer
'Set frm = Forms(FRM_CRIT)

'Check for user options to declare sqlString recordsource
ptOption = Forms!frmReportCriteria.optPatients.Value
billingOption = Forms!frmReportCriteria.optGpBilling.Value
Select Case (ptOption)
'This patient Case
Case Is = 1

   Select Case (billingOption)
        'selected patient receipt
        Case Is = 1
        strWhere = "WHERE ((([tblbillingrates.billingrateamount]*[hours])>0) AND ((tblappointments.appointmentdate) Between [Forms!frmReportCriteria.txtBeginDate] And [Forms!frmReportCriteria.txtEndDate]) AND ((tblappointments.paid)=True)) AND ((tblpatients.patientID) = Forms!frmpatientsappts.patientID)"
        Case Is = 2
        'selected patient invoice
        strWhere = "WHERE ((([tblbillingrates.billingrateamount]*[hours])>0) AND ((tblappointments.appointmentdate) Between [Forms!frmReportCriteria.txtBeginDate] And [Forms!frmReportCriteria.txtEndDate]) AND ((tblappointments.paid)=False)) AND ((tblpatients.patientID) = Forms!frmpatientsappts.patientID)"
        'selected patient statement
        Case Is = 3
        strWhere = "WHERE ((([tblbillingrates.billingrateamount]*[hours])>0) AND ((tblappointments.appointmentdate)<Now()) AND ((tblappointments.paid)=False)) AND ((tblpatients.patientID) = Forms!frmpatientsappts.patientID)"
   End Select
'All Patients Case
Case Is = 2
     
       Select Case (billingOption)
            'selected patients receipt
            Case Is = 1
            strWhere = "WHERE((([tblbillingrates.billingrateamount]*[hours])>0) AND ((tblappointments.appointmentdate) Between [Forms!frmReportCriteria.txtBeginDate] And [Forms!frmReportCriteria.txtEndDate]) AND ((tblappointments.paid)=True))"
            'selected patients invoice
            Case Is = 2
            strWhere = "WHERE((([tblbillingrates.billingrateamount]*[hours])>0) AND ((tblappointments.appointmentdate) Between [Forms!frmReportCriteria.txtBeginDate] And [Forms!frmReportCriteria.txtEndDate]) AND ((tblappointments.paid)=False))"
            'selected patients statement
         Case Is = 3
            strWhere = "WHERE((([tblbillingrates.billingrateamount]*[hours])>0) AND ((tblappointments.appointmentdate)<Now()) AND ((tblappointments.paid)=False))"
        
        End Select

End Select

'Sub Report RecordSource with "strWhere" parameter

strSQL = "SELECT tblpatients.insurerID, tblpatients.patientID, [billingrateamount]*[hours] AS  " & _
"InvoiceAmount, tblappointmentduration.hours, tblpatients.dob, tblpatients.pttitle,  " & _
"tblpatients.firstname, tblpatients.lastname, tblpatients.insref, tblpatients.active, " & _
"tblbillingrates.billingrateText, tblinsurers.providerno, tblbillingrates.billingrateamount, " & _
"tblappointments.appointmenttimestart, tblappointments.appointmentdate, " & _
"tblappointments.paid, CCur(Charging([tblpatients.distance],[InvoiceAmount],[tblbillingrates.billingrateID],[physioID])) AS " & _
"Expr1, tblappointments.physioID, tblappointments.baddebt, tblappointments.appointmentID, " & _
" tblinsurers.insurerID, tblinsurers.insurerName, tblpatients.distance, tblpatients.address1, " & _
"tblpatients.address2, tblpatients.town, tblpatients.county, tblpatients.country, " & _
"tblpatients.postcode, tblpatients.tel, tblpatients.mobile, tblpatients.distance, " & _
"tblbillingrates.billingrateID, tblpractices.practiceID, tblpractices.practiceName, " & _
"tblinsurers.contactTitle, tblinsurers.contactname, tblinsurers.address1, " & _
"tblinsurers.address2, tblinsurers.town, tblinsurers.county, tblinsurers.postcode " & _
"FROM tblbillingrates INNER JOIN (tblpractices INNER JOIN " & _
" ((tblinsurers INNER JOIN tblpatients ON tblinsurers.insurerID=tblpatients.insurerID) " & _
"INNER JOIN (tblappointmentduration INNER JOIN tblappointments ON " & _
"tblappointmentduration.apptdurationID=tblappointments.apptdurationID) ON " & _
"tblpatients.patientID=tblappointments.patientID) ON tblpractices.practiceID=tblpatients.practiceID) ON " & _
"tblbillingrates.billingrateID=tblappointments.billingrateID & strWhere"

'Apply the strWhere Parameter?????
'& strWhere;"


'Assign approprate strSQL as the Sub Report RecordSource, debug to finish
Reports!rptNoCriteriaBilling!rptdipmabilling.Report.RecordSource = strSQL
Debug.Print strSQL
'Success
Debug.Print strWhere
'Success

End Sub

Any guidance would be much appreciated.

 

[dhookom]

I would set the subreport record source to a saved query. Then use most of your code to update the SQL property of the saved query.

Without reading all your code, I would replace

(tblpatients.patientID) = Forms!frmpatientsappts.patientID)"

with

(tblpatients.patientID) = " & Forms!frmpatientsappts.patientID

This assumes the PatientID is numeric.


Duane
Hook'D on Access
MS Access MVP

 

[DeeMark]

Many tks dhookom,
1. Setting the Report RecordSource to a saved query, was one query - i.e was it supported? As I was facing SQL errors at the same time, I couldnt easily discover through debug if the setting of the saved query was causing the error and/or supported. Will try as you suggest again.

2. Assuming the remainder of the syntax holds good for SQL through the rest of the procedure, how would you recommend I apply the 'WHERE' criteria to the SQL statement.

3.Not shown, but the command to open the report, initially opens the 'Set Up' form (acDialog) to run the criteria options before opening the report proper. I'm noty sure if this the right way to go about it.

4.As the time learning this is disproportinate to the progress, I have set up the reports individually to move on, but would like to nail this procedure for later use, as it enhances the functionality of the application so much.

Tks so far
Deemark

 

[dhookom]

It is very difficult to change the Record Source of a subreport after the main report has started to open. That's why I suggest you use a saved query and change its SQL property. You could keep an unfiltered query like [qselBaseQuery], build your where string, and then apply them both to the subreport's record source query:

Currentdb.QueryDefs("qselForReport").SQL = CurrentDb.QueryDefs("qselBaseQuery").SQL & strWhere

I never use a dialog form opened from code in a report. I always have a form open to set criteria etc and then open the report.

Duane
Hook'D on Access
MS Access MVP

 

[DeeMark]

DHookom
Tks your prompt response,your advice is most helpful.I was almost there, so will go back and try the saved query method and apply the WHERE criteria. Noted your valued advice re the Dialog Form.
Many thanks
Deemark

 

[DeeMark]

Changed approach to this procedure. Opted to create a non-filtered query as a Main report recordsource. Dispensed with the subreport.
Created the user defined options in Group Option Frames. Created multiple 'strWhere' sql statements and applied to the DoCmd.Open Report statement.
Lost most time forgetting to put TRUE after the required Me.FilterOn statement.
Thank you
Deemark