I am trying to loop through a recordset using a do until
loop in Access 97. Here is an example of the code:

Dim rsTimeoff As ADODB.recordset
Dim ConnStr6 As String
ConnStr6 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source "
Set rsTimeoff = New ADODB.recordset
rsTimeoff.Open "us_TimeOff", ConnStr6,
adOpenForwardOnly, adLockPessimistic

rsTimeoff.MoveFirst

Do Until (rsTimeoff!us_agentSSN = strGlobalSSN And
rsTimeoff!us_Date = dtmJobDate) Or (rsTimeoff.EOF = True)

rsTimeoff.MoveNext
Loop


The loop works fine when there is a record in the
recordset that matches the variables that are being
compared. Whenever there is not a record in the recordset
that matches the variables, the loop continues until it
reaches the End Of File (.EOF). At this point it should
jump out of the loop and perform the next line in the code
outside of the loop structure. Instead, it is throwing an
error. The error is the 3021 runtime error. How can I
prevent this from occuring using Access 97's VBA. I am
using ADO to connect to the database if this helps. I
have written code very similar to this in the past with
VB6 and it works just fine. I even have examples in books
that do the same thing in VB6. How can I get the loop to
end and jump to the next line in the code when it
reaches .EOF? Any help would be greatly appreciated.

Thanks

JDB

You might try changing the Do Loop condition to only
end the loop on the EOF condition, so that it won't
be trying to evaluate the other condition while the
file is at EOF, as I suspect that's where it dies.
Then you can just add an If statement to evaluate the
other condition, where if true, you "Exit Do". Here's
a quick re-hash of just that part of the code with my
suggested changes...

Do Until rsTimeoff.EOF = True
If (rsTimeoff!us_agentSSN = strGlobalSSN And _
rsTimeoff!us_Date = dtmJobDate) Then
Exit Do
End If
rsTimeoff.MoveNext
Loop

If for any reason "Exit Do" turns out not to be a valid
statement, here's the alternative:

Dim GetOut
GetOut = 0
Do Until (rsTimeoff.EOF = True) Or (GetOut = 1)
If (rsTimeoff!us_agentSSN = strGlobalSSN And _
rsTimeoff!us_Date = dtmJobDate) Then
GetOut = 1
Else
rsTimeoff.MoveNext
End If
Loop

Hope that helps...

Steve

>-----Original Message-----
>I am trying to loop through a recordset using a do until
>loop in Access 97. Here is an example of the code:
>
>Dim rsTimeoff As ADODB.recordset
> Dim ConnStr6 As String
> ConnStr6 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> " Data Source "
> Set rsTimeoff = New ADODB.recordset
> rsTimeoff.Open "us_TimeOff", ConnStr6,
>adOpenForwardOnly, adLockPessimistic
>
> rsTimeoff.MoveFirst
>
> Do Until (rsTimeoff!us_agentSSN = strGlobalSSN And
>rsTimeoff!us_Date = dtmJobDate) Or (rsTimeoff.EOF = True)
>
> rsTimeoff.MoveNext
> Loop
>
>
>The loop works fine when there is a record in the
>recordset that matches the variables that are being
>compared. Whenever there is not a record in the
recordset
>that matches the variables, the loop continues until it
>reaches the End Of File (.EOF). At this point it should
>jump out of the loop and perform the next line in the
code
>outside of the loop structure. Instead, it is throwing
an
>error. The error is the 3021 runtime error. How can I
>prevent this from occuring using Access 97's VBA. I am
>using ADO to connect to the database if this helps. I
>have written code very similar to this in the past with
>VB6 and it works just fine. I even have examples in
books
>that do the same thing in VB6. How can I get the loop
to
>end and jump to the next line in the code when it
>reaches .EOF? Any help would be greatly appreciated.
>
>Thanks
>
>JDB
>.
>

"J Bailey" <(E-Mail Removed)> wrote in
news:072c01c37eeb$d8fadf40$(E-Mail Removed):

> rsTimeoff.MoveFirst

This line does nothing except raise an error if the recordset is empty:
just junk it.


> Do Until (rsTimeoff!us_agentSSN = strGlobalSSN And _
> rsTimeoff!us_Date = dtmJobDate) _
> Or (rsTimeoff.EOF = True)

This is a complex boolean expression, and while it may be correct, and it
might even mean something to you now, you'll stare at it in eighteen months
and wonder what the hell you were trying to do. You would be much better
served by splitting it up, and at the same time avoid the error:

' first test is the EOF, so you don't even get into
' the loop if it's an empty recordset
Do While Not rsTimeOff.EOF

' okay, now look for test conditions and exit
' when they are both met
If rsTimeOff!us_agentSSN = strGlobalSSN And _
rsTimeOff!us_Date = dtmJobDate Then
Exit Do

End If

> rsTimeoff.MoveNext
> Loop


> I
> have written code very similar to this in the past with
> VB6 and it works just fine.

The reason it fails here and works in VB6 is that VB will short-circuit
boolean expressions: as soon as one part of an OR list is true it stops
testing the rest, and vice versa for AND lists. VBA is not so clever,
however, and tries to evaluate each little bit regardless of whether it
will affect the outcome. There are advantages and disadvantages of each
approach, but you just need to know what is going to happen.


Incidentally, I notice that this loop does absolutely nothing except to see
if there is a row that meets the criterion. This might be because you
wanted to save space in the message (good idea!); but if you really only
want to test for that then you can do it with less grief in one go:

strSQL = "SELECT COUNT(*) AS NumRecs " & _
"FROM us_TimeOff " & _
"WHERE us_agentSSN = '" & strGlobalSSN & "' " & _
" AND us_Date = Format(dtmJobDate, strSQLDtFormat)

rs.Open strSQL, ConnStr6, adOpenForwardOnly, adLockPessimistic

If rs!NumRecs = 0 Then
' there is no record there

Else
' there is at least one

End If


Hope that helps


Tim F