No current record

  • Thread starter Thread starter robc
  • Start date Start date
R

robc

Basically this gets stuck on the last row inside the inner loop every time.
Why doesn't the "And Not rs.EOF" work ?

Function PrjOrdSeq()

Dim db As database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("qrytblPrjOrdLinHier-SORT")
Dim strCurJob As String
Dim cnt As Long

cntRec = DCount("[Job]", "qrytblPrjOrdLinHier-SORT")

rs.MoveFirst

Do While Not rs.EOF
strCurJob = rs!Job
cnt = 1

Do While rs!Job = strCurJob And Not rs.EOF
rs.Edit
rs!Sequence = cnt
rs.Update
cnt = cnt + 1
rs.MoveNext
Loop

Loop


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Function
 
Not sure what you are doing but the problem in the criteria in the inner
loop:

Do While rs!Job = strCurJob And Not rs.EOF

If rs!Job is different from strCurrentJob AND EOF is true, you code WILL try
to move past the EOF and hence you get an error!
 
Thanks for responding,

I am simply trying to add a counter on a per Job basis. I am updating a
column called Sequence which I wish to increment on a per Job basis.

Job Sequence
------ ----------
JOBA 1
JOBA 2
JOBA 3
JOBB 1
JOBB 2
JOBB 3
JOBB 4

Problem appears to be on last step... at EOF the Loop still wants to
increment 1 more time. That is when it crashes and I get "No Current
Record". Am I testing for EOF at the wrong time or something ?

Thanks !



Van T. Dinh said:
Not sure what you are doing but the problem in the criteria in the inner
loop:

Do While rs!Job = strCurJob And Not rs.EOF

If rs!Job is different from strCurrentJob AND EOF is true, you code WILL try
to move past the EOF and hence you get an error!

--
HTH
Van T. Dinh
MVP (Access)



robc said:
Basically this gets stuck on the last row inside the inner loop every time.
Why doesn't the "And Not rs.EOF" work ?

Function PrjOrdSeq()

Dim db As database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.openrecordset("qrytblPrjOrdLinHier-SORT")
Dim strCurJob As String
Dim cnt As Long

cntRec = DCount("[Job]", "qrytblPrjOrdLinHier-SORT")

rs.MoveFirst

Do While Not rs.EOF
strCurJob = rs!Job
cnt = 1

Do While rs!Job = strCurJob And Not rs.EOF
rs.Edit
rs!Sequence = cnt
rs.Update
cnt = cnt + 1
rs.MoveNext
Loop

Loop


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Function
Click to expand...
Click to expand...
 
As I wrote the problem is that you tested TWO conditions at the same time!
Please re-read my earlier post.

You need to change you logic, especially the nested While loop.
 
Back
Top