SUM on a Form

  • Thread starter Thread starter Pap
  • Start date Start date
P

Pap

I've got a form containing locked database fields and then a field that
requires input to. I also then have a field next to that what multiplies
these others up. If I try to Sum the multiplier field in the footer of the
form I get an #error# message. I know I can some the 'fixed' database field,
but is there a way to sum the calculating field? For example:

Fixed Table Field 1 = [A]
Fixed Table Field 2 =
Variable Table Field 1 = [C]
Text Box 1 = [D] = [A]**[C]

I want to sum [D] within the form footer.

Thanks.
 
I've got a form containing locked database fields and then a field that
requires input to. I also then have a field next to that what multiplies
these others up. If I try to Sum the multiplier field in the footer of the
form I get an #error# message. I know I can some the 'fixed' database field,
but is there a way to sum the calculating field? For example:

Fixed Table Field 1 = [A]
Fixed Table Field 2 =
Variable Table Field 1 = [C]
Text Box 1 = [D] = [A]**[C]

I want to sum [D] within the form footer.

Thanks.
Click to expand...


Use

=Sum([A]**[C])

in the footer, or do the calculation of D in a calculated field in the form's
Recordsource query. You can sum *fields* but you cannot sum *controls*.
 
First, a Sum doesn't do multiplication and a Sum works on a set of records,
so all you need is a function that does the calculation and popluates the
text box. I don't know what controls the locked fields are bound to, but
here is how the function would work:

Private Function CalcTotal() As Double
Me.Text Box1 = Nz(Me.TextBoxA,0) * Nz(Me.TextBoxB,0) * Nz(Me.TextBoxC,0)
End Function

To make it populate the box, you need to call the function in the After
Update of text boxes A, B, and C. The Nz function is there to prevent Ivalid
Use of Null errors. To make the call, put the following in the After Update
event of the 3 text boxes. You can type it directly into the After Update
Event Text Box of the Property Sheet for each control:

=CalcTotal()
 
Back
Top