Calculated Control on Subform Footer (1 Viewer)

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
I have a control on the footer that I refer to on the main form. The calculated control sums a control on the subform.

It used to work but as suddenly stopped and all I get is: #Error.

The controlSource is a simple
Code:
=Sum([GM])
Where [GM] is the control on the subform that I am trying to calculate the sum of.

Any ideas anyone?
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Make sure that you do not have any controls on the form that are named GM.
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Oh, and you might try using

=Sum(Nz([GM],0))
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
Hi Bob,

This is very strange!! The calculated control used to work but suddenly stopped whilst making some changes to the form although not related to the GM control. I have added the NZ() function to no avail.

The GM control comes from the underlying recordsource of the subform and populates with not errors.
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Hi Bob,

This is very strange!! The calculated control used to work but suddenly stopped whilst making some changes to the form although not related to the GM control. I have added the NZ() function to no avail.

The GM control comes from the underlying recordsource of the subform and populates with not errors.

Still, did you make sure that the control bound to GM is not named GM too? It should be txtGM.
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
It gets stranger. I have created a temporary form from the RecordSource behind the subform and added a control to calculate GM works on that which works fine. I have removed GM from the list of fields on the subform saved it and then added again but still getting #Error.

What is frustraiting is it used to work.

I think I possibly need to scrap the subform and re-build it! :mad:
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
Added ot this I can't get anything to work in the footer. I have tried calculating other controls but to no avail. Maybe I have a corrupt form
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Sometimes rebuilding is the way. But you might try just deleting the subform off of the main form and then adding it back on. Does the control work when just opening the subform?
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Added ot this I can't get anything to work in the footer. I have tried calculating other controls but to no avail. Maybe I have a corrupt form

Oh, and are you using the FORM FOOTER or the PAGE FOOTER (it needs to be the FORM Footer).
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
I was just about to try that. No the control doesn't work when just openig the subform.
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
Through a process of elimination I have discovered the following.

The error only occurs when I have another control that calculates the sum of a calculates control on the subform.

The subform contains 2 calculated controls:

One calculates a percentage based a running total control on the subform and a fixed figures on the main form. That works fine.

Another calculates the commission payabe to the sales manager using a function (I have fixed the problem raised in an earlier post on this function). I pass the GM and percentage figures from the current record on the subform when calling the function. This works OK.

So why when I sum the total of the commission calculated control does that cause a problem with the control that calculates the sum of the GM column? and why can't I sum the calculated field (commission) in the foot of the subform.
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Ah that would explain it. You can NOT use a calculated control with another one. You have to recreate the ENTIRE calculation in the second control - the one which is being used in the first and then also the one you need for the second. So, if you have:

=[Field1]/[Field2]

for one control you would need to use

=Sum([Field1]/[Field2])

in the second - you could NOT use

=Sum([FirstControlName])
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
Thanks Bob. I wondered if it was something like that. Unfortunately bcasue of the change in percentage of each entry I can't just do =Sum([Field1]/[Field2]).

So some brain power need to look for a different option. Will post if I come up with a suitable solution.
 

spikepl

Eledittingent Beliped
Local time
Today, 04:27
Joined
Nov 3, 2010
Messages
6,142
And the logic behind this is of course to avoid circular references and other sequence/timing-stuff :)
 

boblarson

Smeghead
Local time
Yesterday, 19:27
Joined
Jan 12, 2001
Messages
32,059
Thanks Bob. I wondered if it was something like that. Unfortunately bcasue of the change in percentage of each entry I can't just do =Sum([Field1]/[Field2]).

I would include that in the query and then you can use just =Sum([CommissionField])

You can use a function in your query if that is required to calculate the commission.
 

Fear Naught

Kevin
Local time
Today, 03:27
Joined
Mar 2, 2006
Messages
229
Great minds think alike. I have just done exactly that and it works a treat. Proves that this forum works.

Thanks a lot.
 

Users who are viewing this thread

Top Bottom