[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

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!
0
crazyutty
Asked:
crazyutty
  • 2
  • 2
1 Solution
 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now