Solved

Calculating Column in Subform and Displaying Results in Form

Posted on 2008-09-30
16
510 Views
Last Modified: 2013-11-29
Hello,

I'm creating a simple db that is like a ledger. There are multiple Accounts and then multiple transactions in each Account. I'm using the MS Ledger template as a starting point. I've searched/tried for days and nothing that I've read seems to work for me.

When you open the Accounts form it allows you to select an Account Name and then based on that selection the subform below populates with the individual transactions tied to that name. This works.

On the subform, named Accounts Subform, I created a text-box in the footer to calculate the total Withdrawal Amount. This does not work correctly. On the top of the main form I have a text box that should show the total of the Withdrawal column. This works but is getting a bad sum from the subform.

No matter what I do I can not get the subform to calculate completely. If I use this: "=[WithdrawalAmount]" it will show a number that is the same as the very first record for that account. But of course, it does not add up the entire column.

I've tried everything found on the net, =Sum, =Nz(sum, =dsum, etc. It just will not total the entire column.

Is there another way to do this?

Thank you for the help.
0
Comment
Question by:anon12000
  • 7
  • 6
  • 3
16 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22612146
You cannot sum subform data on a main form.
You need to create the sum in a textbox in the footer of the subform and then you can set the main form textbox to be equal to the textbox in the subform.

For an example look at the Orders form in Northwind.

0
 

Author Comment

by:anon12000
ID: 22613206
I'm sorry, I must not have been very clear in the original post.

That IS what I tried (from above):
"On the subform, named Accounts Subform, I created a text-box in the footer to calculate the total Withdrawal Amount. This does not work correctly. On the top of the main form I have a text box that should show the total of the Withdrawal column. This works but is getting a bad sum from the subform."

Is there another way to go about this? What I mean is, can it be done a different way, like with a querie or some code, anything?

I could not get it to work doing the sum on the subform footer.

Thanks.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 22613503
So the problem really lies with the Sum in footer of the subform?

If the subform is bound and the field you are summing is a from the recordsource, then the sum in the footer will only be correct after you have saved each new record or change.


0
 

Author Comment

by:anon12000
ID: 22625794
Thanks for the help. Beginner here. I'm not sure that I know what you mean by everything in your post:

Yes, the problem is in the subform, unbound text box that tries to sum the same field in every record for that account.

"If the subform is bound" The subform is bound to a table. When you select a name in the main form, the individual ledger entries for that account pull up in the subform below.

"the field you are summing is a from the recordsource" Yes, the field is a total $ field for that record.

"then the sum in the footer will only be correct after you have saved each new record or change" This is not saving/entering new records on this subform. It only pulls up the records already in the db for that account selected in the main form.

I started with the Ledger Template that Access has to build the db. What it has labled the Accounts Subform form is where I'm trying to sum the Total Withdrawals column and pass that to the top of the Accounts form in a Total field.

Thank you.,
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22643502
anon12000,

  "I could not get it to work doing the sum on the subform footer"

This is what I have been using since Access 97 till Access 2007.
It woks fine then, and still works to this day.

:-)

JeffCoachman
Access-EEQ23776955TotalFromSubFo.mdb
0
 

Author Comment

by:anon12000
ID: 22646618
Hello,

I tried again, went through with a fine toothed comb double checking names, etc. Still will not work using your example.

I think that it must be something in the way that the Ledger template builds the db.

- I just created a brand new empty db using the Ledger template.
- Go to Enter/View Other Info>Enter/View Accounts
- Make up an account name.
- Return to the Main Switchboard and select Enter/View Transaction Entry
- Enter a transaction and assign it to the account that you created.
- Notice on the Footer of this form that the 3 fields for Totals and Balance work correctly.
- Close the Transaction Entry form and go back into the Other Info>Enter/View Accounts form
- Look at your account, it should show the transaction below. Right above the transaction subform it has a field for Account Balance. ** It does not work, shows #Name?**

Is that right or is it just mine that's doing that?
0
 
LVL 77

Accepted Solution

by:
peter57r earned 125 total points
ID: 22647663
The problem you are having has been recognised as a bug in A2007 although apparently not in previous versions.
I am wondering whether this is yet another problem introduced by Office2003SP3.

Whatever, the fix for A2003 is the same as the fix for A2007; see...
http://www.allenbrowne.com/RecordCountError.html

For your control source which use a subform you will need:

=IIf(formhasdata([Accounts Subform].Form),[Accounts Subform].Form!Balance,0)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22647926
peter57r,

Interesting point as it worked fine for me when I created the file in both Access 2003 and 2007.
For the record, I did not install SP3 for Office 2003 ;-) , ...but I do have SP1 installed for Office 2007.

Jeff
0
Highfive Gives IT Their Time Back

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!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22647932
anon12000,

Can you confirm that my sample, as submitted, did function as expected.
Yes or No.

JeffCoachman
0
 

Author Comment

by:anon12000
ID: 22657981
I haven't been able to try out the latest from peter57r, will get to it later this afternoon. Thank you.

Jeff, Yes, your sample db did work as expected. That is what led me to believe that it must be a problem from somewhere else in the form(s). Are you saying that you tried the Ledger template and it all worked fine out of the box?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22658189
anon12000,

 "Are you saying that you tried the Ledger template and it all worked fine out of the box?"

Yes.
Here is what I used:
(I created the "Total Deposits" textbox by simply pointing it to the total from the subform.)

What you need to do is to *carefully* analyze your Objects and their names.
Also make sure you are referencing the correct object (for example, the subform "Object" is not the same thing as the subform itself)

Look over my sample again, it is very simple.
Compare it to yours, object by object.

I have a feeling you may just be missing something simple.
;-)

JeffCoachman
Access-Template-Accounts-ledger-.mdb
0
 

Author Comment

by:anon12000
ID: 22661640
Ok thanks, I'll check the names again.

We are not talking the same thing here though.

To be "apples to apples" with my crappy broken db, you would need to put your Total Deposits textbox on the ***Accounts*** form, not on the Transactions form.

Can you:
1. Put a Total Deposits or Total Withdrawal Amount on the top of the ****Accounts form****
2. Have that textbox add up the deposits/withdrawals for that account only that are listed below in the **Accounts Subform**

I'm not trying to be difficult. I really appreciate the help but adding the **Accounts** subform is where my problem is happening.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22663609
anon12000,

Well, then obviously you will have to post a sample of this database.

Jeff
0
 

Author Comment

by:anon12000
ID: 22674624
OK, I've attached the db as created by the Ledger template.

Note 2 things:
1. If you go to "Enter/View Transaction Entry" you'll see one transaction already in there. At the bottom of that form, Transactions form, it sums just fine like it should.

2. Instead go to "Enter/View Other Information" then "Enter/View Accounts". I would like for the transactions for that account to sum withdrawal and deposit at the top of the form. Also that "Account Balance" field is broken.
ledger3.mdb
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 22675120
anon12000,

1. I inserted a textbox in the Main form and referenced the subform control using the same syntax as my sample, and it worked fine.
Besides an *exact* sample of what you are trying to do is illustrated on the Transactions form.
Check it out.

Can you post your attempt, displaying your textbox in the mainform and what you used as the controlsource?

2. "Also that "Account Balance" field is broken."
It is not "Broken", with one transaction, it will never work.
The textbox correctly displays $80.00 when these are your entries:
WithdrawalAmount      DepositAmount
$10.00                      $100.00
$60.00                      $50.00

JeffCoachman
0
 

Author Closing Comment

by:anon12000
ID: 31501848
I'm very sorry about the delay. Emergency project sprung up and displaced this one. Thank you both for the help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

15 Experts available now in Live!

Get 1:1 Help Now