populate control.value - should I use SQL or VBA?

[Mr.K]

I have a continueous subform with a control in the footer to which I would like to pass a value from a query where the RecordID in the Qry = RecordID on the ParentForm. Do I have to do it using vba (if so, on what event do I update the control and call the vba code) or can I do that just using control's source property (if so, how should my SQL look like more or less with RecordID filtering for the single value)?

 

[edtab]

In the On Current event of your main form, try placing a sub

Private Sub Form_Current()
Me!SubformName!ControlName = Me.RecordID
End Sub

This code should fire when you move to a record in your main form.
Also assumes you have a control named "RecordID" in your main form.

HTH

 

[Mr.K]

I think I'm totally misunderstood. I don't have a problem with linking the subform with the mainform.
I have a control on a subform that I want to give a value to based on a complicated query that calculates a total for the records in the subform. (I cannot use the SUM function in the footer of the subform). My question is: how do I display that value in the control?...

Should I use vba (if so, on what event do I update the control and call the vba code) or can I do that just using control's source property (if so, how should my SQL look like more or less with RecordID filtering for the single value)?

Pat, edtab - sorry if my first post wasn't clear enough, thanks for your feedback.

 

[deBassMan]

Edtab's suggestion seems appropriate.

i.e
In the On Current event of your main form, try placing a sub

Private Sub Form_Current()
Me!SubformName!ControlName = Me.RecordID
End Sub

 

[Mr.K]

Why can't you use a Sum() in the footer?...

My continuous subform Payments displays a TotalDue after (for) each record (payment for a class registration). However, some of the payments are partial payments for the same RegistrationID. In such case my TotalDue displays the same total amount due for a given RegistrationID whenever the payment for that RegistrationID appears.
Now, my SUM in the footer must display AccountBalance which in this case cannot be the SUM of TotalDues because of all the partial paymants that must be grouped first

To give a simplified visual of what my subform displays:

PaymentID | amount | RegistrationID | TotalDue
001 | $100 | 23 | -$100
002 | $200 | 01 | -$75
003 | $400 | 23 | -$100

*RegistrationID links to Classes and their Fees, so in case of RegistrationID=23 fee for that class is $600. Total payments for "Class23" are $500 ($100+$400). So balance on each row displays due for that class as $100 (600-500). So now the TOTAL ACCOUNT BALANCE is not SUM of TotalDues (-$275) but: -$175.
What I'm trying to show on that subform somewhere is the TOTAL ACCOUNT BALANCE of -175 calculated based on the query I designed (which groups all the payments and calculates the sum for each forms!frmMain.recordID). My question is how to set the value of that control TOTAL ACCOUNT BALANCE to what the query calculates for me and on what event since I want it to update whenever the subform gets requeried or/and when its records get updated.

Thanks for your help guys,
M