object variable or with block variable not set 2

[lsantos]

when running a simple form with a button to retrieve table data I get "object variable or with block variable not set". Code follows. Any tips will be much appreciated. Luis

Option Compare Database

'This Type maps to the columns in Property file
Private Type OpenSpec

AgreeID As Integer ' Agreement ID
LastDueDate As Integer ' Last Rent Due

End Type

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim db As DAO.Database
Dim wksp As DAO.Workspace
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset

Dim spcSelected As OpenSpec

Dim strSQL As String

'strSQL = "SELECT * FROM [Property] where Agreement > 0"
strSQL = "SELECT * FROM [Property]"

Set db = CurrentDb
Set wksp = DBEngine.Workspaces(0)
'wksp.BeginTrans
Set rs = qd.OpenRecordset(strSQL)
rs.MoveFirst

MsgBox "# 1", vbExclamation

Do While Not rs.EOF

'Call OpenSpecGetDetails(spcSelected)

If rs![Agreement ID] <> 0 Then
MsgBox rs![Agreement ID], vbExclamation
End If

rs.MoveNext

Loop

'wksp.CommitTrans
rs.Close
Set rs = Nothing
wksp.Close

'If rs!RecordCount = 0 Then
'MsgBox ("No transactions to post.")
'Else
'MsgBox rs!RecordCount, vbExclamation
'End If

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Private Function OpenSpecGetDetails(spcAny As OpenSpec) As Boolean

On Error GoTo OpenSpecGetDetails_Err

'unpicks all the info from the list
With Me![listReports]

spcAny.[Agreement ID] = .Column(0)
spcAny.Description = .Column(30)
End With

OpenSpecGetDetails = True
OpenSpecGetDetails_exit:
Exit Function

OpenSpecGetDetails_Err:
ErrorMsgBox "OpenSpecGetDetails function"
Resume OpenSpecGetDetails_exit

End Function

 

[PHV]

I get "object variable or with block variable not set"
And which line is highlighted by the debugger ?
I guess the following:
Set rs = qd.OpenRecordset(strSQL)
as qd is not yet instantiated ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya lsantos . . . . .

[blue]PHV[/blue] is on target!. Following is a [blue]sample setup of a recordset thru Query Definition:[/blue]

[blue]   Dim db As DAO.Database, qdf As DAO.QueryDef
   Dim SQL As String, rst As DAO.Recordset
   
   Set db = currrentdb()
   SQL = "Select * From tblBest;"
   [purple][b]Set qdf = db.CreateQueryDef("", SQL)[/b][/purple]
   Set rst = qdf.OpenRecordset(dbOpenDynaset)[/blue]

See Ya! . . . . . .

 

[lsantos]

PHV and TheAceMan1,

Thank you very much for your replies. Spot on!