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.

 

[boblarson]

Look into the DMax and DMin functions.

Use a combination of DMax and DMin on the dates to get the most recent value for the date entered and the value for the first date entered. Then, you can take those values and subtract the value gathered with DMax from the one gathered with DMin to get the loss to date.

 

[theSizz]

Thanks Bob, for pointing me in the right direction.
I'll use those functions on a form in a calculated field. This will be better than trying to store the calculations in a table.

Thanks for the help.