MapForums

I have a form in Access that plots our oil well records within a specified distance from a center point using Mappoint. Everything works except -
1. Access crashes when you go to close it.
2. You can only run the plot once w/o closing access. If I try to run the mapping feature more than once, I get an error message- Method 'Distance' of object '_Map' failed.
3. I'd like to turn off the Save Map msgbox at the end of the application.

I am using Gilles Kohl to obtain the lat & long of the location (in this case county), then find records within specified distance based on that and entered tolerance on Access form.


Private Sub cmdFindCounty_Click()
On Error GoTo Err_cmdFindCounty_Click

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim oApp As Object
Dim oMap As Object
Dim strPushpinSetName As String
Dim strSQL As String
Dim strNote As String
Dim dblLat As Double
Dim dblLon As Double
Dim dbtolerance As Double
Dim dbLatNorth As Double
Dim dbLatSouth As Double
Dim dbLongEast As Double
Dim dbLongWest As Double
Dim dbDistance As Double
Dim shapeCreated As Boolean
Dim oDS As MapPoint.DataSet
Dim oDataSet As MapPoint.DataSet
Dim oRecordset As MapPoint.Recordset
Dim oLoc As MapPoint.Location
Dim oLocTst As MapPoint.Location
Dim oPin As MapPoint.Pushpin
Dim oPush1 As MapPoint.Pushpin


Set oApp = CreateObject("MapPoint.Application")
Set oMap = oApp.NewMap
dbtolerance = Me.txtTolerance

Set oLoc = oMap.Find(txtCounty & " County, " & cboState)
If Not oLoc Is Nothing Then
Set oPush1 = oMap.AddPushpin(oLoc, UCase(txtCounty) & " County, " & cboState)
oPush1.BalloonState = geoDisplayBalloon
oPush1.Symbol = 25 'Change pushpin symbol to red dot
oPush1.Highlight = True
oPush1.Note = "Center of Search Radius."
oPush1.Goto
End If

Call CalcPos(oMap, oLoc, dblLat, dblLon) 'Return lat and long by reference

'A degree of latitude is always ~ 69miles. Using 1/68 of a degree
'per mile ensures that the rectangle is slightly larger than the radius desired.
dbLatNorth = dblLat + (CDbl(dbtolerance) / CDbl(6)
dbLatSouth = dblLat - (CDbl(dbtolerance) / CDbl(6)

'A degree of longitude varys based on nearness to the equator or the poles.
'Used ~55 miles . Using 1/55 of a degree in equations.
dbLongEast = dblLon + (CDbl(dbtolerance) / CDbl(55))
dbLongWest = dblLon - (CDbl(dbtolerance) / CDbl(55))

Set rs = New ADODB.Recordset
'Create recordset based on latitude and longitude tolerances.
Set rs.ActiveConnection = CurrentProject.Connection
strSQL = "SELECT tblBitRecord.WellOperator, tblBitRecord.WellName, "
strSQL = strSQL & "tblBitRecord.SpudDate, tblBitRecord.Latitude, tblBitRecord.Longitude "
strSQL = strSQL & "FROM tblBitRecord "
strSQL = strSQL & "WHERE tblBitRecord.Longitude BETWEEN " & dbLongWest & " AND " & dbLongEast
strSQL = strSQL & "AND tblBitRecord.Latitude BETWEEN " & dbLatSouth & " AND " & dbLatNorth
With rs
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strSQL
End With

'Create a new pushpin set
strPushpinSetName = "Locations within " & CStr(Me.txtTolerance) & _
" miles of selected county"
oMap.DataSets.AddPushpinSet strPushpinSetName
Set oDS = oMap.DataSets(strPushpinSetName)
oDS.Select

Do While Not rs.EOF
Set oLocTst = oMap.GetLocation(rs!latitude, rs!Longitude)
'This adds the pushpins to the "My Pushpins" set.
Set oPin = oMap.AddPushpin(oLocTst, rs!WellName)
'strNote = rs!WellOperator & " - " & rs!WellName
dbDistance = oMap.Distance(oLoc, oLocTst)
strNote = rs!WellOperator & " - " & rs!WellName & ": " & Format(dbDistance, "##,##0.00") & " mi"
'objPin.BalloonState = geoDisplayName
oPin.BalloonState = geoDisplayBalloon
oPin.Note = strNote
'Cut and paste the pushpin into the new set.
oPin.Cut
oDS.Paste
rs.MoveNext
Loop

On Error Resume Next
oDS.Symbol = 25
oDS.DisplayDataMap DataMapType:=geoDataMapTypePushpin
oDS.ZoomTo
oDS.Name = strPushpinSetName
Set oRecordset = oDS.QueryAllRecords
oRecordset.MoveFirst
Do Until oRecordset.EOF
Set oLocTst = oRecordset.Pushpin.Location
'Use the distance method to find the distance between a pushpin and the reference point.
dbDistance = oMap.Distance(oLoc, oLocTst)
'If the pushpin is outside of the desired distance, change the color of the symbol.
If dbDistance > dbtolerance Then
oRecordset.Pushpin.Symbol = 30 'green circle
Else
oRecordset.Pushpin.Symbol = 25 ' red circle
End If
oRecordset.MoveNext
Loop

oMap.Altitude = dbtolerance * 7.5

oMap.Shapes.AddShape geoShapeRadius, oLoc, dbtolerance * 2, 1000 'dbTolerance * 2
oMap.Shapes.Item(oMap.Shapes.Count).Name = "Bit Record Tolerance"
oMap.Shapes.Item(oMap.Shapes.Count).Line.Weight = 2
oMap.Shapes.Item(oMap.Shapes.Count).Line.ForeColor = vbBlack
oMap.Shapes.Item(oMap.Shapes.Count).Fill.Visible = True
oMap.Shapes.Item(oMap.Shapes.Count).Fill.ForeColor = RGB(185, 187, 185)
oMap.Shapes.Item(oMap.Shapes.Count).ZOrder geoSendBehindRoads

shapeCreated = True

'Place the map into Access
oMap.CopyMap
imgClip.Visible = True
imgClip.Action = acOLEPaste

lblMapInfo.Caption = strPushpinSetName

rs.Close
Set rs = Nothing
Set cn = Nothing
Set oLoc = Nothing
Set oLocTst = Nothing
Set oPin = Nothing
Set oRecordset = Nothing
Set oDS = Nothing
Set oDataSet = Nothing
Set oMap = Nothing
Set oApp = Nothing
Me.SetFocus

Exit_cmdFindCounty_Click:
Exit Sub

Err_cmdFindCounty_Click:
MsgBox Err.Description
Resume Exit_cmdFindCounty_Click

End Sub

Hi,

I can only answer (3):

1. Maybe it help if you describe exacly what you means by 'crash'. Can you determine if something crash in your code or is it access ? If it is you code can you tell the exact line where it happens ?

I'm not myself famiilisar with VB, but some extra information is probably very usefull.

__________________
rgds, Wilfried Mestdagh
www.mestdagh.biz
MapPoint coding demo
Order MapPoint 2009 with Routing and User Tools Spreadsheet