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

[comteck]

How are you calculating the result?

comteck

[terrybens]

=Sum([Database_field])

Terrybens

[terrybens]

I thought about adding VBA on the filter event to recalculate the totals but this seems to be quite difficult. There should be another solution because there is no problem with an mdb file.

All my queries are in proc stocks so I cannot use an mdb file.

Terrybens.

[davenims]

Hi Terry

Perhaps you've solved this by now, but if not, the issue is with the way an ADP fetches the data from the server. The normal .Filter propery fetches the data and then applies a filter to it to choose which records to display. It seems that the totals on the form are calculated before this process on an ADP, which is what causes this problem.

The solution is actually dead simple:

Rather than using:
Form.Filter = "Your Filter"

You need to use:
Form.ServerFilter = "Your Filter"
Form.Requery (If the filter is being applied in VBA you'll need one of these)

The ServerFilter property for the form is right down at the bottom of the property list.

Using this method the filter is applied at the server end before the data is fetched, so not only will your totals now be correct, but the query will run more quickly aswell.

Hope this helps

[terrybens]

Hi,

Thanx a lot Dave for your help.

However in my case the problem remains. I use a proc stock to get my data and a server filter cannot be applied to a proc stock :confused: .

My totals then remain wrong :( .

I need a proc stock to get my data.

Any other idea ? I thought about temporary tables but this is quite to complicated.

Thank you guys for your help.

Terry

[comteck]

Terry,

Try doing your filtering in a query. Then do the calculation in the query itself as an expression.

The SQL statement would be sort of like this:

SELECT Sum([Population]) AS ExpressionName
FROM tblTableName
WHERE (((tblTableName.Country)="France"));

If you don't know SQL very well, that is ok. Just go into query design view, switch over to SQL view from the view icon in the upper left corner, and enter the code above (replace fieldname and tblTableName with your appropriate names - same for Population and Country. Replace these with whatever you named your table/fields). Also, use whatever ExpressionName that you like - It doesn't make any difference.

When you've entered the SQL, switch back over to design view, and the word "France" will be in the criteria box under Country. If you want to be prompted for a country instead, replace the word "France" with the following:

[Enter Country Name]

Try running the query by itself before assigning it to a form, to see if it works ok.

Hope this helps.
comteck

[terrybens]

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

[MMcCarthy]

The calculated fields are unbound fields on your form as in they are not bound to the table or query which is the record source of the form. As such when you update the records the unbound fields are not automatically updated.

However, assuming the field are calculated based on values in the form update, simply requery each calculation. That is:

For each calculated field in the form footer:

Me. FieldName. Requery

If this does not solve your problem please let me know.

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

[terrybens]

Hi everybody,

The subtotals text field are unbound fields. I have coded the calculation on the "apply filter" event.

I solved my last difficulty which was a message like "The form is read only because no unique table has been indicated".

I don't know why but changing these parameters of the field : enabled = no, locked = yes solved the problem.

Thank you guys for your replies.

Terrybens

[PEB]

And maybe you can Requery all your form, using commands like:
Me.Requery
Me.Refresh
Me.Repaint
docmd.showallrecords
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_ApplyFilter, 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 #myTemporaryTable) 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_ApplyFilter, 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.