Full Version: Totals from a Subform
UtterAccess Forums > Microsoft® Access > Access Forms
Trying to get the totals from a and I keep getting a #ERROR or #SIZE error if the total is null? iconfused.gif
If I use =[Transactions Balance Form].[Form]![Balance] I get the #ERROR error
If I use =Nz([Transactions Balance Form].[Form]![Balance]) I get the #SIZE error
The form is for a Accounts and the Main form has the Account information and the Subform has the details of Expenses or Income?
You could try an IIF:
However, this may only work if it's just the total that's null. If the subform itself is completely empty (no data), that may be your problem, and that may be the condition you want to check.
sanity check time. sometimes one can lose track of the actual subform object name...and that is what the error# seems to be indicate.
et into vba mode - when you type in me.subformname....that subformname should be showing in the intellisense i.e.
if you don't see the subformname then the subform object name isn't what you think (am presuming you have the controlname correct of course....cause that doesn't show in intellisense...)
this is where I would start....
First off, I want to thank you for try to help me out.
The subform is blank for that account. In the Totals text box I have =Nz(Sum([Actual Amount])) and it shows nothing in the text box that I have hidden in the form footer on it.
On the main form is where I am getting the errors. iconfused.gif
OK, that's what I thought.
When a subform bound to the mainform displays no data, it basically makes the whole subform "blank". So, trying to access a control on that blank subform results in an error.
There's a few ways to work around this, depending on your specific situation:
1) Use ISERROR in your IIF statement (similar to what I posted before, just use ISERROR instead of ISNULL)
2) Use something in your IIF statement that checks to see if the subform has any records ... IIF (yoursubformcontrolname.RecordSetClone.RecordCount > 0, yourcontrolname, Null)
3) Write a custom function in your main form that can do the above checks and return the value you'd like to see, and set your control to =thatfunctionname()
Ok, sounds good, but can you tell this old dummy where all that goes? iconfused.gif I am not very good at some of this stuff, but I enjoy fooling and trying with it. I live in a rural area here in Kentucky and there are no classes that I can attend to find out these things and I appreciate you all taking the time to try and put me on the right road.
All of the controls I have posted on here are the actual field names and control sources.
You should be able to try #1 or #2 directly in the controlsource of the control on your main form.
. =IIF(IsError([Transactions Balance Form].Form![Balance]),0,NZ([Transactions Balance Form].Form![Balance],0))
2. =IIF ([Transactions Balance Form].Form.RecordSetClone.RecordCount > 0, NZ([Transactions Balance Form].Form![Balance],0), 0)
I tried them and I did give you the wrong name, it is =IIf(IsError([Accounts Subform].[Form]![Balance]),0,Nz([Accounts Subform].[Form]![Balance],0))
When I use that one it gives me 0's even if there data there.
When use #2 I get a #NAME error. I changed the subform name in it as well.
Sorry to such a pain, maybe I will know something one of these days?????
Glad you got it working!
Have run into another problem with this.
split the database to see how it was going to and now I get a #NAME error when I first open the form after opening the program. So far if I just go in and Zoom any of the text boxes that they are in it will work till I shut down the program again and I have to do the same thing again. <
Any suggestions?
Nevermind for now, I think I have it fixed. I had left the front end in it's original location and I moved it to another location and when I opened it again and it asked to active some missing stuff. It has worked ever since I did that. Have to see if that done it or not.
Thanks anyways;
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.