VBA problem with inner DB loop

[gci1000]

Having some problem with VBA record set not being set correctly - probably a
bug in the code. Any help would be great. Run-time error. I have the
following code:

Set rs = CurrentDb.OpenRecordset("SELECT Code, LongCaption FROM MyTable")
Do While rs.EOF = False

longDescExists = False
If currentLongDesc = "" Then
' --> Code crashed here when it reaches this point for the 2nd time
Set rs2 = CurrentDb.OpenRecordset("SELECT Code, " & descName & "
FROM " & tblName & " RT Where RT.Code = 'B'")
WhichRs = "rs2"
If rs2.EOF = False Then
longDesc = rs2(1)
If longDesc <> "" Then longDescExists = True
End If

End If
End If

rs.MoveNext
Loop
rs.Close
rs2.Close

 

[Dirk Goldgar]

Having some problem with VBA record set not being set correctly - probably
a
bug in the code. Any help would be great. Run-time error. I have the
following code:

Set rs = CurrentDb.OpenRecordset("SELECT Code, LongCaption FROM
MyTable")
Do While rs.EOF = False

longDescExists = False
If currentLongDesc = "" Then
' --> Code crashed here when it reaches this point for the 2nd time
Set rs2 = CurrentDb.OpenRecordset("SELECT Code, " & descName & "
FROM " & tblName & " RT Where RT.Code = 'B'")
WhichRs = "rs2"
If rs2.EOF = False Then
longDesc = rs2(1)
If longDesc <> "" Then longDescExists = True
End If

End If
End If

rs.MoveNext
Loop
rs.Close
rs2.Close

It's pretty clear that you ought to be closing rs2 inside the loop, since
you're opening it inside the loop. Aside from that, it's hard to tell what
your code is suppose to do and hence what the error is likely to be, because
there's nothing in the SQL for rs2 that seems to be loop-dependent -- it
doesn't make use of any values from the current record of rs. Is that an
exact and complete quote of the relevant code?

If you'll state what the error number and message are, maybe we can give you
a better guess.

 

[gci1000]

Yes, I tried to close the rs2 (rs2.close) after the inner group, and still
the same problem. It crashes the second time it reaches "Set rs2". BTW, yes,
a couple of lines were not included to simplify the question. Thanks.

 

[Klatuu]

In this line of code:
Set rs2 = CurrentDb.OpenRecordset("SELECT Code, " & descName & "
FROM " & tblName & " RT Where RT.Code = 'B'")

Where are descName and tblName Dimmed?
What data type are they?
Were do they get their value?
Are you sure they both have a legitmate value at the time you are doing the
OpenRecordset?
Crashes here is a bit vague. What error message are you getting?

 

[gci1000]

The variables seem to be ok and they legitimate values during a debug. The
error is "runtime error '3420', object invalid or no longer set." Let me know
if you can think of a possibility. Thank you.

 

[gci1000]

I found the bug. I have "RT.Code = " & mycode, it should be "RT.Code = '" &
mycode & "'". I simplified when I submitted the question.
Thanks for the pointing out looking for the variable types.

 

[Dirk Goldgar]

Yes, I tried to close the rs2 (rs2.close) after the inner group, and still
the same problem. It crashes the second time it reaches "Set rs2". BTW,
yes,
a couple of lines were not included to simplify the question. Thanks.

Please post the whole section of code. The only thing I can think of is
that maybe you have an On Error Resume Next statement in effect and your
call to OpenRecordset is raising an error.