EOF PROBLEM

[Glint]

Hi Guys,
I have this code on the afterupdate of a combo:

Dim X As Long
X = Me.Recordset.RecordCount
'If Not IsNull(Location1) And Location2 <> Location1 Then 'specify a
previous location different from a new one so that you do not attempt to move
'that you do not attempt to move an item into the same old location.
If vbYes = MsgBox("Do you want to move all " & X & " items to this
new location?", vbYesNo, "ECKANKAR AREA ADMIN") Then
Dim rstTrack As DAO.Recordset, rstClone As DAO.Recordset
Set rstTrack = DBEngine(0)(0).OpenRecordset("InventoryTrack")
Set rstClone = Me.RecordsetClone
rstClone.MoveFirst
With rstTrack
.AddNew
!ItemID = rstClone!ItemID
!PreviousLocation = rstClone!LL
!Location = Location2
!ApprovedBy = DLookup("[UserMember] ", "UsersTable",
"[UserPassword] = Forms![Open Sesame]![User]")
!AllotmentDate = Date
!AllotmentTime = Time
.Update
End With
Do Until rstClone.EOF
'If rstClone.EOF Then Exit Do
rstClone.MoveNext
With rstTrack
.AddNew
!ItemID = rstClone!ItemID
!PreviousLocation = rstClone!LL
!Location = Location2
!ApprovedBy = DLookup("[UserMember] ", "UsersTable",
"[UserPassword] = Forms![Open Sesame]![User]")
!AllotmentDate = Date
!AllotmentTime = Time
.Update
End With
Loop
rstTrack.Close
Set rstTrack = Nothing
Else
Exit Sub
End If
'ElseIf IsNull(Location1) Then
' MsgBox "You need to specify a previous location to avoid moving
from and into the same place.", vbInformation, "ECKANKAR AREA ADMIN"
' DoCmd.GoToControl "Location1"
'Else
' MsgBox "You have picked the same old location.", vbInformation,
"ECKANKAR AREA ADMIN"
' Exit Sub
'End If

The code updates my table alright, but somehow overlooks the DO UNTIL
rstClone.EOF. I tried DO WHILE Not rstClone.EOF and still got the Rnu Time
error '3021': No Current Record.

What should I do?

 

[Tom van Stiphout]

On Sun, 27 Apr 2008 09:23:01 -0700, Glint

I wonder why you're not using an insert query. Soo much code that can
go wrong, could all be replaced by one or two queries. But that aside,
you are writing:
do until rstClone.eof
rstClone.MoveNext
'access rstClone.ItemID
'etc.
loop

So the last time around in the first line we are at the last record.
Then you MoveNext so we're now at EOF. Then you try to read that
record, and KABOOM!

Much better:
while not rs.EOF
'do the work, access the record, etc.

'prepare for next iteration
rs.MoveNext
wend

-Tom.

 

[Glint]

Thanks Guys,
Your suggestions are noted.
I think I will also try an insert query as you suggested.
--
Glint

On Sun, 27 Apr 2008 09:23:01 -0700, Glint

I wonder why you're not using an insert query. Soo much code that can
go wrong, could all be replaced by one or two queries. But that aside,
you are writing:
do until rstClone.eof
rstClone.MoveNext
'access rstClone.ItemID
'etc.
loop

So the last time around in the first line we are at the last record.
Then you MoveNext so we're now at EOF. Then you try to read that
record, and KABOOM!

Much better:
while not rs.EOF
'do the work, access the record, etc.

'prepare for next iteration
rs.MoveNext
wend

-Tom.

Hi Guys,
I have this code on the afterupdate of a combo:

Dim X As Long
X = Me.Recordset.RecordCount
'If Not IsNull(Location1) And Location2 <> Location1 Then 'specify a
previous location different from a new one so that you do not attempt to move
'that you do not attempt to move an item into the same old location.
If vbYes = MsgBox("Do you want to move all " & X & " items to this
new location?", vbYesNo, "ECKANKAR AREA ADMIN") Then
Dim rstTrack As DAO.Recordset, rstClone As DAO.Recordset
Set rstTrack = DBEngine(0)(0).OpenRecordset("InventoryTrack")
Set rstClone = Me.RecordsetClone
rstClone.MoveFirst
With rstTrack
.AddNew
!ItemID = rstClone!ItemID
!PreviousLocation = rstClone!LL
!Location = Location2
!ApprovedBy = DLookup("[UserMember] ", "UsersTable",
"[UserPassword] = Forms![Open Sesame]![User]")
!AllotmentDate = Date
!AllotmentTime = Time
.Update
End With
Do Until rstClone.EOF
'If rstClone.EOF Then Exit Do
rstClone.MoveNext
With rstTrack
.AddNew
!ItemID = rstClone!ItemID
!PreviousLocation = rstClone!LL
!Location = Location2
!ApprovedBy = DLookup("[UserMember] ", "UsersTable",
"[UserPassword] = Forms![Open Sesame]![User]")
!AllotmentDate = Date
!AllotmentTime = Time
.Update
End With
Loop
rstTrack.Close
Set rstTrack = Nothing
Else
Exit Sub
End If
'ElseIf IsNull(Location1) Then
' MsgBox "You need to specify a previous location to avoid moving
from and into the same place.", vbInformation, "ECKANKAR AREA ADMIN"
' DoCmd.GoToControl "Location1"
'Else
' MsgBox "You have picked the same old location.", vbInformation,
"ECKANKAR AREA ADMIN"
' Exit Sub
'End If

The code updates my table alright, but somehow overlooks the DO UNTIL
rstClone.EOF. I tried DO WHILE Not rstClone.EOF and still got the Rnu Time
error '3021': No Current Record.

What should I do?