Need Help with Runtime Error 91

Question

I get a Runtime Error 91 "object variable or with block variable not set" message when I try to run the following code.  I have not been able to solve this issue.  Any help would be appreciated.

Option Compare Database

Option Explicit

Private Sub LadiesTIndex_Click()

On Error GoTo ErrorHandler

Dim dbs As DAO.Database

Dim rsSQL As DAO.Recordset

Dim qdf As DAO.QueryDef

Dim MemberNum As Integer

Dim Differential As Currency

Dim TournDate As Date

Dim mySQL As String

Set dbs = CurrentDb

mySQL = ""

While Not rsSQL.EOF

mySQL = mySQL & "SELECT TOP 50 PERCENT" & _

"qryLTIndexOrder.Differential" & _

"qryLTIndexOrder.MemberNum, qryLTIndexOrder.TournDate," & _

"FROM qryLTIndexOrder," & _

"WHERE qryLTIndexOrder.memberNum = 92127, 93509"

rsSQL.MoveNext

If Not rsSQL.EOF Then mySQL = mySQL

Wend

Set rsSQL = dbs.OpenRecordset(mySQL, dbOpenSnapshot)

With dbs

Set qdf = .CreateQueryDef("tmpMemberNum", mySQL)

DoCmd.OpenQuery "tmpMemberNum"

.QueryDefs.Delete "tmpMemberNum"

End With

dbs.Close

qdf.Close

Exit Sub

ErrorHandler:

MsgBox Err.Description

End Sub

Answer 1

What is the exact text that it printed? This will be a clue as to why you're getting the error you quoted.

Answer 2

I placed debug.print mySQL after mySQL and it printed the results of mySQL in the intermediate window.  I found this recommendation in a number of this forums answers.

Answer 3

My crystal ball is broken. What does the debug.print print?

Answer 4

Thank you, I missed that.  Now I get an error message that the "select statement includes a reserved word or an argument that is misspelled or missing, or the punctuation is incorrect". When I add "debug.print mySQL", the result looks correct.  Here is my corrected code.

Option Compare Database

Option Explicit

Private Sub LadiesTIndex_Click()

On Error GoTo ErrorHandler

Dim dbs As DAO.Database

Dim rsSQL As DAO.Recordset

Dim qdf As DAO.QueryDef

Dim MemberNum As Integer

Dim Differential As Currency

Dim TournDate As Date

Dim mySQL As String

Set dbs = CurrentDb

Set rsSQL = dbs.OpenRecordset("qryLTIndexOrder", dbOpenSnapshot)

mySQL = ""

While Not rsSQL.EOF

mySQL = mySQL & "SELECT TOP 50 PERCENT " & _

"qryLTIndexOrder.Differential, " & _

"qryLTIndexOrder.MemberNum, qryLTIndexOrder.TournDate, " & _

"FROM qryLTIndexOrder, " & _

"WHERE ((qryLTIndexOrder.MemberNum) = 92127) "

rsSQL.MoveNext

If Not rsSQL.EOF Then mySQL = mySQL

Wend

With dbs

Set qdf = .CreateQueryDef("tmpMemberNum", mySQL)

DoCmd.OpenQuery "tmpMemberNum"

.QueryDefs.Delete "tmpMemberNum"

End With

dbs.Close

qdf.Close

Exit Sub

ErrorHandler:

MsgBox Err.Description

End Sub

Answer 5

This line: While Not rsSQL.EOF

will fail, because rsSQL has not been assigned yet. IOW, there should be a "Set rsSQL = ..." line before it.