Do total on a subform column

[hdesbiens]

Hi

i have a form wich have a subform in it with all the records of the table, i have put some filters fields that filter the subform, what i want to do is dependently of what is on my subform i want to do a total on the column total.

So that if all the records are shown the total will be for exemple 5000$ and if i filter my subform the total wiil be 1000$.

Is there a way to do that?

thanks a lot

 

[VicRauch]

Of course I don't know what the names of your fields and forms, so I used my own.
I created a new, plain form "Form9" to use as the sub form. It has two fields in the form, "Comment" and "Age". (I know they are not normal for this example, but this is a table I had available.) I created a new form "Form8" I placed two fields on this form, "txtFilter" and "txtTotalShowing". The "txtFilter" will accept the value to filter the subform records with, using the field "Comment". We will sum the "Age" field.

Here is the code behind "Form8".

Private Sub txtFilter_AfterUpdate()
  Me!FixedWidth.Form.Filter = "Comment='" & Me!txtFilter & "'"
  Me!FixedWidth.Form.FilterOn = True
  Me!FixedWidth.Requery
  Me!txtTotalShowing.Requery
End Sub

Function TotalShowing()
  TotalShowing = DSum("Age", "FixedWidth", Me!FixedWidth.Form.Filter)
End Function

When you change the data in the "txtFilter" field and press the Enter key or the Tab key, the "AfterUpdate" event will run. This will change the filter on the subform, requery the subform, then requery the Total "txtTotalShowing" from the subform. The function TotalShowing is used as the SourceData for the "txtTotalShowing" field on Form8. This function will use the filter from subform as the criteria for selecting records to sum for the "txtTotalShowing" field on the main form, Form8.

HTH
Vic

 

[Pampers]

Or you can use the sum-function in the footer of the subform. It sums the values that are on your screen. use an unbound textbox and put in something like:

=Sum([Amount]) or =Nz(Sum([Amount]))

Pampers
Just let it go...