No current record

[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

 

[Van T. Dinh]

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!

 

[robc]

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 !

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)

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

 

[Van T. Dinh]

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.