Increment Current Record Value on Basis of Last Records Value

  • Thread starter Thread starter lgray
  • Start date Start date
L

lgray

Can someone please provide a basic example on how to loop through a recordset
and assign a value to a datafield that is based on the value of the preceding
record.

For example, in a table that includes projects and sub-project information,
there is a field for Starting Date and a field for Ending Date for each
sub-project. I will need to calculate the Starting Date and Ending date for
each sub-project on the basis of a calculation which will identify the Ending
Date of the preceding sub-project, add 1 day to the sub-project, and assign
this value as the Beginning Date of the next sub-project. The Ending Date
for the project is a simple calculation of Beginning Date + (estimated days
to complete), so I think I can handle that part.
I will be given the Starting Date of the entire project, and the estimated
days to complete the sub-projects. Because the estimated days to complete
the sub-projects are an average, they will be the same through the data.
 
Linda,

There's three things you need to accomplish this

1) A query to sort the table into the order you want to process them
2) A procedure to loop through the recordset of the abovementioned query
3) A variable to hold the Last Ending Date (from the previous record)

The query can either be a saved query or a coded sql string. Lets assume
its a saved query, saved under the name MyQuery. Lets also assume that the
Start Date field in the MyQuery is named "StartDate" and the End Date field
is named "EndDate"

Then you need a procedure to process it. Lets pretend its on the OnClick
event of a form button named btnProcess:

Private Sub btnProcess_Click

Dim rs As DAO.Recordset
Dim dStartDate as Date
Dim iEstDays as Integer

iEstDays = 'Number of est. days (you said this was the same for all...)

Set rs = CurrentDB.OpenRecordset("MyQuery")
If rs.RecordCount <> 0 Then
rs.MoveFirst
'Initilize the first StartDate (because there's no previous record)
dStartDate = 'Add your first start date here
'Loop the rs
While rs.EOF = False
'Update each record
With rs
.Edit
rs.Fields("StartDate") = dStartDate
rs.Fields("EndDate") = DateAdd("d", iEstDays, dStartDate)
.Update
End With
'Reset dStartDate for the next record
dStartDate = DateAdd("d", iEstDaysm dStartDate)

rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing

End Sub



this should get you started

hth
-jack
 
Dymondjack,
This looks like what I need. I will be working modifying it for my process
over the next couple of days.

Thank you for your help. Like most of us novices, I don't know what I would
do without the input from you guys.

Sincerely
Linda
 
Back
Top