I am new to MP2K forums.

I developed an Access form which employees add their daily route. The form contains a sub-form in data-sheet (tabular) view where users add address and other information each day. (All data is stored in a database (.mdb file) on our server.) The form also contains a calendar control where one can call up routes from previous days. The sub-form is based on a query which ties to the calendar control. I want to place a button on the form which automatically launches a separate instance of MapPoint and does the following:

1. MapPoint imports addresses from the abovementioned sub-form. I would like to use the query that the form uses but am willing to take roundabout methods like exporting the day’s addresses to a temporary file.

2. Automatically places the list of addresses in MapPoint’s route planner after searching within a prescribed territory based on Zip Codes. This is to avoid searching all of North America before mapping each waypoint. (The start and end waypoints will always be the same since that is our fleet location.)

3. Finally, MapPoint automatically optimizes the route and get directions.

While I don’t expect someone to provide the VB code for all this, I would like to know if this is possible or if I’m just wasting my time. I am somewhat versed in Access but am a novice at VB. A search of the forums has, so far, provided little help for this specific application. Can anyone steer me toward some good resources? I realize this may take some time to finally get it to work.

Thanks in advance.

Hi arcturus19,

Your idea is definately possible.

1) You'll just need to export to text and import using an instance of MapPoint
2) In order to start and end at the same point, your solution will need to
move those points in code.
3) Route.Optimize and Map.CopyDirections will work.

Mike Mattys

Thanks for the advice.

I've done much work since my last post. I actually have the waypoints optimizing correctly with my base as the start and end points. I only have one problem left to tackle.

Whenever there are no records in the the form and I launch my MapPoint app, it fails to load and the debugger pops up. This likely a simple fix but I have been searching for days and have not found anything close to this problem. It will only work as long as their are some records in the query/dataset. I believe this will be is a simple IF, THEN, ELSE statement about the recordset being empty. I'm apparently not using the correct syntax.

Any ideas?

Thanks in advance,

Hi,

Probably this is an exception. I don't know the right syntax in VBA, but should be something as try except (or catch) end;

Hi.

You are using a class named 'Form' that has events such as
OnLoad()
and
OnOpen()

You need to take advantage of this by proceeding or not
proceeding with the event dependent upon whether there
are records in the form's recordsource.

In this case, do what is neccessary for Access
in the OnLoad event and determine whether you should
create an instance of MapPoint like this:

Private Sub OnOpen()
If Me.Form.RecordsetClone.RecordCount>0 Then
'Load MapPoint and process records
End If
End Sub



Mike Mattys

Hi Mike:

Thanks again. That worked beautifully. I also included some data validation teqniques in the text box so one cannot leave it blank.

Yes, I am using the OnLoad event for most of my code for the for with MapPoint control. I only have a couple of hurdles to get over and I'm done, I think.

My question now is what is the best method for finding addresses in a certain geographic area? For expediancy, the users will only be typing in addresses in the route form, without city, state and zip. (West Los Angeles is actually broken into small cities so just using Los Angeles does not work well.) Currently, I am putting a string of towns like Venice and Marina Del Rey separated by comas for the default value of the City field of my table aong with CA for the default in the State field. (These are hidden from the user.)

While this works about 90% of the time, some waypoints only come up with the street and no address, particularly in Venice, CA. I have to delete the waypoint and enter the address and city in MapPoint to locate the correct address. This is odd because the city I enter is among those included in my string of cities in the imported records. Maybe using a string of zip codes is better?

Perhaps I'll get better results if MapPoint searches within zip codes or cities using VBA code instead of putting a bunch of these in my table.

Let me know what you think is the best way to increase the accuracy of my results.

Thanks in advance,

acrturus19

Anyone.....?

Does it work better if users include the City?

Anytime I include the correct city when I enter it in the route planner, It finds it. I suppose if I one entered the city in the form, it would find it as well. The point is I don't want users to have to do this. I'm trying to have the program search among a group of cities, but I don't think the code is working. Maybe Someone can look at my code and see if I haven't done that part correctly. I likely need to add more code pertaining to record matching.

If Me.Form.RecordsetClone.RecordCount > 0 Then
'Load MapPoint and process records
Dim objApp As MapPoint.Application
Dim objMap As MapPoint.Map
Dim objRoute As MapPoint.Route
Dim objFR As MapPoint.FindResults
Set rs = Me.Recordset
Set objApp = CreateObject("mappoint.application")
Set objMap = objApp.ActiveMap
Set objRoute = objMap.ActiveRoute
Dim StartPoint As MapPoint.Waypoint
Set objMap = MPC.NewMap(geoMapNorthAmerica)
MPC.Toolbars.Item("Navigation").Visible = True
MPC.Toolbars.Item("Standard").Visible = True
MPC.PaneState = MapPoint.GeoPaneState.geoPaneRoutePlanner


'Set the route start point
Index = 1
Set oloc = objMap.ActiveRoute.Waypoints.Add(objMap.FindAddres sResults("Headquarters Address Goes Here", "Los Angeles", "CA", "90031")(1))
With objRoute.Waypoints
'Map each waypoint
Do Until rs.EOF
Index = 2
Set oLocs = objMap.ActiveRoute.Waypoints.Add(objMap.FindAddres sResults(Me.job_address, "Los Angeles, Marina Del Rey, Venice, Playa Del Rey, Santa Monica, Culver City, Pacific Palisades, Beverly Hills, West Hollywood", , "CA", , geoCountryUnitedStates)(1))

rs.MoveNext

Loop

End With
'Set the route end point
Set oloc = objMap.ActiveRoute.Waypoints.Add(objMap.FindAddres sResults("Headquarters Address Goes Here", "Los Angeles", "CA", "90031")(1))
objMap.ActiveRoute.Waypoints.Optimize
objMap.ActiveRoute.Calculate

Else
MsgBox "You must enter an address and job code."
DoCmd.Close acForm, "Map Today's Route"
End If

End Sub