Creating a coded event on a sub form

  • Thread starter Thread starter Jamie
  • Start date Start date
J

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
 
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
 
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
 
Back
Top