Add up field values in a Loop

[Elana]

I'm trying to write some code where Access looks at the value of fldPct in a form's recordset (sometimes just one record, sometimes up to 8 records will be displayed on the form) to confirm that the values add up to 100% before the user exits the form. If the values are less than 100, then it will create an error message.

I don't know how to pass the value into a variable that will add them up. Can someone give me a push in the right direction?

Thanks very much!

 

[Peter D]

Try this:

dim sngPct as single
sngPct = Dsum("fldPct","MyFormRecordSourcName","<criteria, if needed>")

Hope this gets you started

Peter De Baets
Peter's Software - MS Access Tools for Developers http://www.peterssoftware.com

 

[Elana]

Hmmm, does it make a difference that the form I'm displaying records on is a subform of another form? I'm not sure how to refer to its record source or the fields in it using the Dsum function.

I can make it total the percentages for every record in the table, but I only need to total the records displayed in the subform's recordset. Should I be cloning the recordset first?


[This message has been edited by Elana (edited 04-09-2002).]

 

[Elana]

Well, here's what I did and it seems to work:

Private Sub frmPolicyUwrsSub_Exit(Cancel As Integer)

Dim sglpct As Single
Dim rst As Recordset
Dim sglanswer As Single

Set rst = Forms!frmPolicySubSubform!frmPolicyUwrsSub.Form.RecordsetClone

rst.MoveFirst



sglpct = rst!UwrPct

Do While sglanswer < 1
sglanswer = sglanswer + sglpct

rst.MoveNext

If rst.EOF = True Then Exit Do

Loop

rst.Close


MsgBox "The value of sglanswer is " & sglanswer



End Sub