Parent/Child Issues

[Gremmel]

I have a parent form with a one to many relationship to a subform. The parent form has an unbound text box that is recalculated via the Control Source of the object (we'll call it TB1). The Subform has a Qty (QTY) value and I want to recalculate another subform field (SFF1) based on whether a change occurs in TB1 or QTY. If the change occurs on TB1 it should recalc all the records of the subform. If the change occurs on QTY it need only recalc the one line of the subform

I have tried .Recalc and .Requery with little to no luck. Anyone done something similar? The subform could have 1 to many children records.

I have vba code in the subform to recalculate the fields by clicking on the each line of the subform. This is not a desired workaround.

Thanks,
Gremmel

EDIT: BTW, I am working in 2013/2010

 

[Gremmel]

I can use the Control Source to calculate the fields in the Children correctly. Is there a way to set a control source to both a field in a table and a calculation?

 

[Pat Hartman]

If you are trying to save the calculated value - don't. Put the calculations in the RecordSource queries. If the subform's query references a field in its parent record, you will need to save the main form in order for the subform to see the update. The recordsets are refreshed at intervals defined in the form, if you want to force the subform to calculate faster, you need to refresh it.

If the operands both come from the same recordsource, the calculated value will change without having to actually update the record.

 

[Gremmel]

Thank you. That put me on the right track. My query is huge but I got it to work. I fear it may have performance issues when the data records get much larger. There are several DSUM in the query.

Any idea that might help with performance?

Thanks
Grem

 

[Pat Hartman]

Never use domain functions in queries. Each domain function runs a separate query so if your query returns a thousand rows, each domain function will run a thousand queries.

If you need to retrieve aggregated data, create a totals query to do the aggregation and then join to that query. This operation is significantly more efficient than using domain functions.