Hi all.

I am trying to write a simple VB script that is executed from Access to calculate the driving distance from one zip code to another and write the distance back to a table. I have the base script (see below) working, but I am running into a problem that when a good route can not be calculate by MapPoint the script bombs.

I am looking for a away to write a default number (i.e. 999999) as the distance when a route can not be calculated so that the script does not bomb each time.

Any ideas (and examples) would greatly be appreciated.

Thanks

Rolf


MapPoint Error:

"Unable to get directions from 71, East Lyme, CT 06333 to 330th St, White Hall, AR 71602. One of the areas may not contain necessary connectors such as ferry routes or main roads. Move one or both of the stops, and try again."

Script:

Private Sub Command0_Click()

Dim objApp As New MapPoint.Application
Dim objMap As MapPoint.Map
Dim objRoute As MapPoint.Route
Dim strfromcity As String
Dim strfromstate As String
Dim strtocity As String
Dim strtoState As String
Dim strfromlocation As String
Dim strtolocation As String
Dim strfromzip As String
Dim strtozip As String
Dim fromLong As Double
Dim fromLat As Double
Dim toLong As Double
Dim toLat As Double
Dim CalcTime As Date


Dim dbs As Database
Dim rstmilage As DAO.Recordset
Dim n As Double

'Set up the application
Set objMap = objApp.ActiveMap
Set objRoute = objMap.ActiveRoute
objApp.Visible = False
objApp.UserControl = True

'Open Route Stops Table and interates through Table
Set dbs = CurrentDb
Set rstmilage = dbs.OpenRecordset("tblMilage", dbOpenTable)

With rstmilage
.MoveFirst
Do While Not rstmilage.EOF

strfromzip = Nz(![fromzip])
strtozip = Nz(![tozip])

'Add route stops and calculate the route
objRoute.Waypoints.Add objMap.FindResults(strfromzip).Item(1)
objRoute.Waypoints.Add objMap.FindResults(strtozip).Item(1)

CalcTime = Now()

If objRoute.Waypoints.Count > 1 Then

objRoute.Calculate

m = objRoute.Directions.Count

rstmilage.Edit
rstmilage("MPZipDist") = objRoute.Directions.Item(m).ElapsedDistance
rstmilage("Create_Time") = CalcTime


objRoute.Clear

rstmilage.Update
rstmilage.MoveNext
objRoute.Clear

Else
rstmilage.Edit
rstmilage("MPZipDist") = "999999"
rstmilage("Create_Time") = CalcTime

objRoute.Clear

rstmilage.Update
rstmilage.MoveNext
objRoute.Clear

End If

Loop
rstmilage.Close
End With



objApp.Quit

MsgBox "All Done"


End Sub