Solved Best way to recalculate a record on a subform. (1 Viewer)

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 07:23
Joined
Apr 1, 2019
Messages
712
Friends, I have a form with a field that is populated with a number that the user can change. That number is used to do some calculations on a field of an embedded datasheet style subform. Pretty usual. The calc on the subform is triggered by the user selecting a record from a combobox. All works perfectly. Now, I'd like to add the functionality that if the user changes the value of the field on the mainform, then each subform record is recalculated to reflect the result of that value. I'm thinking that I have to iterate through the recordset of the subform then go to the recalc subroutine for each record. If I've made sense?

Is this the correct approach? Appreciate your advice as always.
 

Cotswold

Active member
Local time
Today, 18:23
Joined
Dec 31, 2020
Messages
521
Why not just run an Update Query, which will complete pretty much instantly with even with thousands of records?

I would make sure that the user cannot enter a null, zero or an invalid number for your system when they amend the record on the mainform. Trigger the query in the AfterUpdate()
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 07:23
Joined
Apr 1, 2019
Messages
712
Cotswold, yes. Thankyou. Will consider your suggestion. Thanks.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:23
Joined
Feb 19, 2013
Messages
16,553
Not at my computer right now but you could try in the combo afterupdate event if the calculation is not stored

me.subformname.requery

or

me.subformname.form.requery

not sure which😁
 

Minty

AWF VIP
Local time
Today, 18:23
Joined
Jul 26, 2013
Messages
10,355
Is the Subform value calculated or stored? (It sounds like it should always be calculated?)
If it is calculated, then include the form control in the underlying query calculation to make the value reflect the "live" form value
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 07:23
Joined
Apr 1, 2019
Messages
712
Gents, the subform value is a calculation, but the result is stored. At the moment, i have a combo box on the subform, which allows the user to select a product. I have an unbound control that is then populated by a dlookup from the combo box that populates a product group. I then have a bound field into which the user enters the "quantity". After entry i use some code to populate another bound control with the cost price (this uses the field value from the main form as part of the calculation). There are several more 'on form' calculations too.

I'm now thinking that his is approach is flawed & i should be using a select? query to do all the calcs instead. Then use the query as the record source for the form. If the user then changes, say the 'quantity' then would i just refresh the query? & if i add new records, i refresh the query too?

Upon reflection, there is no need to store the calculated result

Please let me know whether i'm on the right track, it's a bit of a backflip!
Cheers
 

Minty

AWF VIP
Local time
Today, 18:23
Joined
Jul 26, 2013
Messages
10,355
@HillTJ That is pretty much what I was suggesting.
If you always calculate it and include the form qty control reference in the query it should be accurate at all times.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 07:23
Joined
Apr 1, 2019
Messages
712
Minty, tanks for the direction. You know, at work i'm the smartest person when it comes to access! Thanks to all for your directions.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 07:23
Joined
Apr 1, 2019
Messages
712
People all good, a bit of a rewrite, but much smarter. Still to incorporate the 'refresh' of the subform on change, but i don't see any problems. Just a quick question. What's the best technique for summing calculated fields?. Cheers to all.
 

Users who are viewing this thread

Top Bottom