Error message in coding

[PaulA]

Greatings, all--

I am getting an 3021 error message (no current record) for the following code.

I am relatively new at coding and this is the most complex I have ever gotten. Although the loop is supposed to end at the end of rst3 recordset, it seems to keep going. The error occurs on the line in red bold.

Can anyone spot the problem?

Here's the code:

'create last retraining date record for each staff for report table from query recordset

rst3.MoveFirst
'add first record for this process and set fixed field values
.AddNew
strStaff = rst3![Employee Name]
strID = rst3!EmployeeID
!Head = rst3![Start Date]
!Name = strStaff
!ID = rst3!EmployeeID
!Dept = rst3!Department
!Start = rst3![Start Date]
!Line = 3

'Do until end of query recordset
Do Until rst3.EOF = True
'if EmployeeID is same as previous query record
If rst3!EmployeeID = strID Then
'if staff has Retraining records; if he doesn't, record will have
'empty subject name field.
If rst3!TrainingSubjectName <> "" Then
.Fields(rst3!TrainingSubjectName) = rst3!max
rst3.MoveNext
Else 'if subject name is empty, means new staff who has not had retraining on any subject
.Update 'update current record
.AddNew 'add new record for new EmployeeID
'set fixed field values
strStaff = rst3![Employee Name]
strID = rst3!EmployeeID
!Head = rst3![Start Date]
!Name = strStaff
!ID = rst3!EmployeeID
!Dept = rst3!Department
!Start = rst3![Start Date]
!Line = 3
.Update
'Update as such Employee has only one record in query recordset
'Go to next query record and addnew table record and set fixed field values
rst3.MoveNext
.AddNew
strStaff = rst3![Employee Name]
strID = rst3!EmployeeID
!Head = rst3![Start Date]
!Name = strStaff
!ID = rst3!EmployeeID
!Dept = rst3!Department
!Start = rst3![Start Date]
!Line = 3
End If
'If StaffID is new but subjectname is not empty, update current table record and
'add new table record and set fixed field values
Else
.Update
.AddNew
strStaff = rst3![Employee Name]
strID = rst3!EmployeeID
!Head = rst3![Start Date]
!Name = strStaff
!ID = rst3!EmployeeID
!Dept = rst3!Department
!Start = rst3![Start Date]
!Line = 3
End If

Loop
.Update
.AddNew
rst3.Close
'End of process for last retraining date records


Thanks!!

Paul

 

[pbaldy]

For starters, it looks like you've reversed the fields. It's:

Destination = Source

 

[PaulA]

Thanks for replying.

I don't see the reversal--

The destination is a recordset called "rstReport". The source is rst3. I had similar (but not identical) loops for rst1 and rst2 and didn't have a problem.

Thanks.

Paul

 

[gemma-the-husky]

where yuor code crashes, you have

rst3.MoveNext

BUT you are not testing to see if you have reached the end of the rst3 file, so when you do

.AddNew
strStaff = rst3![Employee Name]

this crashes

you need to test for endofile first

so you get instead

rst3.MoveNext
if rst.eof then handle eof condition
'and only then carry on
.AddNew
strStaff = rst3![Employee Name]

it looks like you are doing a movenext twice in the same loop - is that correct/what you intended