EOF PROBLEM

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glint - 27 Apr 2008 17:23 GMT

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?

Signature

Glint

Reply to this Message

Tom van Stiphout - 27 Apr 2008 19:04 GMT

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:
[quoted text clipped - 56 lines]
>
>What should I do?

Reply to this Message

Glint - 28 Apr 2008 22:56 GMT

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

Signature

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,
[quoted text clipped - 79 lines]
> >
> >What should I do?

Reply to this Message

Bob Quintal - 27 Apr 2008 20:27 GMT

> Hi Guys,
> I have this code on the afterupdate of a combo:
[quoted text clipped - 4 lines]
>
> What should I do?

it doesn't "overlook" .EOF, YOU are trying to do an update after a
.movenext but before you test for .EOF

try...
Do Until rstTrack.EOF
rstTrack.MoveNext
If rstTrack.EOF Then Exit Do
With rstClone
[your addnew]
End With
Loop

Or better yet, remove the first .addnew block, just start the loop

With rstClone
.MoveFirst
Do Until .EOF
With rstTrack
[your addnew]
end with
.MoveNext
Loop
End With

Signature

Bob Quintal

PA is y I've altered my email address.
** Posted from http://www.teranews.com **

Reply to this Message