Update field on subform with calculation

[NoviceBoy]

Hi All,

I have a form with a value field on it. I also have a subform on the main sheet. When I update the field on the main form, I want a field on the subform to equal 50% of the value.

I'm sure this is simple, but I've tried everything and can't for the life of me get this to work.

Can anybody help please?

Many thanks

 

[John Big Booty]

In the After Update event of the field on the main form, put the following code;

Forms!YourMainFormName!YourSubFormHolderName.Form!YourOtherFieldName = Forms!YourMainFormName!YourFieldName * 0.5

Book mark this link for easy reference for the correct syntax for referencing sub forms and their controls.

 

[NoviceBoy]

Many thanks JBB, nice Avatar!

That works a treat, but problem is it updates the wrong record in the subform. It defaults to the first record.

I have three amounts on my main form, each of them relating to a corresponding record on my subform eg. Submit Planning Amount relates to a record where the Amount of the record where the Details field is Submit Planning on my subform.

I need it to update the Submit Planning record.

Sorry to be a pain, but hope you can help.

Many thanks

Dave

 

[John Big Booty]

Perhaps the samples and discussion here will help.

 

[NoviceBoy]

JBB,

That works a treat - many thanks.

The code I ended up using was

Private Sub F194_AfterUpdate()
Dim SrchVar As String

If IsNull(Me.F194) Then
Exit Sub
End If

SrchVar = Me.AcqStage1

Forms![Fin Finance]!cfoAcqFixedCosts.SetFocus
Forms![Fin Finance]!cfoAcqFixedCosts.Form!Details.SetFocus
DoCmd.FindRecord SrchVar, acEntire, False, acSearchAll, False, acCurrent, True
Forms![Fin Finance]!cfoAcqFixedCosts.Form!Amount = Forms![Fin Finance]!F208 * 0.3
End Sub

AcqStage1 is a hidden field on the main form which matches the details field on the subform.

You're a lifesaver!

 

[John Big Booty]

Glad you got it sorted