Changing the RecordSource for a Report

[chuckcoleman]

Hi, I have a report that has a sub report and that sub report has another sub report. I have in the last sub report in the On Load property the following code. Also, in the Record Source property in the Data tab of the last sub report I have that as blank, nothing is there. When I test the code it by doing a print preview it returns no records. I would have thought the On Load property would run the code below. What am I doing wrong?

Private Sub Report_Load()

Dim rpt As Report
Dim strSQL As String

If CurrentProject.AllForms("Re-Invoice Form").IsLoaded Then
 strSQL = "Select * From [Balance Due-Report-Paid-3-Reinvoice];"
Else
 strSQL = "Select * From [Balance Due-Report-Paid-3];"
End If
Set rpt = CreateReport
rpt.RecordSource = strSQL

Set rpt = Nothing
End Sub

 

[Pat Hartman]

The subreports may not be loading in the order you expect.

 

[Gasman]

Setting rpt to Nothing?
Why are you not using Me.

 

[chuckcoleman]

Pat, I don't think loading in the order is the problem. If I just open the last sub report by itself, it should produce records. It doens't produce any records. As far as rtp to Nothing I thought that just cleaned up/discarded the report after it's created.

 

[Gasman]

Yes, it does, but you are doing it in the load?
Try using me. for the recordsource line

 

[Pat Hartman]

Since subreports use master/child links, opening a subreport all by itself will not necessarily show what the subreport would show in context.

Your code doesn't seem to make any sense. What is "Re-Invoice Form"?

Is the problem that you want to open the report from more than one form and in one case, this subreport will be bound to qry1 and in other cases bound to qry2? If so, this is what you need.

Private Sub Report_Load()

    If CurrentProject.AllForms("Re-Invoice Form").IsLoaded Then
        Me.RecordSource = "Select * From [Balance Due-Report-Paid-3-Reinvoice];"
    Else
        Me.RecordSource = "Select * From [Balance Due-Report-Paid-3];"
    End If
End Sub

 

[chuckcoleman]

Pat, yes your are correct. If I open the report from the Re-Invoice form I want to use the [Balance Due-Report-Paid-3-Reinvoice] query. If the Re-Invoice form isn't open and I want the report from a different form called Invoice Form, I want to use the [Balance Due-Report-Paid-3] query. I tried your code and when I attempt to do a Print Perview on the report I get, Run-Time error 2191. You can't set the Record Source property in print preview or after printing has started.

 

[Gasman]

Try the Open event.

 

[chuckcoleman]

Nope, same message on the Open event.

 

[Gasman]

The way I have done this in the past is to change the sourceobject for the subreport control.

Private Sub Report_Open(Cancel As Integer)
' Change report layout for 44 onwards.
Me.srcRest_By_Ship.SourceObject = "Rest_By_Ship"
If Nz(Me.OpenArgs, 0) > 43 Then
    Me.srcRest_By_Ship.SourceObject = "rptRest_By_Ship"
    Me.srcRest_By_Ship.Left = 0
    Me.srcRest_By_Ship.Width = (Me.Width - 720)
End If
End Sub

Each report has a different query source so I could have

or

Edit: These links infers it can be done in the Open event?

Report.RecordSource property (Access)

Office VBA reference topic

learn.microsoft.com

How to Dynamically Set the Report RecordSource in Microsoft Access - ComeauSoftware.com

The Record Source for a Microsoft Access form or report can be set dynamically in code as well as at design time. This article demonstrates how.

www.comeausoftware.com

 

[chuckcoleman]

OK, I've made some progress. I eliminated the error messages by using DIM in the Me.RecordSource. I also added a MsgBox for each condition to make sure I understand if the Re-Invoice Form is open or not. That works. However, I still don't get any data in the sub report.

Private Sub Report_Open(Cancel As Integer)
Dim rpt As Report
Dim strSQL As String
Dim ReInv As String
Dim NotReinv As String

ReInv = "Balance-Report-Paid-3-Reinvoice"
NotReinv = "Balance Due-Report-Paid-3"

If CurrentProject.AllForms("Re-Invoice Form").IsLoaded Then
 MsgBox ("I'm going to run Reinvoice Query")
 Me.RecordSource = "Select * From [Reinv];"

Else
 MsgBox ("I'm going to run the NOT Reinvoice Query")
 Me.RecordSource = "Select * From NotReinv;"
End If

'Set rpt = CreateReport
'rpt.RecordSource = strSQL
'Set rpt = Nothing

End Sub

 

[Gasman]

Have you walked through this code line by line?
We are assuming that there is data for whatever linked field you have in the main report?

Not sure how you Dim Me.recordsource, as it is a property of the report. Regardless, you should Dim ALL your variables, but Me. Recordsource is not a variable.

Why your started with rpt pbject, I have no idea?

Perhaps review a few videos?

https://www.youtube.com/results?search_query=access+sub+reports

Failing that upload a DB with enough to see the problem and instructions on how to recreate the problem.

 

[theDBguy]

OK, I've made some progress. I eliminated the error messages by using DIM in the Me.RecordSource. I also added a MsgBox for each condition to make sure I understand if the Re-Invoice Form is open or not. That works. However, I still don't get any data in the sub report.

Let's try some troubleshooting steps here. Comment out all the code and simply enter one of the SELECT statements in the Record Source property of the subreport. Now, try to preview the report with the form open and closed. Do you see any records in the subreport? If so, are they the correct records? Then, change the SELECT statement to the other one and repeat the process. Same questions. Tell us what you see...

 

[chuckcoleman]

Ok, I'm pretty sure the problem is because the report in question is a sub report to another sub report on the main report. If I comment out all code, (I actually put "Exit Sub" as the first line in the code), and I hard code the query and then open the report by itself, (without the main report), it works fine and shows the data. If I open the main report with the two sub reports, I don't get any errors but there isn't any data.

 

[theDBguy]

Ok, I'm pretty sure the problem is because the report in question is a sub report to another sub report on the main report. If I comment out all code, (I actually put "Exit Sub" as the first line in the code), and I hard code the query and then open the report by itself, (without the main report), it works fine and shows the data. If I open the main report with the two sub reports, I don't get any errors but there isn't any data.

Subreports are like subforms, they are often linked to the data in the main report. Did you properly set the Master and Child Linked Fields properties?

 

[chuckcoleman]

I do have the Master and Child Linked Fields filled out. When I run the code I see the MsgBox message twice and I then get an error: 2191, "You can't set the Record Source Property in the print preview or after printing has started". I don't know why it appears to cycle twice through the code.

Private Sub Report_Open(Cancel As Integer)
' Exit Sub
Dim rpt As Report
Dim strSQL As String
Dim ReInv As String
Dim NotReinv As String

ReInv = "Balance Due-Report-Paid-3-Reinvoice"
NotReinv = "Balance Due-Report-Paid-3"

If CurrentProject.AllForms("Re-Invoice Form").IsLoaded Then
 MsgBox ("I'm going to run Reinvoice Query")
 Me.RecordSource = ReInv
Else
 MsgBox ("I'm going to run the NOT Reinvoice Query")
 Me.RecordSource = NotReinv
End If

'Set rpt = CreateReport
'rpt.RecordSource = strSQL
'Set rpt = Nothing

End Sub

 

[Gasman]

Not sure why it does not work for you unless it is because it is a subreport?
I just created this simple report

which works in both Report and Print preview?

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = "Select Top 10 ID, TransactionDate, Account, Num, Description, Memo, Category, Clr, Amount from TestTransactions"
End Sub

 

[chuckcoleman]

I found this that might be the cause: Error 2191 - You can't set the recordsource property after printing has started | Access World Forums (access-programmers.co.uk)

However, I'm not sure how to do this: "The solution was to create a global variable as a flag to indicate the main report was opened. Then on the On_Open Event of the subform, test the variable to see if the report was already open, in which case the recordsource for the subreport was already set. By doing this, it does not try to set the recordsource for the subreport again, and has no issue executing the SendObject method."

 

[ebs17]

When working with events, keep in mind that a subreport is loaded before the main report.

A way to avoid such confusion and that definitely works:
- Use a saved query qryX as the subreport's RecordSource.
- Change the definition of the query before opening the main report:

Dim sSQL As String
If ... Then
   sSQL = "..."
Else
   sSQL = "..."
End If
CurrentDb.QueryDefs("qryX").SQL = sSQL

 

[chuckcoleman]

I've rewritten my report to have the second sub report NOT nested in the first sub report. I think I can make this work, fingers crossed.

Thank you all for your help.