Here is what I found & what I did to make the functions work:
1: I can't set the controlSource equal to "=count(*)" because some of the fields in (*) may be null, so I use the explicit field name.
2: To get the aggregate functions to work on the footer this is what I had to do concerning the filtering.
It appears that the aggregate functions still totaled the form's recordset object regardless of the form's filtering. So no matter what the filter was the field in the form's footer using "=count([RecID])" as it's control source would not change. At first I thought I would have to write a custom function for the filter to use and set the form's recordset object to the filtered recordset (ugh!). Although, I myself would prefer to write everything myself, as a matter of productivity this doesn't fly too well. So here is the work-around I had found for this issue. Using the form's onApplyFilter event I was able to capture the form's filter and set the form's ServerFilter property. Here is the code:
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
Debug.Print "ApplyType: " & ApplyType
Debug.Print "Filter: " & Me.Filter
Debug.Print "FilterOn?: " & Me.FilterOn
If ApplyType = 1 Then 'If a filter is being applied...
Me.ServerFilter = Replace(Me.Filter, """", "'") 'Replace double-quote (") with single-quotes (')
Else 'The filter is being un-applied so remove it from the server filter.
'Me.Filter = ""
Me.ServerFilter = ""
End If
Debug.Print "ServerFilter: " & Me.ServerFilter
End Sub
Vuella, this is the easiest way I could come up with at the moment and it causes the aggregate functions to work on applying filter(s)
--Micah
Oops - brain fart on my previous reply. So - the sum was wrong because you're
filtering the recordset, and the sum doesn't reflect the filter. What if you
just use ServerFilter instead of Filter?
If that doesn't work, this may be a bigger can of worms. First, you can set
generate the form's recordset in code like I saidin the previous reply, but
that assumes you want to requery the recordset, and not just re-filter it.
You can loop through the records in the form's recordset, but with all of the
issues I mentioned before, plus, if you filter a recordset before you assign
it to a form's Recordset property, -bad- things can happen, so you almost have
to filter the recordset, loop through and compute your aggregate, remove the
filter, then set the form's Recordset, then reapply the same filter to the
form - yick.
Ok, got some things figured out on the count() aggregate function. All the
aggregate functions in the form footer will fail if I try to use count(*) as
the control source for the text field. I actually have to do a count on one
of the form's text fields in the Detail section (ie count([recID])). The
problem that I run into with the adp is that once the form has been filtered
it still doesn't change the count in the footer and recalculate everthing.
The way you have suggested to do it worked fine in an access front-end, but
it doesn't seem to work well with the adp I'm developing. If it's an issue
with the adp or my Access, I do have OfficeXP SP3 installed for any related
information.
At this point I think I am going to make a function that I will call in the
onApplyFilter event to tell the footer fields to recalculate.
I will post later with the success or failure of doing it this way.
--Micah
Mike said:
I have a continous form with summary information in the footer. When the
recordset returns I can use the count([FormFieldName]) to get the count of
records returned. But, it appears that when a filter is applied, the number
does not change because the underlying recordset has not changed and has
since closed. Is there any way that I can recalcute totals after a filter
is applied? And it is quite frustrating that sometimes the summary fields
work correctly and sometimes they do not "#Error" appears in the fields, and
they were just working.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Try using =Count(*) in the ControlSource property of the TextBox you are
using as the Count control on your form. I set up a Continuous form w/
a Count in the footer & filtered it & the count changed correctly.
Perhaps you have some other criteria?
Usually #Error means a calculation is being tried on incompatible data
types. E.g.: "abcd" + 12. Make sure your columns are (contain) the
correct data types.
