No current record error (1 Viewer)

[WayneRoberts]

Hello,

The following code is used in calculating the due date for actions. The due date for each action is calculated from a preceding action date, by adding a defined number of days. The relationship between the actions is defined by assigning the predecessor/preceding action.

I am trying to use a nested loop to firstly assign the criteria data agaisnt which the rest of the record set must be checked in order to determine if the criteria data is a predecessor and than to use the predecessor's information to calculate the due date for the record being checked.

I keep on getting a no current record error at the .edit inside the nested loop. Could anybody please assist with some advice on this ?


'**Calculate action Dates


Public Sub CalculateDates()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim NumOfRec As Long
Dim CritCounter As Long
Dim EvalCounter As Long

'SQL statement
Dim strName As String

'Criteria selection

Dim CritActionID As Long
Dim CritActionIDPlanDate As Date


strName = "E" & GVariable() & GVariableI()


strSQL = "SELECT * FROM EventsAction WHERE EventID ='" & strName & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


With rs

.MoveLast
.MoveFirst

NumOfRec = rs.RecordCount

CritCounter = 1
EvalCounter = 1



Do Until CritCounter = NumOfRec + 1

'set checking criteria
CritActionID = ![ActionID]
CritActionIDPlanDate = ![PlanDate]


'Loop through results and calculate planned action date if CriteriaActionID is predecessor
EvalCounter = 1
rs.Move EvalCounter 'Move back to first record prior to starting date calculation

Do Until EvalCounter = NumOfRec + 1


If ![PredID] = CritActionID Then
.Edit
![PlanDate] = CritActionIDPlanDate + ![ActionWindow]
.Update
EvalCounter = EvalCounter + 1

Else

EvalCounter = EvalCounter + 1

End If

If EvalCounter > NumOfRec Then
Exit Do
Else
rs.Move EvalCounter
End If

Loop



CritCounter = CritCounter + 1

If CritCounter > NumOfRec Then
Exit Do
Else
rs.Move CritCounter
End If

Loop


rs.Close
Set rs = Nothing

End With

End Sub

 

[namliam]

Please use the code tags, see my sig for details.

No current record means you are trying to edit a record that doesnt exist, i.e. you reached eof.

 

[Keith Tedbury]

Before the code you want to run that uses the record set you just opened you need an if statement that checks to make sure that it has records.

To do that use the recordcount property

With rs

    If .RecordCount <> 0 Then

        .MoveLast
        .MoveFirst

        code to run

        Loop

    End If

    rs.Close
    Set rs = Nothing

End With

If the record count is not 0 it will continue on and run the code otherwise it skip and avoid the error. You may want a message box to tell you there was no records


As a side note its generally good practice to indent code when you use things like if and with as it makes it easier to read and see what code is affected by them.