Creating a coded event on a sub form

[Jamie]

Hello all,

I have a problem populating a field on a sub form. I am attempting to
create an onclick type event on the sub form that will sum a number of
fields and populate the total to a empty field (from the same table.)

On the main form is a listbox displaying reference numbers from a
table/query, the sub form displays the remaining fields based on the
select reference number. In the sub form users will add numbers to
three fields: [AV], [RW] and [FA]. Ideally as the user enters the data
I would like the [TOTAL] field to update and display the summed total
(and ideally tick a tickbox).

I am unsure what the trigger event needs to be, i.e. OnClick.
OnLoseFocus, etc. I am also unsure how to reference the fields
correctly. MS Access seems to attach the code to the Form itself, but
this doesn't work. I am guessing that the code needs to be applied to
the txtboxes ([AV],[RW],[FA]) themselves.

I have tried using code along the lines of:

Private Sub AV_AfterUpdate()
Me![Total].Value = Me![AV].Value + Me![RW].value + Me![FA].value
End Sub

Can any one help? I can provide any essential details I have
unwittingly left out.

If someone can point me in the direction of some online resources I
would really like to follow some tutorials on this type of
functionality.

Thanks in advance for any help offered.
Jamie

 

[Michel Walsh]

If all the controls are on the same form, then open that form (not its
parent, but the form itself) in design view and add the code behind it:



Private Sub AV_AfterUpdate()
Me.Total= Nz(Me.AV,0)+Nz(Me.RW,0)+Nz(Me.FA,0)
End Sub

Private Sub RW_AfterUpdate()
Me.Total= Nz(Me.AV,0)+Nz(Me.RW,0)+Nz(Me.FA,0)
End Sub

Private Sub FA_AfterUpdate()
Me.Total= Nz(Me.AV,0)+Nz(Me.RW,0)+Nz(Me.FA,0)
End Sub



should do the job.

Vanderghast, Access MVP

 

[Jamie]

Thank very much, that worked perfectly. Just to clarify, does the Nz
function provide the 0 in the absence of a value, thus allowing the
sum to work?

Jamie

 

[Michel Walsh]

Exactly.

Vanderghast, Access MVP