Perform operation on last record in loop

[BusMgr]

I have the following loop. After I increment the counter, it sees EOF and doesn't calculate the time difference on the last record. I've tried several elseif statements, but to no avail.

Thanks for any help in advance.
BusMgr

Public Function CorrTimeDiff()
'
'This function is used when a record is deleted from the
'Operation form so that new Hours will be calculated
'automatically.
'

Dim db As Database
Dim rs As DAO.Recordset
Dim qdfMyQuery As QueryDef
Dim CurrTime As Date
Dim PrevTime As Date
Dim ctlOpsID As Long
Dim temtim As Single
Dim intctr As Integer
Dim strSQL As String

On Error GoTo Err_CorrTimeDiff

'Set DB = DBEngine.Workspaces(0).Databases(0)
Set db = CurrentDb()

Set qdfMyQuery = db.QueryDefs("qryReSortToTime&quot

' Create the recordset (or dynaset)
Set rs = qdfMyQuery.OpenRecordset()

' Test to see if there are any records

With rs
If Not rs.BOF And Not rs.EOF Then

intctr = 0 'Set increment counter to zero - correlates to first record

Do Until rs.EOF 'Initate loop

DoCmd.SetWarnings False 'Turn off warnings

.AbsolutePosition = intctr
PrevTime = rs(4) 'Set previous time to previous record time
.AbsolutePosition = intctr + 1
CurrTime = rs(4) 'Set current time to current record time
ctlOpsID = rs(2) 'Set OpsID to current record Operations ID

temtim = GetElapsedTime(Nz(CurrTime - PrevTime)) ' Calculate time difference

'Update the table with the time difference based on current Operations ID
strSQL = "UPDATE OperationsNew SET OperationsNew.OpsHrs = " & temtim & " WHERE OperationsNew.OpsID = " & ctlOpsID

DoCmd.RunSQL strSQL 'Run strSQL statement

intctr = intctr + 1 'Increment counter
Loop 'Loop

Else
'Message if there are records
MsgBox "There are no time records.", vbInformation, "Not Correcting Time Differential"
Exit Function
End If
End With

Bye_CorrTimeDiff:
Exit Function

Err_CorrTimeDiff:
Resume Bye_CorrTimeDiff
End Function

 

[cmmrfrds]

You could try a While loop. While and Until check the condition at different times.

While NOT rs.EOF
'- do the stuff
Wend

OR
use rs.MoveNext and rs.MovePrevious
instead of absolute position which is probably causing the problem. If you are going to move forward a position then move back before exiting the loop, or you will be at EOF on the last record.

 

[BusMgr]

I know that while and until check at different times, but in this case both would still miss the final record. The issue isn't of absoluteposition or moveprevious, but the loop sees the EOF flag and does not do the time calculation on the final record.

The loop that I have leaves the first record alone, which is what I want, but it doesn't perform the calc. on the last record, which is what I need.
Thanks

BusMgr

 

[cmmrfrds]

What do you want to do if you hit EOF here.
.AbsolutePosition = intctr + 1

What do you want to do if you hit EOF here.
intctr = intctr + 1 'Increment counter

 

[BusMgr]

What I am trying to do is move through the qry and calculate the time difference between the current record (starting at record 1) and the previous record. The qry reorders the ToTime entered on a form if the user goes back and makes a correction to his time table based on field DayID and field ToTime. (I am assuming that for the first record (record 0) that the time difference has been set by the user and does not need to be recalculated.)

Logically thru the loop

record 0
.absoluteposition = intctr +1
record 1
calc time difference
update table for record 1
intctr = inctr + 1
loop
record 1
.absoluteposition = intctr +1
record 2
calc time difference
update table for record 2
intctr = inctr + 1
loop

etc

It reaches EOF based on the last intctr = intctr + 1
and drops out of loop before it calculates time difference on last record minus the previous record. I realize that I have to re-write my calculation for the last record to something like -

currtime = last record rs(4)
ctlOpsID = last record rs(2)
prevtime = previous record rs(4)
temtim = GetElapsedTime(Nz(CurrTime - PrevTime))

But I need to figure out how to get there first!

I appreciate your help.

Thanks
BusMgr

 

[cmmrfrds]

Have you tried something like this.

while......

'-first increment

.AbsolutePosition = intctr + 1
If rs.EOF = true then
may need to do something - not sure
end if

'- increment at end of loop
intctr = inctr + 1
.AbsolutePosition = intctr
if rs.EOF = true then
do the logic for the last record
make sure it is EOF when leaving
end if
loop

 

[SBendBuckeye]

Try something like this:

rst.MoveFirst
intCtr = 0
While Not rst.EOF
If intCtr > 0 Then 'Skip the first record
Do your calcs and updates
End If
intCtr = intCtr + 1
rst.MoveNext
Loop

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[JeremyNYC]

Hmm. Why not just do this

set rst = yadda
rst.movenext
do until rst.eof
Do stuff
rst.movenext
loop

It seems to me that there's no need for a counter at all. I will admit that I didn't read the code too closely, but it does seem like this should work.

There are a couple of things you could do to make your code easier to read. For one thing, indenting in a standardized way will help a lot. Stuff inside loops usually gets indented, to make it easier to see what code is being looped. Also, using the field names in your recordset will both make it easier to read, and make it more robust, because now if you add fields to the table there's a chance you'll shuffle the fields, and that would make your code fall over.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.