Sum a field in subform Results

Hi There,

I have a field in my subform that I'm calculating using a value from the main form [RE_Forecast_PPod]:


This is working correctly.

What I then tried to do was create a Sum of this field in the footer of the subform to pass back to the main form :


I just keep getting an error on this sum. If I take out the part where I'm dividing by the field Forms!RE_Forecast_PPod!Bag_size_kg I can get it to work. Is this a syntax problem? My subform is [RE_Forecast_Inj subform]

Any help greatly appretiated.


I am having a problem that had been run into before. I have a couple of calculated fields in a subform. One sums a field in the subform and one counts the records. I have a function that I wrote that takes all the data from the main and subform and does a calculation. If I do a refresh and then do the function the calculation is done before the subform calculates the sum and count.

How do I make function wait for the subform calculated field to populate before it runs. I suppose I could do dcount and dsum in the function instead of using the calculated field on the subform - wouldn't that be slow? Is there a better way?


Hello All

I have a form/subform setup.
In the subform, I have a field that records work done for the work order. There can of course be several items in the field associated with the work order.
I am trying to calculate the sum of the "Cost" field for that particular record (some can have 80 items listed in the subform which are all associated to the one work order). I can't seem to get anything except "error" in my unbound text box.
I set the control sorce to "=Sum( [qry_WO_Details Query subform1].Form![WO_Details_Cost] )" (no quotes). I'm trying to put it in the form footer. There are other fields I need to sum up as well.

Any suggestions?

Thank you


This should be really straightforward but for some reason I am experiencing a total nightmare with this!

I have a form containing a subform (JobDetailsSub) set to Continuous forms. The subform is controlled by a query. The query contains some calculated fields. One field is HoursToday (a sum of 4 other fields). I want to total the HoursToday field in the footer of the main form. I have tried all options I can think of but still getting #error. I thought it should be as simple as =Sum( [JobDetailsSub].Form![HoursToday] ) but this is not working.

I know you cannot sum a calculated field in a form but I thought as it was calculated in the query it would still work. Is this not the case?

Please help!

Many Thanks

I have a query that displays reservations for equipment. There is a field that shows exactly how many weekdays the equipment was reserved for and what category of company has reserved it.

I am wondering how I can display on a form, the collective sum total of each reservation by category, without using a different query for each category of company.

In the end I just want a subform based on my query, with 3 or 4 textboxes that have a number showing the total time equipment was reserved by each company category.

Please let me know if you need more information


Please Help! This is my first attempt at access. I've gotten this far from what I've been ble to learn from this forum. I'm attempting to create a Aircraft Weight and Balance Databse. I'm stuck trying to sum Calculated fields ZeroFuelWeight and FuelWeight in my Subform. Any help or comments on my Database would be greatly appreciated

Hi, if you are reading this is because you are trying to help me, so, first of all very thank you! Lets go!

I have a subform whith rows (2, 3 or 10) where I put the products that I sell within a form whith the client address.

In the subform I have a field supose to sum the product total (quantity * price), but I cant fix the formula. I used to sum the columm "=sum([quantity * price])" and got a #Error.

What happened?

Are there a special syntax to use the "=sum()"?? Or I cant use to sum the columm?


This should be really straightforward but for some reason I am experiencing a total nightmare with this!

I have a form containing a subform (JobDetailsSub) set to Continuous forms. The subform is controlled by a query. The query contains some calculated fields. One field is HoursToday (a sum of 4 other fields). I want to total the HoursToday field in the footer of the main form. I have tried all options I can think of but still getting #error. I thought it should be as simple as =Sum( [JobDetailsSub].Form![HoursToday] ) but this is not working.

I know you cannot sum a calculated field in a form but I thought as it was calculated in the query it would still work. Is this not the case?

Please help!

Many Thanks

Hi All,

On a main form I've put 8 subforms. The subforms will show data dependent on a selector on the main form. I would like to sum a specific variable from the subforms and display it on the main form. I put a textbox field on the main form and sum all the 8 subform.variables. So far so good.

Problem is continues errors because the subform is not always visible/available because dependent on the selector. I've tried all kind of iiF code like Nz, numeric, >0 etc etc and still it is not working.

See enclosed .accdb. It is on the main form called Menu. The variable to sum is TotKcal from the subforms.

Please help.

Hello ,
I have a form with a textbox where user enters date , one button that requeries a subform and one SumTextbox that shows the sum of one field of subform.
When the user enters a date , that is not on a subform , subform returns blank , and on the SumTextbox I get #Error.

How can I input criteria ,so that when the field is blank ,my ResultTextbox shows 0?

By now I have tried : Code: = IIf ( IsNull([FieldName]) ; 0; sum( [FieldName] )) and this :
but this doesn't work

I have a continuous subform with a list of items that are be selected with check boxes. A header control called “AddersTotal” shows the sum of the value of the items selected. On the subform After Update event, I would like the "AddersTotal" to populate a field in the parent form called “Accessories “ so that I can store the value and use it other places.

I have tried the code me.parent.Accessories=me.AddersTotal for the After Update event of the subform. The field Parent.Accessories will show the old total, not the new total. If I precede the code with Form.recalc, the Parent field shows 0. At times, I get an error message “data has been changed” .

I must be missing something fundamental about what “After Update” means. I have searched on the web but found only one lengthy discussion that ended in failure plus some things that make me think that the above should work. Any help much appreciated.
I am using W7 with A2007


Im trying to calculate a sum from 4 subreports in a headreport.

Im following the next steps:

Bring the total from a subreport back onto the main report

Your subreport has a total at the end - a text box in the Report Footer section, with a Control Source like this:
Now, how do you pass that total back to the the main report?
Stage 1

If the subreport is called Sub1, and the text box is txtTotal, put the text box on your main report, and start with this Control Source:
Stage 2

Check that it works. It should do if there are records in the subreport. If not, you get #Error. To avoid that, test the HasData property, like this:
=IIf([Sub1].[Report].[HasData], [Sub1].[Report].[txtTotal], 0)
Stage 3

The subreport total could be Null, so you might like to use Nz() to convert that case to zero also:
=IIf([Sub1].[Report].[HasData], Nz([Sub1].[Report].[txtTotal], 0), 0)

If you are stuck at some point, these further suggestions might help.
Total does not work in the subreport

If the basic =Sum([Amount]) does not work in the subreport:Make sure the total text box is in the Report Footer section, not the Page Footer section. Make sure the Name of this text box is not the same as the name of a field (e.g. it cannot be called Amount.) The field you are trying to sum must be a field in the report's source table/query. If Amount is a calculated text box such as:
then repeat the whole expression in the total box, e.g.:
=Sum([Quantity]*[PriceEach]) Stage 1 does not work

If the basic expression at Stage 1 above does not work:Open the main report in design view.
Right-click the edge of the subform control, and choose Properties.
Check the Name of the subreport control (on the Other tab of the Properties box.)
The Name of the subreport control can be different than the name of the report it contains (its Source Object.) Uncheck the Name AutoCorrect boxes under:
Tools | Options | General Stage 2 does not work

If Stage 2 does not work but Stage 1 does, you must provide 3 parts for IIf():an expression that can be True or False (the HasData property in our case), an expression to use when the first part is True (the value from the subreport, just like Stage 1), an expression to use when the first part is False (a zero.) But at stage 2, im getting an error.

What im doing is first part1:

=[Active Directory Users met kosten Query subreport].[Report].[HasData]

After putting the iff experssion in and putting part 2 behind it, im having :

=IIf([Active Directory Users met kosten Query subreport].[Report].[HasData], [Active Directory Users met kosten Query subreport].[Report].[Text16], 0)

At this point im getting an error about a syntax?!

Can any1 help me with this pls?

I want to add a sum of all fields in a subreport called 'Index' the field is called 'Salary Increase'. I want the sum of these fields to be displayed in the unbound textbox called 'Salary'. Please help.

This is my problem:
Table Invoice:
Total_Invioce (Double)
Table Payments:
So, one payment can distributed in more than one invoice, and payments might be partial.

I created a table InvoiceVsPayment:
PaymentPart: stores the portion of the payment that belongs to the invoice.

I have a sub-form(datasheet view) used to input the parts of the payments by Invoice. The source of the subform is a query that shows the InvoiceId, Total_invoice, and Payment part. The query works fine letting me update the paymentpart, however, I would like to show a running total of the payments already done to that specific invoice, so that I can calculate the remaining balance.
I tried the following approaches, with no success:
1. I tried with a query that will total payments by invoice (qry_total_payment), but if I include the query in the subform query, then I can't edit the fields in the form.
2. I created a field in the Invoice table, where I would store the sum of the payments from the other table. But I don't know how to update the field using qry_total_payment query. The update query in access does not let me use a "totals" query. Any ideas?

P.S. I know option 2 is not a good practice, but don't see another way around?


Private Sub Command43_Click()
On Error GoTo Err_Command43_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddCnt"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Sub

MsgBox Err.Description
Resume Exit_Command43_Click

End Sub

This is access built in open form command button. This works fine for opening the default form, but what i need to do is to open the form, but load the record into the form from which the command button was clicked.

Basicaly, at present state i have a parent form, with details on companies. Then i have incorporated a subform that lists all staff relating to the company. i have made the sub form to display as continual forms. For each record (staff member) their is a command button which open a form for viewing staff details. Of course, this only opens the default so i have to search for the staff which i already have the details for in the previous form. What a waste of time searching yet again for the staff member. So i need to sum how grab the primary key field from the sub form , so when the Onclick command button use the key, to pull up the record in the staff form.

The best two ways i can think of are (please bare with me i am new to access and VBA) is to A) create a temp table with the key, and run a query to give the staff form the required fields or B) sum how place the table primary key field into a global varible, and as a default in the staff form, to search for a "globle variable" to give the desired result, then have a query (or what ever method used to fill a forms fields).

the latter i think is the desired approach, however i am not the expert. This is why i need your help, any VBA you can pass my way to perform this task would also be appreciated


I have a payments table witha field(lots) that I want to sum. The payments table is related to the developments table so I can have quite a few payment records tied to one development.
I have a form based off of a developments query with a subform based of a payments query. When the subform is displaying more than one record, I want to be able to sum all the 'lot' fields and display the value in a textbox on the regular form.

How would I go about summing a repeated field with itself?

Hi everyone!

This currently affects all my objects, from table up to forms. My problem is that I cannot make the Totals row on the datasheet view to work, nor can I make the form footer sum to work.

Usually, I just sum a field at the footer of the subform with a textbox with a value of =Sum([Field]). Then I call that particular textbox on my mainform with =SubFormName.Form!ControlName. I have several of this controls already working. Each one assigned to a different subform, all of them working.

But when I did the same thing to another subform, I get a #Error in my totals textbox on the footer of my subform, it won't work anymore. So I checked and re-checked my syntax, they are all correct.

To check, I pressed the "Totals Row" feature of access 2010 in datasheet view, but could not make the sum value appear... however, the average, min, max, variance, deviation, etc. are all WORKING except for the "SUM."

I think something broke here... any ideas?


I searched in Report forum and couldnt find help, and figured this is a general topic.

I have a report with a subform in it. I want to run a sum of a field in the subform. I made a text box inside the subform footer. And put this expression for the control:

=Sum([Total Cost])

The field box is named Total Cost. When I go to preview the report the Sum text box is empty.

Why is this?

I'm no expert but have created a database and am stuck on something I'm sure is possible.

I have a 'final cost' field in my 'job' mainform and a subform with a field listing several 'extra costs' for each job. I want a field in my mainform that automatically populates with the total of all these costs.

The closest I got was a query including both of the fields and with simple a+b expression field to add them. The problem here was that the 'final cost' was repeated for each 'extra cost' and the results were like 'extra1+final' 'extra2+final' etc.

Not sure if I have explained this well. I have attached a screenshot of the form with the fields I'm talking about in bold red.

Any ideas? Am I being dense?

Cheers. Siobhan.

Okay, I know this is probably simple, but I am getting an error in putting a total on a form. I have the following:

Subform name is EntryDetail Subform
I have a field in the footer called TotalEntry that calculates the total entry. The formula is =sum([EntryAmt])

Form name is Entry
I have a textbox with the control =[Entry Subform].Form!TotalEntry

The text box shows #Name?

I am attaching a copy of the database.

Any ideas?

Not finding an answer? Try a Google search.