how to reference a calculated control in the sub-form?

[ken2sin]

Help folks,

I have problem in writing a code which will reference a calculated control in the sub-form.


Private Sub fsubKpPmtVou_Exit(Cancel As Integer)
If me.fsubKpPmtVou![Total]<> me.[Amt-Cr] Then
Sound 3
me.[Amt-Cr].SetFocus
End If
End Sub


Private Sub fsubKpPmtVou_Enter()
me.fsubKpPmtVou.Form.Requery
End Sub


Private Sub Form_Close()
Dim db As Database, rct As Recordset, vn As String
If me.fsubKpPmtVou![Total]<> me.[Amt-Cr] Then
vn = me.VouNo
Set db = CurrentDb
Set rct = db.OpenRecordset("qryKpPmtVou")
rct FindFirst "VouNo=vn"
If Not rct.NoMatch Then
rct.Delete
End If
End If
End Sub

([Total] is an unbound control capturing the sum of a bound control "[Amt-Dr]" in the sub-form.)

The "fsubKpPmtVou_Exit" works fine while the "Form_Close" event halts at the "If me.fsubKpPmtVou![Total]......." line.

Any thing has gone wrong or I really cannot reference a calculated control in the form or sub-form all together. Your expertise is appreciated.

[kedaman]

Eh, not sure what you meant with calculated control, but you can store a control in a object, control or variant variable.

By using set you assign the reference, for instance:

Dim A as variant
Set A = Controlarray(n+2)

And to use for instance setfocus property

A.Setfocus

[ken2sin]

Hi Kedaman,

Many thanks for your help.

I work with Access2000. I created a form in continous view and had it put into a subform control of a main form. On the form footer of the subform I created an unbound control "[Total]" with control source set "=Sum(Nz([Amt-Dr]))" and [Amt-Dr] being one of the field in the subform.

When I run the VBA codes, the "fsubKpPmtVou_Exit" works fine while the "Form_Close" halts and gives an error message at the line

"If Me.fsubKpPmtVou![Total]<> Me.[Amt-Cr] Then"

The problem is why it works ok on the "fsubKpPmtVou_Exit" but the "Form_Close" when using the same referencing statement.

Any comment and suggestions.

Many thanks!