Access Project (adp) - Filter form and sub totals

[terrybens]

Hello,

I'm using a Microsft Access 2000 Project (adp file) connected to MS SQL Server 2000 database.

I have created a continous form having a detail part (results of the query) and a footer part (aggregate functions).

My problem concerns the sub-totals calculated in the footer part that are not updated when the results are filtered in the form.

For exemple, if we have the following results

ID City Country Population
1 Paris France 2.100.000
2 New-York USA 8.100.000
3 Marseille France 800.000

The sub-total calculated is 11.000.000 which is correct.

However, when I filter the results in the form to the country "France" (right filter by selection), the sub-total calculated is still 11.000.000 which is wrong. :confused:

Anyone have the same problem ?

Anyone having an idea ?

I have tested this functionnality with a Access database (.mdb file) and the sub-total is correctly updated when the results are filtered. :eek:

Why a different behavior between an "adp" and a "mdb" file ?

Anyone having an idea to get the correct sub-totals with filtered results ?

Thank you for your help.

Terrybens

[PEB]

And maybe you can Requery all your form, using commands like:
Me.Requery
Me.Refresh
Me.Repaint
docmd.showallre cords
You can see more info in Access help for those methods!

[Lana567890]

Hi,
I'm struggling with the same problem now. What kind of code did you enter at the "apply filter" event?
Thanks,
Lana

Thank you comteck for your reply.

I cannot use "sum" in the source of my form because the form displays a list of results while a "sum" function in a query will return an aggregated line.

I can however calculate the sub-total in another query using VBA code, which is your solution I guess. On the event "apply filter" of the form I can run a piece of code to calculate the sub-total.

I have tried this but the sub-totals fields in my footer are in "read only" because I have not specified a unique table in my form. However I cannot choose one (nothing in the listbox) because my record source is proc stock.

I have no idea how to solve this problem.

Thank you guys for your help.

Terry

[Didier Baille]

Hello,
I come back on this subject more than 3 years after the last post, because I want to share my solution to this problem with anybody facing it, and because I didn’t find a full working solution anywhere in the web.

I have an Access project (ADP) using SQL Server 2005.
I have some statistics forms that are doing parameterized query, build specifically responding to forms controls. So, I can’t use a SQL Server view as a source for my form.

A first idea was to use SQL Server temporary table, to respond a Microsoft support article saying that sum can’t be done on calculated fields. This had for effect to make sum TextBox in footer working (no more #Error). But, unfortunately, when applying an Access filter, sums where not actualized, even with a Requery.

The second thing was to use the Form_ApplyFilte r, to pass the local Access filter to Me.ServerFilter . And it’s works!

Here is bellow a summary of my code.

Very important: As Access can’t run more than one instance of a form, using a temporary table (in my code bellow, named #myTemporaryTab le) will not be a problem. But, if you have to use this technique in more than one form of you application, be careful to use different names in each form for your temporary table. To avoid any problem, I use the form name, followed by “Data”, and of course prefixed by # (this is mandatory).
Also, note the closing of this temporary table, in Form_Close, but also just before to create it, just to be sure to avoid any error.
In Form_ApplyFilte r, note the substitution of double cote by simple cote, and also the transformation of the access alike word in standard SQL like word.

 
‘ Opening form
Private Sub Form_Open(Cancel As Integer)
    …
    LoadStatRecordSet
End Sub
 
Private Sub Form_Close()
    globalConnexion.Execute "IF OBJECT_ID('tempdb..#myTemporaryTable’) IS NOT NULL DROP TABLE #myTemporaryTable’"
End Sub
 
‘ This Sub construct the query based on user inputs, and open a SQL temporary table
‘ This SQL temporary table is private to user connexion, so no problem for multi users.
‘ note: globalConnexion is your ADODB.Connection to access SQL Server
Private Sub LoadStatRecordSet()
    Dim strSql As String
 
    strSql = "SELECT <here, your fields>
        "INTO #myTemporaryTable " & _
        "FROM <here, your source tables with join>" & _
        "WHERE <here, your conditions, May responds to user inputs>"
    strSql = strSql & " ORDER BY <here, your fields for sorting> "
 
    globalConnexion.Execute "IF OBJECT_ID('tempdb..#myTemporaryTable’) IS NOT NULL DROP TABLE #myTemporaryTable"
    globalConnexion.Execute (strSql)
 
    Me.RecordSource = "#myTemporaryTable"   ‘ a SELECT * FROM #myTemporaryTable doesn’t works !
 
End Sub
 
 
Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    On Error GoTo Form_ApplyFilter_Err
 
    If ApplyType = 1 Then   'If a filter is being applied...
        Me.ServerFilter = Replace(Replace(Me.Filter, """", "'"), "Alike", "Like")     'Replace double-quote (") with single-quotes (')
    Else    'The filter is being un-applied so remove it from the server filter.
        Me.ServerFilter = ""
        LoadStatRecordSet
    End If
    Me.Requery
 
Form_ApplyFilter_Done:
    Exit Sub
 
Form_ApplyFilter_Err:
    <here, your code to inform user, log error…, like a msgBox or anything else)
    Resume Form_ApplyFilter_Done
 
End Sub

Hope this can help you!

[MMcCarthy]

Thanks for posting your solution. It's much appreciated.