Access form field sum

Posted on 2004-11-29
Last Modified: 2008-03-03
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!
Question by:crazyutty
    LVL 4

    Expert Comment

    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


    That should do it.

    Author Comment

    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:

    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!
    LVL 58

    Expert Comment

    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

    Author Comment

    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!  
    LVL 58

    Accepted Solution

    I have no objections about closing the question, but I would like to understand something...

    When you used:

    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!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now