Refreshing calculated controls

Question

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.

george hepworth · Answer

Storming&nbsp;You can use the AfterUpdate events of the controls in the subform.&nbsp;&nbsp;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.&nbsp;Public Sub RefreshMainForm()&nbsp;&nbsp; &nbsp; &nbsp;With Me&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Parent.FirstUnboundControlNameGoesHere.Requery&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Parent.SecondUnboundControlNameGoesHere.Requery&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Parent.ThirdUnboundControlNameGoesHere.Requery&nbsp; &nbsp; &nbsp; End With&nbsp;End Sub

storming · Answer

George Hepworth&nbsp;&nbsp;Hi George, many thanks for that but....&nbsp;The calc controls are based on the following code when the form is loaded:-&nbsp;[Forms]![Accounts open maintenance]![Tot Gross] = DSum("[Gross Amount]", "Accounts open query", "[Type]='Income'") - DSum("[Gross Amount]", "Accounts open query", "[Type]='Expenditure'")&nbsp;How do I reset the control to the filtered data?

george hepworth · Answer

Call that code by referring to the Open Form sub. You'll have to change it from a Private to a Public Sub&nbsp;&nbsp;OR, move that code to the public sub as described above.&nbsp;&nbsp;&nbsp;

storming · Answer

George Hepworth&nbsp;&nbsp;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

george hepworth · Answer

Storming&nbsp;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.&nbsp;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.&nbsp;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.

Forum Discussion

Refreshing calculated controls

Share

Resources