Get SUM of a column in a subform

X

XP

Using VBA, I would like to return in a variable the current sum of a column
in a subform (i.e. not record count, but sum).

Could someone please post generic example VBA code to do this?

Thanks much in advance.
 
P

Perry

Create a textbox on yr mainform, with controlsource
=DSum("MyAmountColumn", "tblAmount")

Assuming "MyAmountColumn" is the field you want summed on your
subform and the recordsource of your subform is "tblAmount"

Krgrds,
Perry
 
X

XP

Hi Perry,

I hope I can make this clear; if not please post back and I'll try to do
better.

In my case, MyAmountColumn is already an aggregate using

Count(Job_Code) As Total_Jobs

the above is what is in the RecordSource of the subform. Given this, what
should my formula look like?

For example: =DSUM("Total_Jobs", "tblUSEmployees")

yields an error.

Thanks for your help...
 
X

XP

Hi Perry,

I tried this, but I get #Error in the text box.

Just to be sure, the text box resides on the main form right?

Any ideas what could be wrong?
 
P

Perry

a bit more info here.

This aggregate expression
Count(Job_Code) As Total_Jobs
where is this residing? which query is this part of?
And what is the recordsource of the subform, you want to run a totalcount
from?

Krgrds,
Perry
 
X

XP

Sure,

The base aggregate (Count(Job_Code) as Total_Jobs) is part of a SQL string
in the module of the main form and is updated in the After_Update event of a
ComboBox on the main form. The After_Update event grabs certain variables and
updates the RecordSource of the subform as follows:

Me.frmMainPSFT.Form.RecordSource = sSQL

The SQL is good as the subform reflects the data needed without error every
time the combo box is changed.

Is it not possible to use something like:

=DSUM("Total_Jobs", "frmMainPSFT") ???

"Total_Jobs" of course being the sum of the column I need on the subform
"frmMainPSFT"...

I couldn't get this to work using the form above, but is it not possible
using some similar syntax?
 
P

Perry

SUBForm
Create a Footer
In the Footer create a textbox with controlsource =Sum([Total_Jobs])
Name of this textbox: Text2
Make the Footer in yr subform invisible.

Mainform
create a textbox with controlsource = frmMainPSFT.Form!Text2

Krgrds,
Perry
 
X

XP

It works! Perry, thank you so much for taking all this time to bail me out!
I really appreciate it!!!

Perry said:
SUBForm
Create a Footer
In the Footer create a textbox with controlsource =Sum([Total_Jobs])
Name of this textbox: Text2
Make the Footer in yr subform invisible.

Mainform
create a textbox with controlsource = frmMainPSFT.Form!Text2

Krgrds,
Perry

XP said:
Sure,

The base aggregate (Count(Job_Code) as Total_Jobs) is part of a SQL string
in the module of the main form and is updated in the After_Update event of
a
ComboBox on the main form. The After_Update event grabs certain variables
and
updates the RecordSource of the subform as follows:

Me.frmMainPSFT.Form.RecordSource = sSQL

The SQL is good as the subform reflects the data needed without error
every
time the combo box is changed.

Is it not possible to use something like:

=DSUM("Total_Jobs", "frmMainPSFT") ???

"Total_Jobs" of course being the sum of the column I need on the subform
"frmMainPSFT"...

I couldn't get this to work using the form above, but is it not possible
using some similar syntax?
Click to expand...
Click to expand...
 
Top