Subform Calculations based on Parentform data

[Gremmel]

Greetings,

I have a form with a subform in a navigation menu. Something like:

Navigation Form
NavigationSubform
ParentForm (header data and some unbound calculation fields)
Subform (Multiple lines tied to query)

I have some fields in the ParentForm (i.e PF1, PF2) that would effect the values on the Subform (i.e. SF1, SF2). Also, there are some user editable values on the subform that will calculate the remaining fields on the subform (still using data from parentform).

My calculations work fine for the changes made on the subform. However I need to be able to calculate all the children lines on the subform when the form loads or a change is made to certain fields on the parentform. My On_Current event only wants to recalculate the first line.

Parent Form
PF1=10
PF2=3
Subform QTY ADJ SF1 SF2
Line 1 5 .05 10.6 112.89
Line 2 8 .14 10.38 105.15
...
Line N

SF1=PF1+(PF2/QTY)
SF2=SF1*(SF1+ADJ)

So if PF1 or PF2 were changed then all the lines is subform would recalc SF1 and SF2. If changes were made to QTY or ADJ, then that line would recalc SF1 and SF2.

Clear as mud? :banghead:

Thanks,
Grem

 

[rzw0wr]

First, Try the OnLoad event.
On the Afterupdate event of a changed text box add Me.Recalc and/or subformname.recalc.

Dale

 

[Gremmel]

Thank you for the reply. I had tried those.

I have this code working on Subform2 when the Qty is updated (also call this event if qty is clicked). I used the pub variables to pass from Parent to Child.

If Qty > 0 Then
  Me.txtWOQtyBreak = [pubPriceMargin] + ([pubTotSetupCost] / [Qty])
  Me.QtyFinal = ([pubPriceMargin] + ([pubTotSetupCost] / [Qty])) * (1 + QtyAdjust)
  Me.txtFinalMargin = (Me.QtyFinal - [pubTotCost] - ([pubTotSetupCost] / [Qty])) / Me.QtyFinal
  Me.txtContPerc = (Me.QtyFinal - ([pubRunContrCost] + ([pubSetupContrCost] / [Qty]))) / Me.QtyFinal
Else
  msg = "Qty value is zero; Please enter Qty"
  MsgBox msg
End If

I am struggling with the Navigation form being Parent, my form being child and my subform being grandchild.

Thanks,
Grem

 

[rzw0wr]

I stay away from Nav forms.
I have never figured them out.

Dale