View Full Version : Need VB Help Using Shapes or Area Identification

06-25-2012, 10:45 AM
I have successfully developed applications sending Zip and City data to MapPoint and returning map-sorted data from MapPoint. I have not yet written code utilizing lines or shapes.

I have an application where 4,000 addresses from one city region must be assigned to arbitrary A, B, C, D, E, and F areas. The area boundaries are named streets.

I would like help in furnishing MapPoint with each area's borders (or other geopoints if necessary), send it all 4,000 addresses and (possibly with an if/then condition for when an address fits inside the borders) have MapPoint's help in coding the addresses to each lettered area.

I can probably get the codes back into Access ok.

I would appreciate any suggestions.
Thanks much.

Eric Frost
06-25-2012, 11:11 AM
Hi joent,

Hi and Welcome to the Forums!

This can definitely be done, just want to give you a quick overview of the process first, then we can work on the details.

1. Create shapes for the areas in MapPoint. You can do this programmatically, but I suspect it's going to be easier to just trace them into MapPoint using the drawing tools. Then use Richard Marsden's Shape Name Utility ( Shape Name Utility - MapPoint Articles - MP2K Magazine (http://www.mp2kmag.com/a152--shape.name.mappoint.html) ) to assign names to the objects.

2. Import all of your addresses, creating a Pushpin for each of them in MapPoint if you have not already. To make the coding easy, you can put these all in one dataset, but it's not necessary.

3. Write code to do something like this

-- Loop over all the shapes
-- Use QueryShape on the Dataset object which has your Pushpins
-- Loop over the Recordset and maybe just put the Shape name into the Note field for each Pushpin

4. Export the Dataset including the Note field, you might need some custom code for this.

I could probably get this code written with you on a consulting/training basis via LiveMeeting or WebEx in about an hour, or if you get stuck and need help feel free to post here and use the forum for free of course :-)

hope this helps,

06-25-2012, 03:22 PM
Thanks so much, Eric!

Thanks for the offer to help directly through LiveMeeting. I will try to get it going first until getting stuck.

I will get going on this right away and keep posting her on how it's going. I don't mind sharing it with everyone here hoping it will help them.

The easiest part for me will be getting it back into Access, I think.


06-26-2012, 01:43 AM
Thanks for pointing the way, Eric. I was only slowed down figuring out the Shapes Index. I installed the Tools ShapeName Addin first and drew the shape.
Here is the code for everyone to review. It is tested and works well. I only used one shape and low data numbers to keep problems down.
I will expand it later. Please let me know what you think!

Public Sub AssignMPShapeDataToAccessCells()
' For MapPoint Forums:
' This Access VBA procedure opens a MapPoint map already having a saved shape in it.
' The Access Cell name is "A" and the MapPoint Shape Name is "A".

' The idea is: You wish to use MapPoint to tell you what addresses (from a large pool of addresses) fit inside a MapPoint Shape.
' When the results are determined, you wish to use only data from inside the shape to post results back to Access.

' A MapPoint dataset is used to import records representing address data both outside and inside the shape.

' An Access DAO recordset (partially based on the dataset) is used to limit data to addresses inside the shape.
' The DAO recordset also provides numbered waypoints to MapPoint inside the MapPoint map's shape.

' Lastly, waypoints are used to match waypoint names to an Access table's names, posting
' shape (cell) name and waypoint order back to the Access table's Cell and CellOrder fields.
' We save the MapPoint map and Access can then deliver MapPoint Shape-related reports.

' Code starts here:
' Set overall application variable and its properties
Dim oApp As New MapPoint.Application
' Make map visible and give control to user
oApp.Visible = True
oApp.UserControl = True
Set oApp = GetObject(, "MapPoint.Application")
oApp.Parent.PaneState = geoPaneRoutePlanner
' Ensure working in miles
oApp.Units = geoMiles

'Open a sample map
oApp.OpenMap "c:\City\Project1\Maps\ShapeA.ptm"

' Set variable to active map
Dim oMap As MapPoint.Map
Set oMap = oApp.ActiveMap

' Set MapPoint dataset to import all data outside and inside shape

Dim dsPars As MapPoint.DataSet
Dim dsPath As String
' Fields in table tAShapeTestBeforeMP are: Cell, Name, Address, City, State, Zip
dsPath = "c:\City\Project1\City0301.mdb!tAShapeTestBeforeMP"
Set dsPars = oMap.DataSets.ImportData(dsPath)

' Set recordset to work only with pins inside shape
Dim rsPars As MapPoint.Recordset
Dim oShape As MapPoint.Shape
Set oShape = oMap.Shapes(1)
' This limits recordset to area inside the shape
' when waypoints are added below
Set rsPars = dsPars.QueryShape(oShape)

' Add addresses as waypoints
Dim oWps As MapPoint.Waypoints
Set oWps = oMap.ActiveRoute.Waypoints

' Add numbers to pushpins inside shape
Do While Not rsPars.EOF
If (rsPars.IsMatched) Then
oWps.Add rsPars.Pushpin
End If

' Post MapPoint-found shape waypoints back into a different access table's Cell and CellOrder fields
Dim i As Long
Dim rsAfter As DAO.Recordset
Dim ss As String
Dim sCell As String

sCell = "A"
For i = 1 To oWps.Count
ss = "Select Cell, CellOrder, Name From tAShapeTestAfterMP "
ss = ss & "WHERE Name = '" & oMap.ActiveRoute.Waypoints.Item(i).Name & "';"
Set rsAfter = CurrentDb.OpenRecordset(ss, dbOpenDynaset)
With rsAfter
!Cell = sCell
!CellOrder = i
End With
Next i

' Cleanup
Set dsPars = Nothing
Set rsPars = Nothing
Set rsAfter = Nothing

' Save map for one Cell (Shape)
oApp.ActiveMap.SaveAs ("c:\City\Project1\Maps\Cell" & sCell & "AfterMP.ptm")
oApp.ActiveMap.Saved = True
Set oApp = Nothing

MsgBox "Finished processing MapPoint shape assignments for Access!"

End Sub

Eric Frost
06-27-2012, 10:27 AM
Wow, this is awesome, thanks for following up!

I'm curious if it's actually necessary to use WayPoints as you are not doing a route? I think the WayPoints might be unnecessary?


06-29-2012, 10:59 PM
Hi Eric,

I have always thought Waypoints are what provide numbers on the map - I don't know! I haven't tried it without Waypoints.
I especially use the collection at the end of the Sub to get the sorted number data I want back into Access by matching the Waypoint Name to the Access Name.
Maybe they do provide a route without directions, because I see the names down the side of the map as if they were in the route planner.
I tied the recordset for data inside the shape to the QueryShape object and then to the Waypoints to limit what I got for Access to the data inside the shape.
The map shows numbers inside the shape, but all pushpins outside the shape have no numbers.
It does work perfectly for what I want!

I did appreciate very much your suggestions about the MapPoint ShapeName add-in and the QueryShape object. Could not have done it without them.

It feels so good that you like my code, Eric!

Thanks much again. I'd like to keep in touch somehow!


09-02-2012, 05:45 PM
you can also code the shape name, without needing the utility, I'm not on My Works Laptop but Ill try and post the code. when selected use the Name Property