2 subforms, 1 subtotal, 1 grandtotal

[willsnake]

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]

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]

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

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]

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

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

 

[willsnake]

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]

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

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]

up please... need help...

 

[June7]

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

 

[willsnake]

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]

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

Thank you...

 

[June7]

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]

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]

Good day,

Thank you very much to helping me masters...

This thread is now solved and closed...