Solved Best way to recalculate a record on a subform.

[HillTJ]

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]

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]

Cotswold, yes. Thankyou. Will consider your suggestion. Thanks.

 

[CJ_London]

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]

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]

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]

@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]

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]

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.