Apr 03 2020 06:18 AM
Apr 03 2020 06:18 AM
I have an unbound form which has on it 3 calculated controls and a subform based on a query on an accounts transactions table.
On opening the form the 3 controls are calculated from data from the subform query.
I want to refresh the 3 calculation controls whenever data is amended/added/deleted in the subform and I want to also refresh the 3 calculation controls if the subform is filtered.
All suggestions would be welcome as to how I can achieve this.
Apr 03 2020 06:34 AM
@Storming You can use the AfterUpdate events of the controls in the subform.
Because you want to call the same requery from multiple controls, you'd probably best make it a separate sub that can be called from those control events. Something like this, using your own control names, of course.
Public Sub RefreshMainForm()
Apr 03 2020 06:50 AM
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?
Apr 03 2020 08:14 AM
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.
Apr 03 2020 08:22 AM
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
Apr 03 2020 08:30 AM
@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.
Apr 03 2020 09:03 AM
@Storming I see. That certainly complicates matters then.
There is no connection between the recordsource for the subform and the queries used in the main form. And if the only filtering is on that subform recordsource, it's not clear how you'd actually update them. Again, someone might be able offer up a work around or solution if we could have something to work with....