Subreport totals to main form (1 Viewer)

TB11

Member
Local time
Today, 18:42
Joined
Jul 7, 2020
Messages
78
I am trying to have a text box on the main form reference a totals text box from subreport for payments.

At times the payments subreport may have no data, but it still shows the subreport and displays a total of $0.00 in those instances (which is what is needed).

I am stuck and keep getting error or name errors. I've tried the me approach, tried getting the value in the expression builder by selecting the report at the top of the left pane, then going down to the payments subreport field and selecting the PaymentTotal field, the IIF suggested by Microsoft, various formulas referenced in other forums, etc., etc., etc.

Main report name = r_Main

Subreport: Source object from Data tab: Report.r_Main_D_Payments_sub Name from Other tab: r_Main_D_Payments_sub

Filed name from subreport that is needed on main report: PaymentTotal (the name of this text box on the main report is text12 - haven't bothered to rename it yet)

If anyone can help, I'd be grateful.
 

TB11

Member
Local time
Today, 18:42
Joined
Jul 7, 2020
Messages
78
My questions come down to this:

#1: How do I reference the totals text box from the payments subreport on the Main report (see above for the names of the controls, report and subreport)

#2: How do I handle the possibility that there will be no info available in the subreport's payment total?

Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:42
Joined
May 7, 2009
Messages
19,169
you need to use a Query or DSum() function to calculate the Total and put it in main form.
this will work whether you are using Navigation Form (maybe as you describe the subform)
or a Regular Tab control.

on navigation form, only one subform is always active, so if your tab "others" is in the
last tab and is not active , then you Can't reference it.
 

TB11

Member
Local time
Today, 18:42
Joined
Jul 7, 2020
Messages
78
I appreciate the help. I did a work around, based on a query with DSum and created one field subreport.
 

thebigman

Registered User.
Local time
Today, 23:42
Joined
Sep 3, 2009
Messages
45
Could I make a field for each total and maybe get some sort of query to group them and make the field the total.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:42
Joined
Feb 19, 2002
Messages
42,981
You want the total on the main form to update when something changes in the subform

Control on the footer of the subform:
=Sum([ProjectHours])

Control on the mainform:
=[sfrmTimeBilledDetail]![txtSumProjectHours]

AfterUpdate event of the subform:
Me.Parent.Repaint
 

thebigman

Registered User.
Local time
Today, 23:42
Joined
Sep 3, 2009
Messages
45
Ok thanks will look into that. Never used sub forms etc but keen to learn. Thankyou.
 

thebigman

Registered User.
Local time
Today, 23:42
Joined
Sep 3, 2009
Messages
45
Just out of interest what languages are best to learn for msaccess? Is it jet sql and vb ?
 

thebigman

Registered User.
Local time
Today, 23:42
Joined
Sep 3, 2009
Messages
45
Ok vba. Will check YouTube etc. I thought access was fading out but it seems not so will persevere. 😀
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:42
Joined
Aug 30, 2003
Messages
36,118
If you listen to the nay-sayers, it's been fading out for 15-20 years. :p
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:42
Joined
Feb 19, 2002
Messages
42,981
The rumors of Access' demise are invariable spread by the MS SQL Server team because they control Jet and they think that Jet = Access - as does most of the IT world.
 

Users who are viewing this thread

Top Bottom