Welcome to MapForums!

Register, sign in, or use Facebook Connect above to join in and participate in the forum.

When you are signed in, this message, the ads in this row, and the red-underlined link ads all go away.

Subscribe to receive our newsletter.
Subscribe Unsubscribe
Results 1 to 9 of 9

MapPoint Automation from an Access Form

This is a discussion on MapPoint Automation from an Access Form within the MapPoint Desktop Discussion forums, part of the Map Forums category; I am new to MP2K forums. I developed an Access form which employees add their daily route. The form contains ...

  1. #1
    arcturus19 is offline Junior Member White Belt
    Join Date
    Sep 2007
    Posts
    5

    MapPoint Automation from an Access Form

    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.

  2. #2
    Mattys Consulting's Avatar
    Mattys Consulting is offline Senior Member Black Belt
    Join Date
    Dec 2002
    Posts
    1,040

    Re: MapPoint Automation from an Access Form

    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

  3. #3
    arcturus19 is offline Junior Member White Belt
    Join Date
    Sep 2007
    Posts
    5

    Re: MapPoint Automation from an Access Form

    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,

  4. #4
    Wilfried is offline Senior Member Black Belt
    Join Date
    Nov 2004
    Location
    Belgium
    Posts
    2,433

    Re: MapPoint Automation from an Access Form

    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;

  5. #5
    Mattys Consulting's Avatar
    Mattys Consulting is offline Senior Member Black Belt
    Join Date
    Dec 2002
    Posts
    1,040

    Re: MapPoint Automation from an Access Form

    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

  6. #6
    arcturus19 is offline Junior Member White Belt
    Join Date
    Sep 2007
    Posts
    5

    Re: MapPoint Automation from an Access Form

    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

  7. #7
    arcturus19 is offline Junior Member White Belt
    Join Date
    Sep 2007
    Posts
    5

    Re: MapPoint Automation from an Access Form

    Anyone.....?

  8. #8
    Eric Frost's Avatar
    Eric Frost is offline Administrator Black Belt
    Join Date
    Jul 1992
    Posts
    5,094
    Blog Entries
    4

    Re: MapPoint Automation from an Access Form

    Does it work better if users include the City?
    ~ Order MapPoint MapPoint 2013 Here and Get Free Access to the MapForums Downloads ~
    ~~
    ~ Upgrade to Get Access to the MapForums Downloads ~

  9. #9
    arcturus19 is offline Junior Member White Belt
    Join Date
    Sep 2007
    Posts
    5

    Re: MapPoint Automation from an Access Form

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mappoint and Access with ActiveX On A Form
    By jpateusa in forum MapPoint Desktop Discussion
    Replies: 5
    Last Post: 05-16-2009, 06:32 PM
  2. Using mappoint automation in .NET without GUI
    By tsay in forum MapPoint Desktop Discussion
    Replies: 2
    Last Post: 06-02-2007, 04:26 AM
  3. Problem using PopUp property in Access Form
    By PMACAFEE in forum MapPoint Desktop Discussion
    Replies: 4
    Last Post: 11-12-2006, 09:45 PM
  4. MapPoint Automation with C/C++ - Part 1
    By Eric Frost in forum MP2K Magazine Articles
    Replies: 10
    Last Post: 09-15-2006, 10:01 AM
  5. summary to access form help please
    By nmilne in forum MapPoint Desktop Discussion
    Replies: 3
    Last Post: 01-15-2006, 03:22 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60