Having a records calculated field get info from previous record

[theSizz]

I have a table (tblStats). The user enters his current weight in a field named Wt. There are 2 other fields in the table named WkLoss and TTDLoss. These two fields are intended to be calculated. What I am trying to do is when the user enters his current weekly weight figure into the Wt field, I want the field WkLoss to get the user's weight from the previous record and place the difference in the field WkLoss. Also I would like to keep a total to date in the TTDLoss field.

So far this has been my attempt.

Public Sub setWeight()

Dim rs As DAO.Recordset

Dim Startval As Double
Startval = 190
Set rs = CurrentDb.OpenRecordset("tblStats")

'Select records to be updated
Set rs = CurrentDb.OpenRecordset _
("SELECT * FROM tblStats ")


    rs.MoveFirst
            
        Do
            rs.Edit
'field to update
            rs.Fields("WkLoss") = Startval 
            rs.Update
            rs.MoveNext
'increment step value
            Startval = WkLoss - Wt 
        Loop Until rs.EOF
        
             rs.Close
             Set rs = Nothing
       
  
End Sub

All I get is the value 190 placed in the first record and all zeros in the subsequent records. Also I don't have a clue as to how to get the Total To Date field to calculate.

Any help would be appreciated
Thanks for your time.

 

[fneily]

Check out:
How to fill a record with data from a previous record automatically in Access 2000 and 2002 and Office Access 2003

http://support.microsoft.com/?kbid=210236

 

[theSizz]

Thanks fNeily that got my thought process going in the right direction.

Using the code example shown on the microsoft site I was able to create a report that produced the desired results.

Thanks again for the quick reply.