Error 91

[TaylorTot]

Hello,

I'm getting Error 91, (Object Variable or With Block not set) with the following code:

Sub MapSelectedProperties()
'Map the selected properties
On Error GoTo MapSelectedProperties_Err_Exit
Dim db As Database
Dim rstProps As DAO.Recordset


Dim objLoc As MapPoint.Location
Dim objMap As MapPoint.map
Dim objPushpin As MapPoint.Pushpin

Dim strMsg As String
Dim i As Integer
i = 0
Set db = CurrentDb()

'Load the selected properties into a recordset
Set rstProps = CurrentDb.OpenRecordset("Get20Mile_SelQry")
'Make sure at least one property was selected
If rstProps.RecordCount > 0 Then
'Load Map
If LoadMap() Then
'Open the form containing the map
FormOpen "frmMap"
Set objMap = gappMP.ActiveMap
'Place a pushpin on the map for each selected property
While Not rstProps.EOF
i = i + 1
Set objLoc = objMap.FindAddressResults(rstProps!Address_1, rstProps!City, rstProps!State, rstProps!Zip)(1)
Set objPushpin = objMap.AddPushpin(objLoc, rstProps!Address_1)
objPushpin.Name = CStr(i)
objPushpin.Note = rstProps!Company
objPushpin.BalloonState = geoDisplayBalloon
objPushpin.Symbol = 77
objPushpin.Highlight = True
rstProps.MoveNext
Wend
'Show all pushpins on the map display
objMap.DataSets.ZoomTo
Else
strMsg = "Unable to load map."
MsgBox strMsg, vbOKOnly + vbExclamation, APP_NAME
End If
Else
strMsg = "No properties selected."
MsgBox strMsg, vbOKOnly + vbExclamation, APP_NAME
End If
MapSelectedProperties_Err_Exit:
On Error Resume Next
Set objPushpin = Nothing
Set objLoc = Nothing
Set objMap = Nothing
rstProps.Close
db.Close
Exit Sub
MapSelectedProperties_Err:
Resume MapSelectedProperties_Err_Exit
End Sub

I'm trying to run a query, but continue to receive this error at the following line:

rstProps.Close

Thanks for your help in advance!

 

[PHV]

You may try this:
If Not (rstProps Is Nothing) Then rstProps.Close

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

 

[TaylorTot]

Thank you very much for the quick reply.

I added this to my code, and yes it removed the error, but now it isn't doing anything??

Any other suggestions?

Again, thank you

 

[PHV]

Comment out the On Error instruction to discover why you can't instantiate rstProps

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

 

[TaylorTot]

Okay, thanks again, I was able to retrieve the error:

Too Few Parameters. Expected 1

On line:

Set rstProps = db.OpenRecordset("Get20Mile_SelQry")

 

[Golom]

My guess would be that this

Set rstProps = CurrentDb.OpenRecordset("Get20Mile_SelQry")

is raising an error but the MapSelectedProperties_Err: block doesn't have any code to report it. When the processing finally gets to [blue]rstProps.Close[/blue], the recordset hasn't been instantiated so you get the error. Try inserting some code like this

MapSelectedProperties_Err:
  MsgBox Err.Number & " - " & Err.Description
  Resume MapSelectedProperties_Err_Exit

 

[PHV]

Seems that Get20Mile_SelQry is a parametized query ...

 

[TaylorTot]

I am still getting the Too Few Parameters Error.

Could it be because I have a parameter in my query (Get20Mile.SelQry)? I tried the following Code:

Set rstProps = CurrentDb.OpenRecordset("Select * FROM Get20Mile_SelQry WHERE (qry_get.ZIP_CODE)=[Forms]![frmMain]![txtZipCode]")

Again I received the Too Few Parameters Error.

Thank you both for your help ;-)

 

[Golom]

Try this

 Set rstProps = CurrentDb.OpenRecordset( _
"Select * FROM Get20Mile_SelQry " & _
"WHERE  (qry_get.ZIP_CODE)='" & _
[Forms]![frmMain]![txtZipCode] & "'")

 

[TaylorTot]

Thank you both for your help, I got it to work!

Have a great day