Access form field sum

I have an Access form that tracks inventory.  For each item, one of the subfroms contains several numerical fields (e.g. issued_before, issued_after, etc).  There can be several records for the subforms (differentiated by item_location).  I want to display the sum of all these numerical fields for all locations for a particular item.  Example:

Item:  NIC

Location: L1
Issued_before: 3
Issued_after: 4

Location: L1
Issued_before: 2
Issued_after: 10

The total issued on the main page should be 19.  I've tried creating a query that sums each field (i.e. issued_before and issued_after) individually, but when I try to access those fields in the form, I get an error message.  Any suggestions?  Thanks!
crazyuttyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NGPSoft1Commented:
On the subform, in the Form Footer, you should have a field that totals. We'll call it txtSubformSum It can be hidden. Then, in the main form, have a field that references that field, something along the lines of

=iif(IsError(<SubFormName>!Form.txtSubformSum),0,<SubFormName>!Form.txtSubformSum)

That should do it.
0
crazyuttyAuthor Commented:
I put in the field in the subform footer (which totals properly).  But when I put the expression the control field of the textbox in the main form, I get #Name?

here is the exact expression:
=IIf(IsError([Spare_Info]!Form.Total_Spares),0,[Spare_Info]!Form.Total_Spares)

where spare_info is the name of the subform and Total_Spares is the name of the total field in the sub-form footer.  What am I doing wrong?

Thanks for the help!
0
harfangCommented:
Open the main form in design view and display the properties of the subform control. The first property is Name:, the second is Source Object:. In your expression, you need to use the Name, not the Source Object.
I'm saying this because Spare_Info might be the name of the form you display as subform, but not necessarily the name of the control that displays it...

Hope this helps
0
crazyuttyAuthor Commented:
After much trouble trying to learn access and talking to several peers, the below statement did the trick!

I created invisible fields totalling individual table columns in the sub-form with the following statement.

=DSum("Issued","Spare_Info","Asset_ID=" & [Forms]![Asset_Info_Form]![Asset_ID])

I referenced this invisible fields in the main form!  
0
harfangCommented:
I have no objections about closing the question, but I would like to understand something...

When you used:
=IIf(IsError([Spare_Info]!Form.Total_Spares),0,[Spare_Info]!Form.Total_Spares)

It didn't work, because one of the names in the expression was incorrect, hence the #Name? error message...

Now you say:
> I referenced this invisible fields in the main form!

This is exactly the same operation, no? You create a reference to a control on the subform from the main form. Now why did it work this time? If you simply replace the control name in that expression (which works) with any other control name on the same subform, it will work as well, no?

BTW: if you do use DSum(), do it on the main form directly, you do not need the hidden control on the subform to start with:
    =DSum("Issued","Spare_Info","Asset_ID=" & [Asset_ID])


Gook luck in any case!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.