Main Report text box referring to a sub form text box changes the value of the sub form text box (1 Viewer)

chuckcoleman

Registered User.
Local time
Today, 12:07
Joined
Aug 20, 2010
Messages
291
OK, this is driving me nuts. I have a report. The main report has two sub reports. If I run the report with the two sub reports, everything displays correctly. See the screen shot below, highlighted in YELLOW. This is on the second page of a two page report. As a simple first step, if I put a text box on the main report that refers to a text box on one of the two sub reports, it changes the value of the text box on the sub report. The YELLOW highlighted number of $6,275 is now $695 in the sub
report and the new text box that I added in the main report is now $220.00 which happens to be the first line on the page, (this is page 2 of 2), circled in RED. My expectation is that the new text box on the main report would show $6,275. In the sub report, $6,275 is on the report footer and it "points" to a hidden text box in the detail section that has a running sum over all. In the main report the text box Control Source is: =[InvoiceSub Report].[Report]![GrandTotalX]. That text box is in the footer section of a field, JobID. There is only one JobID per report. I've also tried it in the Detail section with the same result.

What am I doing wrong? Once I get this fixed, in the second sub report where you see "Amount", I want it to pick up the $6,275. First things first, main form to sub form. Thanks, Chuck

Invoice Report-1.PNG

Invoice Report-2.PNG
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 18:07
Joined
Jul 9, 2003
Messages
13,549
My guess is it's because you're main report control can't access to subreport control because the subreport is not available immediately
 

chuckcoleman

Registered User.
Local time
Today, 12:07
Joined
Aug 20, 2010
Messages
291
Uncle Gizmo, how do you fix that?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 18:07
Joined
Jul 9, 2003
Messages
13,549
I made a sample report and had a little play! I came up with this, but I don't think I'm quite grasping what you're doing. Maybe you could post a simplified example?

Me.txtShowSubRptVal is the text box on the main report

Me.subFrmWinBOT.Report.txtSum1 is the text box in one subreport (I've set up two)

Code:
Private Sub Report_Load()
 
    Me.txtShowSubRptVal = Me.subFrmWinBOT.Report.txtSum1
 
End Sub

Updated. Edited my Mistake!
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:07
Joined
Feb 19, 2002
Messages
33,679
When you reference a control in a subreport/subform's detail section, you will always refer to the CURRENT record. In most cases, this will be the first row of data but in the case of a subform, you might have moved focus to some other row and so references will point there.

If you want to reference controls in a subreport/subform, the best options are the subForm/subReport Header/Footer since those only ever occur once so there is no ambiguity.

Reports might not actually work the way you expect them to since they are rendered sequentially unlike a form where you immediately see both the header and footer together. The report's footer isn't rendered until the process gets to it sequentially after formatting all the pages that come before it. Access is smarter than we are so things like page 1 of n FORCE Access to render the report TWICE. The first time to count how many pages it formatted and the second time to show you the correct value of n on each page. A reference to the SubReport's Footer might force the same two-pass process.
 

chuckcoleman

Registered User.
Local time
Today, 12:07
Joined
Aug 20, 2010
Messages
291
When you reference a control in a subreport/subform's detail section, you will always refer to the CURRENT record. In most cases, this will be the first row of data but in the case of a subform, you might have moved focus to some other row and so references will point there.

If you want to reference controls in a subreport/subform, the best options are the subForm/subReport Header/Footer since those only ever occur once so there is no ambiguity.

Reports might not actually work the way you expect them to since they are rendered sequentially unlike a form where you immediately see both the header and footer together. The report's footer isn't rendered until the process gets to it sequentially after formatting all the pages that come before it. Access is smarter than we are so things like page 1 of n FORCE Access to render the report TWICE. The first time to count how many pages it formatted and the second time to show you the correct value of n on each page. A reference to the SubReport's Footer might force the same two-pass process.
Pat, thank you for taking the time to provide feedback. I got around the problem by modifying the query that is used for the report and using the field in the query, and not trying to use a control out of a subreport. I verified that my subreport control was in the footer, yet, the value displayed of the subreport control in a main report control wasn't the same. It is just strange. Again, thank you for your feedback.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:07
Joined
Feb 19, 2002
Messages
33,679
Try adding page 1 of n to the footer of the main form. That might make Access use two passes and get what you are looking for.
 

Users who are viewing this thread

Top Bottom