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.ServerFilt er = "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)="Fran ce"));

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