Why does "<>" not produce a 3021 error as does "=" in recordset

  • Thread starter Thread starter Dale
  • Start date Start date
D

Dale

I am numbering fields in one table by comparing to its relational one to
many table's primary key. The two fields used in the comparison are text,
on the last record of the comparison if I use "=" rather than "<>" in the
second part of the loop I get the runtime error "3021 no current record".
In my test environment I cannot reproduce the error, only in the production
environment. All is well if I use the "<>" comparator in both test and
production environments....can anyone explain to me the significance of "<>"
over the "="?

Thanks...

Do While Not rstPatients.EOF
lngCount = 1
Do While rstPatients!Patsys <> rstEncounters!Patsys
With rstEncounters
.Edit
!encnum = lngCount
.Update
.MoveNext
lngCount = lngCount + 1
Debug.Print lngCount
End With
Loop
rstPatients.MoveNext
Loop
ErrExit:
rstEncounters.Close
rstPatients.Close
Set dbs = Nothing
strEnd = Now()
MsgBox "Operation completed: " & Format(DateDiff("n", strStart, strEnd),
"###0.0000") & " mins"
 
Your inner loop does not test for EOF, so there really is no current record
in rstEncounters when it reaches the last record.
 
It looks to me that you are not testing for EOF of the Recordset
rstEncounters and so the code may try to access a value past EOF. Hence the
error.

--
HTH
Van T. Dinh
MVP (Access)
 
If the code does not produce an error with the "<>" comparator, does that
mean the code is optimal? Or should I be testing for EOF in the
rstEncounters and if so would that be another loop statement or an IF
statement within the inner loop?

Thanks again...
 
I always check for EOF after MoveNext before trying to access the Field
values. Otherwise run-time errors can occur in your database.

Generally, I use Loops in traversing Recordsets ...
 
Thanks Van
Can you give an example, I think this case is a little unusual as there are
two recordsets being tested and there are already 2 loops running. Any
tests for EOF in the rstEncounters that I have tried within the inner loop
and before the movenext statement, throws my numbering off as the loop
doesn't exit to restart numbering at 1.
 
I am not sure what your code is trying to do.

Describe in word what you want tthe code to do.
 
Back
Top