Forum Discussion
Refreshing calculated controls
Hi George, many thanks for that but....
The calc controls are based on the following code when the form is loaded:-
[Forms]![Accounts open maintenance]![Tot Gross] = DSum("[Gross Amount]", "Accounts open query", "[Type]='Income'") - DSum("[Gross Amount]", "Accounts open query", "[Type]='Expenditure'")
How do I reset the control to the filtered data?
Call that code by referring to the Open Form sub. You'll have to change it from a Private to a Public Sub
OR, move that code to the public sub as described above.
Hi, the initial calc works from the query.
I do not think after the user performing a filter on the initial data that the same recalc can be used as it points to the original data when opening the form and not the filtered data
Storming I'm sorry but that's a bit opaque to me. I assume that you mean you want to apply DIFFERENT criteria when requerying those controls later.
In that case, you will have to write different calculations to do that using the filtered recordset. It's hard to know specifically what that might look like because I can't see how that filtering is done. In any event, though, you'll be able to do it from a Public Sub in the subform's code module, which you call in the after update event of whatever controls are being filtered.
If you can provide a sample accdb with only the relevant forms and queries and enough sample data to see how it works, it should be possible to be more helpful.
Hi George, I am just using the built in filtering functions Access provides, i.e. by the drop down arrow to the right of the column header.