2 subforms, 1 subtotal, 1 grandtotal (1 Viewer)

willsnake · Dec 19, 2018

Good day experts,

Here I am again seeking help for the access database I am trying to create.

Attached is my main form, what I wish is that SUBTOTAL will be displayed in the 1st subform (LOT) from the total of 2nd subform (LOT DETAILS). Then from the total in 1st subform, a grand total will be displayed in the main form.

Hope this is possible, I have been searching but cannot find any solutions. I can only find a grand total from single subform...

June7 · Dec 19, 2018

A form doesn't really have the properties to do what you want (reports do). Likely will have to use DSum() domain aggregate function.

What method are you using to synchronize the two subforms?

arnelgp · Dec 19, 2018

you need to create a Query for your first subform that will Sum the the total of the Lot Detail:

Code:

select [Lot Number], [Particular], (Select Sum(T1.[Quantity]*T1.[Cost]) From [Lot Detail] As T1 Where T1.[Lot Number]=[Main Lot].[Lot Number]) As [SubTotal] From [Lot Main];

[Lot Main] is the table in subform1.

arnelgp · Dec 19, 2018

if the resulting Recordset is Not Updatable the
use DSum():

Code:

select [Lot Number], [Particular], 
DSum("[Quantity]*[Cost]", "[Lot Detail]", "[Lot Number]='" & [Lot Number] & "'") As [SubTotal] 
From [Lot Main];

willsnake · Dec 21, 2018

June7 said:
A form doesn't really have the properties to do what you want (reports do). Likely will have to use DSum() domain aggregate function.

What method are you using to synchronize the two subforms?

I just did the form wizard, and include the fields from 3 different tables, then access just combine it in 1 form...

willsnake · Dec 21, 2018

arnelgp said:
you need to create a Query for your first subform that will Sum the the total of the Lot Detail:

Code:

select [Lot Number], [Particular], (Select Sum(T1.[Quantity]*T1.[Cost]) From [Lot Detail] As T1 Where T1.[Lot Number]=[Main Lot].[Lot Number]) As [SubTotal] From [Lot Main];

[Lot Main] is the table in subform1.

Thank you for the reply. But I can't seem to find how to do this...

Sorry for my ignorance...

willsnake · Jan 10, 2019

up please... need help...

June7 · Jan 10, 2019

arnel is suggesting you set the form RecordSource to a query that uses DSum(). Did you try it?

willsnake · Jan 10, 2019

I have been searching in web on how, but I cant seem to find it...

So sorry but I am still new to Access..

willsnake · Jan 11, 2019

May I ask guidance on how to apply what is advised?

Thank you...

June7 · Jan 11, 2019

You don't know how to set a form RecordSource property?

Suggest you provide db for analysis, follow instructions at bottom of my post.

MajP · Jan 11, 2019

Here is a pretty good example for using Dsum in a query. It will show how to type it into the query builder
https://theaccessbuddy.wordpress.com/2013/12/25/dsum-function-the-art-of-writing-a-criteria/

willsnake · Mar 14, 2019

Good day,

Thank you very much to helping me masters...

This thread is now solved and closed...

2 subforms, 1 subtotal, 1 grandtotal (1 Viewer)

willsnake

Registered User.

Attachments

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

willsnake

Registered User.

willsnake

Registered User.

willsnake

Registered User.

June7

AWF VIP

willsnake

Registered User.

willsnake

Registered User.

June7

AWF VIP

MajP

You've got your good things, and you've got mine.

willsnake

Registered User.

Similar threads

Users who are viewing this thread