Do Loop problem

[Tcs]

I have a variable number of records in a table I use to build my pass-thru SQL
statement. I find out how many rows there are, and divide by 100, building my
statement using only 100 rows at a time. (To keep the overall size of the
statement within limits.) Then I perform a loop for how many times I need,
i.e., 706 rows = 8 loops, 701 rows = 8 loops, 700 rows = 7 loops, etc. From
within this loop I call a function which loops the 100 necessary to get the rows
and build the statement. All this actually works...BUT...not always.

I seem to get the:

Error # 3021 was generated by DAO.Field
No current record.

error intermittently. It would seem I still don't quite understand when rs.EOF
is true. I've done this kind of thing before, many times, although NOT in VBA.
Here's my code, any suggestions/corrections would be greatly appreciated.

If Not rs.EOF Then
If rs.EOF Then
Exit Function
End If
rs.MoveNext
strSQLwhere1a = _
"((b.UTCSID=" & rs![CustID] & ") AND (b.UTLCID=" & rs![LocID] & "))"
MsgBox strSQLwhere1a, , "strSQLwhere1a - " & Date & " - " & Time
Else
Exit Function
End If
Do While Not rs.EOF
If rs.EOF Then
Exit Function
End If
For intLoop2 = 1 To 99
' MsgBox "(" & intLoop1 & ")", , "intLoop1 - " & Date & " - " & Time
' MsgBox "(" & intLoop2 & ")", , "intLoop2 - " & Date & " - " & Time
If intLoop1 > 6 Then
' MsgBox "(" & intLoop1 & ")", , "intLoop1 - " & Date & " - " & Time
' MsgBox "(" & intLoop2 & ")", , "intLoop2 - " & Date & " - " & Time
End If

rs.MoveNext

strSQLwhere1a = strSQLwhere1a & _
" OR ((b.UTCSID=" & rs![CustID] & ") AND (b.UTLCID=" & rs![LocID] & "))"
' MsgBox strSQLwhere1a, , "strSQLwhere1a - " & Date & " - " & Time
DoEvents
Next intLoop2
Exit Do
Loop

Thanks in advance,

Tom

 

[Wayne Morgan]

Does it tend to happen near the end of the run? The internal loop (the For
loop) has no check for EOF, it will run 99 times or until an error is
encountered.

 

[Ted Allen]

I agree with Wayne's post, but in addition you have a
movenext command after you check for .eof on the fifth
line of your code. Then, you reference the recordset
field values.

I'm guessing that you are thinking that .eof means that
the recordset is on the last line of data, but this is
not the case. When moving through a recordset you will
eventually get to the last record. At that point, .eof
will still be false. When you use .movenext from the
last record, .eof will become true and there will not be
any current record and therefore no field values.

Also, the 2nd through fourth lines of your code seem
unnecessary. You already know that .eof is false by the
if statement in line 1.

In general, when looping through recordsets, you will
check the .eof property to enter the loop as you have
done, then you will work with the record within the loop,
and at the very end you would use .movenext prior to
looping back to check the .eof property value. This way,
when .eof is true, you know that you have run through all
records.

HTH, Ted Allen

-----Original Message-----
Does it tend to happen near the end of the run? The internal loop (the For
loop) has no check for EOF, it will run 99 times or until an error is
encountered.

--
Wayne Morgan
MS Access MVP

I have a variable number of records in a table I use to build my pass-thru
SQL
statement. I find out how many rows there are, and divide by 100,
building my
statement using only 100 rows at a time. (To keep the overall size of the
statement within limits.) Then I perform a loop for how many times I
need,
i.e., 706 rows = 8 loops, 701 rows = 8 loops, 700 rows = 7 loops, etc.
From
within this loop I call a function which loops the 100 necessary to get
the rows
and build the statement. All this actually works...BUT...not always.

I seem to get the:

Error # 3021 was generated by DAO.Field
No current record.

error intermittently. It would seem I still don't quite understand when
rs.EOF
is true. I've done this kind of thing before, many times, although NOT in
VBA.
Here's my code, any suggestions/corrections would be greatly appreciated.

If Not rs.EOF Then
If rs.EOF Then
Exit Function
End If
rs.MoveNext
strSQLwhere1a = _
"((b.UTCSID=" & rs![CustID] & ") AND (b.UTLCID=" & rs![LocID] & "))"
MsgBox strSQLwhere1a, , "strSQLwhere1a - " & Date & " - " & Time
Else
Exit Function
End If
Do While Not rs.EOF
If rs.EOF Then
Exit Function
End If
For intLoop2 = 1 To 99
' MsgBox "(" & intLoop1 & ")", , "intLoop1 - " & Date & " - " & Time
' MsgBox "(" & intLoop2 & ")", , "intLoop2 - " & Date & " - " & Time
If intLoop1 > 6 Then
' MsgBox "(" & intLoop1 & ")", , "intLoop1 - " & Date & " - " & Time
' MsgBox "(" & intLoop2 & ")", , "intLoop2 - " & Date & " - " & Time
End If

rs.MoveNext

strSQLwhere1a = strSQLwhere1a & _
" OR ((b.UTCSID=" & rs![CustID] & ") AND (b.UTLCID=" & rs![LocID] &
"))"
' MsgBox strSQLwhere1a, , "strSQLwhere1a - " & Date & " - " & Time
DoEvents
Next intLoop2
Exit Do
Loop

Thanks in advance,

Tom

.

 

[Tcs]

Are you saying that I should be doing something like this?

Do While or For Next loop
test for eof
work with the record
movenext
loop

I guess I have to ask, when one first opens the recordset, does one have the
first record available to work with? I've seen code that makes this appear
true, but when I tried it, it didn't work. That's why I performed the MoveNext
BEFORE I worked with the record. If I have a recoard available to work with
when I open the recordset, that would mean my code is actually MISSING (skipping
over) the first record, right?

I REALLY appreciate the help here. Thanks,

Tom

 

[Brendan Reynolds]

I can't think of a situation in which the cursor is not positioned on the
first record immediately after opening a recordset, assuming, of course, the
recordset contains records. But if you want to be sure, use MoveFirst rather
than MoveNext.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Tcs]

Funny you should ask. I've had it both ways. I had eof code both just before
and just after the MoveNext command inside the For loop. It didn't seem to make
a difference. Over the couple days I was testing, the number of records went
from 707 to 701 to 774. For the 707 and or 701 quantities, I'm thinking that I
was right on the line, so to speak. If it was the 707 number, and I was
skipping the 1st rec because I executed a MoveNext before I should have, I'd
actually only have 700, not the correct 707. (7 loops * 1 skip/loop.)

I need to work with it some more.

Thanks.

 

[Tcs]

Well... I THOUGHT that I had seen this, but it wasn't available. I just went
back and tried it again, and it IS available. (Perhaps I'm just getting too old
for this? :0 )

Thanks.

 

[Dirk Goldgar]

I can't think of a situation in which the cursor is not positioned on
the first record immediately after opening a recordset, assuming, of
course, the recordset contains records. But if you want to be sure,
use MoveFirst rather than MoveNext.

To the best of my knowledge, there is no circumstance in which a
recordset is not positioned at the first record immediately after
opening, so long as the recordset contains records. If the recordset
does not contain records, both BOF and EOF will be true, and a call to
..MoveFirst will raise an error. Therefore, code like this is perfectly
safe and appropriate:

Set rst = CurrentDb.OpenRecordset( ... )
Do Until rst.EOF
' ...
rst.MoveNext
Loop

If, on the other hand, you are handed a recordset and you don't know
whether it was just opened or not, you would probably want to do
something like this:

If rst.EOF And rst.BOF Then
' this recordset is empty
Else
rst.MoveFirst
Do Until rst.EOF
' ...
rst.MoveNext
Loop
End If