Filter a Continuous Form, Count & Total's do not update

[gazzippy]

Using Access 2000 with SQL Server 2000

I have an adp that has a continuous form that shows all of the records, a user can then right click on any of the fields and filter them accordingly to get the results they require, some of the fields have totals on them and the record count is shown at the bottom of the form in a text box, however after applying a filter the text boxes containing the Sum and Count functions do not take into account the filter being applyed, these functions work fine in an MDB, just not an ADP, I have tried using Stored Procedures and SQL strings as the recordsource for my forms.

Anyone else cam accross this?

 

[Aliffi]

dear!

i have the same problem , everything is set with my ADP but same counting problem on filterd records

i also tried alot , but i think it is a fault allover ADP
and no one is present to come with a sulotion to this.

wish some one who knows could see and help us

or if you have found plz send it to me also

regards
aliffi

 

[gazzippy]

You need to use the ServerFilter, even though when you apply a filter it sets the .Filter property of the form

Found this to do it, may need tweaking slightly for your use:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
On Error GoTo Error_Handler

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

Exit_Error_Handler:
Exit Sub

Error_Handler:
StdErrMsg
Resume Exit_Error_Handler

End Sub

I also found that I had to build the SQL string for the recordsource and set it in the form load event rather than using a stored proc, you may not need to do this, just that it worked for me.

Nice to see that when a filter is applied we get the correct records, just the wrong totals, good that

 

[cmmrfrds]

After applying the filter, did you try a refresh or recalc.

Me.ReCalc
or
Me.Refresh

 

[danvlas]

I had to use dynamic SQL to solve this issue.
Created one combo box for each field that counted for the filter, then built the 'Where' clause and passed it to the stored proc that returned the source for the form.
Whenever such a box is changed, the condition is re-built, sent to the stored procedure and the recordset is set to the result of execution.

So far it works fine.

Tried Refresh, Requery, Recalc, Repaint with no luck.

Also tried the above mentioned solution, but it didn't work for me.
I guess it works for gazzippy because of "build the SQL string for the recordsource and set it in the form load event rather than using a stored proc", which forces a requery of the form...

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html