UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Google Maps And Multiple Markers, Office 2007    
 
   
highwayrob
post Sep 9 2011, 11:08 AM
Post #1

UtterAccess Addict
Posts: 103
From: North Florida, USA



Good afternoon. I have been able to integrate some code into VB to allow the display of my customer site using a call to google maps via a URL. I modified the code to display multiple sites by using markers. I create these long strings looping thru an access table and appending a city and state on each new marker. For the sake of this forum question, I am showing the resultant string that is misbehaving. Looping thru the table and building the strings is pretty straight forward code and I would be happy to forward that code to anyone who needs it.
lthough the two following examples use the same cities and states, I get two different resultant maps. The first example places two extra markers on the map (south west Arkansas and Washing DC) and does not color code the markers correctly.
The second example does proper color coding and does not show the extra two markers. If you look closely, you can see some darker shadows on the markers on the second google map example, this indicates overlapping markers. This is the inspiration to use tiny markers to help distinguish different sites that are close together.
Any ideas why the first example shows the extra markers and does not color code?
Private Sub ExpGoogleMap()
Dim S1, S2 As String
S1 = "c:\Program Files\Internet Explorer\Iexplore.exe "
S1 = S1 & "http://maps.googleapis.com/maps/api/staticmap?center=35.535833,-85.469444&zoom=5&size=800x800&markers=size:tiny%7C=color:yellow%7CMiami%20Gardens,FL&markers=size:tiny%7C=color:red%7CRobertsdale,AL&markers=size:tiny%7C=color:red%7CRiverview,FL&markers=size:tiny%7C=color:red%7CGulf%20Hammock,FL&markers=size:tiny%7C=color:red%7CBeaufort,NC&markers=size:tiny%7C=color:green%7CCastalia,NC&markers=size:tiny%7C=color:green%7CPendleton,SC&markers=size:tiny%7C=color:green%7CSaint%20Johns,FL&markers=size:tiny%7C=color:red%7CNew%20London,CT&markers=size:tiny%7C=color:blue%7CRichmond,VA&markers=size:tiny%7C=color:blue%7CJasper,FL&markers=size:tiny%7C=color:green%7CCanton,MS&markers=size:tiny%7C=color:red%7CPembroke%20Park,FL&markers=size:tiny%7C=color:blue%7COxford,NC&markers=size:tiny%7C=color:red%7CBrowns%20Summit,NC&markers=size:tiny%7C=color:green%7CRiverview,FL&markers=size:tiny%7C=color:red%7COneonta,AL&markers=size:tiny%7C=color:blue%7CDaytona,FL&sensor=false"
Call Shell(S1, vbNormalFocus)
S2 = "c:\Program Files\Internet Explorer\Iexplore.exe "
S2 = S2 & "http://maps.googleapis.com/maps/api/staticmap?center=35.535833,-85.469444&zoom=5&size=800x800&markers=color:yellow%7Clabel:T%7CMiami%20Gardens,FL&markers=color:red%7Clabel:F%7CRobertsdale,AL&markers=color:red%7Clabel:F%7CRiverview,FL&markers=color:red%7Clabel:F%7CGulf%20Hammock,FL&markers=color:red%7Clabel:F%7CBeaufort,NC&markers=color:green%7Clabel:%7CCastalia,NC&markers=color:green%7Clabel:%7CPendleton,SC&markers=color:green%7Clabel:i%7CSaint%20Johns,FL&markers=color:red%7Clabel:F%7CNew%20London,CT&markers=color:blue%7Clabel:H%7CRichmond,VA&markers=color:blue%7Clabel:H%7CJasper,FL&markers=color:green%7Clabel:3%7CCanton,MS&markers=color:red%7Clabel:F%7CPembroke%20Park,FL&markers=color:blue%7Clabel:H%7COxford,NC&markers=color:red%7Clabel:F%7CBrowns%20Summit,NC&markers=color:green%7Clabel:E%7CRiverview,FL&markers=color:red%7Clabel:F%7COneonta,AL&markers=color:blue%7Clabel:H%7CDaytona,FL&sensor=false"
Call Shell(S2, vbNormalFocus)
End Sub
Go to the top of the page
 
+
DanielPineault
post Sep 9 2011, 09:10 PM
Post #2

UtterAccess VIP
Posts: 2,842



I could be wrong, but I believe your issue resides with the =size:tiny%7C . When I remove those from your URL, it displays correctly.
Go to the top of the page
 
+
highwayrob
post Sep 12 2011, 10:43 AM
Post #3

UtterAccess Addict
Posts: 103
From: North Florida, USA



With some help from the Google Maps Forum...taking out the '=' in "=color" fixed the issue. Works great now!
Go to the top of the page
 
+
highwayrob
post Oct 20 2011, 01:56 PM
Post #4

UtterAccess Addict
Posts: 103
From: North Florida, USA



For anyone interested......Here is the code where I loop thru a data file and create and add in my markers. I crearte two different maps, one with small markers and the other with larger markers (those over 50 years old). I also check what states are in the list of cities so I can size the map a bit. Then I Open Exporer and submit to Google. Enjoy!
tr = 0
MarkerStr1 = ""
MarkerStr2 = ""
SEastOnly = True
While (Not rst.EOF)
With rst

Lbl = "A"
MColor = "green"

MarkerStr1 = MarkerStr1 & "&markers=color:" & MColor & "%7Clabel:" & Lbl & "%7C" & rst!City & "," & rst!StateCode
MarkerStr2 = MarkerStr2 & "&markers=size:tiny%7Ccolor:" & MColor & "%7C" & rst!City & "," & rst!StateCode

ctr = ctr + 1
End With
If (rst!StateCode <> "FL") And (rst!StateCode <> "AL") And (rst!StateCode <> "NC") And (rst!StateCode <> "SC") And (rst!StateCode <> "VA") And (rst!StateCode <> "GA") Then
SEastOnly = False
End If
rst.MoveNext
Wend
rst.Close

CenterAndSize = "center=35.535833,-85.469444&zoom=5&size=640x640&scale=2"
If (SEastOnly = True) Then
CenterAndSize = "center=31.035833,-82.469444&zoom=6&size=640x640&scale=2"
End If

CmdString1 = "c:\Program Files\Internet Explorer\Iexplore.exe http://maps.googleapis.com/maps/api/staticmap?" & CenterAndSize
CmdString2 = "c:\Program Files\Internet Explorer\Iexplore.exe http://maps.googleapis.com/maps/api/staticmap?" & CenterAndSize

CmdString = CmdString1 & MarkerStr1 & "&sensor=false"
Call Shell(CmdString, vbNormalFocus)
CmdString = CmdString2 & MarkerStr2 & "&sensor=false"
Call Shell(CmdString, vbNormalFocus
Go to the top of the page
 
+
hudson426
post Oct 21 2011, 03:39 PM
Post #5

New Member
Posts: 1



Rob
This is amazing but I am struggling to replicate.
You have a private sub called ExpGoogleMap(), is this in a module or on the on click event of a button?
When I try to replicate your work, I get an error:-
Run-Time Error '424':
Object required
When I debug it,
While (Not rst.EOF)
Is highlighted. What does this mean?
Can you walk us through where the code should be placed? Post the entire code?
I've been on this for a few hours and I'm stuck in the mud!
Ounderstand what the code is doing. I understand that you have a table called rst and in that table you have two fields:-
City --> StateCode
Robertsdale --> AL
Miami Gardens --> FL
Riverview --> FL
Are there any other fields in your rst table?
Cheers
Hudson
Go to the top of the page
 
+
highwayrob
post Oct 24 2011, 11:38 AM
Post #6

UtterAccess Addict
Posts: 103
From: North Florida, USA



EXPGoogleMap was a test routine used earlier in development.
did not post the compelte routine as it referred to my tables and I thought it had stuff in it that would be confusing. Here is the complete routine. I hope it helps you.....
SCList is a Listbox where user either specifies a specific record to Map or if they do not select a record, it loops thru and maps them all. I also take action to not try to map addresses with 'NA' in the street address.
The Runtime 424 error may be because you need to reference one of the Microsoft Objects. I have the following references in my VB code under Tools\References: Microsoft Access 12.0 Object Library, OLE Automation, Microsoft DAO 3.6 object library, Microsoft ActiveX Data objects or Microsoft Office 12.0 Object Library.
Hope this helps. :=)
Private Sub GoogleMapBttn_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim strSqlSiteID, MarkerStr1, MarkerStr2, MColor, Lbl, CmdString1, CmdString2, CenterAndSize As String
Dim ctr As Integer
Dim SEastOnly As Boolean

If SCList.ListIndex >= 0 Then
strSqlSiteID = "SELECT ServiceCallMaster.ServiceCallID, SiteMaster.Address1, SiteMaster.City, SiteMaster.Zipcode, States.StateCode " & _
"FROM States RIGHT JOIN (SiteMaster RIGHT JOIN ServiceCallMaster ON SiteMaster.SiteID = ServiceCallMaster.SiteID) ON States.StateID = SiteMaster.State " & _
"WHERE (((ServiceCallMaster.ServiceCallID)={guid " & Me.SCList & "}));"
rst.Open strSqlSiteID, CurrentProject.Connection, adOpenStatic, adLockOptimistic
If Not rst.EOF Then
With rst
'address1 city state
If Len(!City) > 0 And Len(!StateCode) > 0 And Not IsNull(!City) And Not IsNull(!StateCode) Then
If (UCase(!Address1) = "NA") Or (UCase(!Address1) = "N\A") Or (UCase(!Address1) = "N/A") Then
CmdString = "c:\Program Files\Internet Explorer\Iexplore.exe http://maps.google.com/maps?q=" & IIf(!City <> "", !City & "+", "") & IIf(!StateCode <> "", !StateCode & "+", "") & IIf(!ZipCode <> "", !ZipCode & "+", "") & IIf("USA" <> "", "USA", "")
Else
CmdString = "c:\Program Files\Internet Explorer\Iexplore.exe http://maps.google.com/maps?q=" & IIf(!Address1 <> "", !Address1 & "+", "") & IIf(!City <> "", !City & "+", "") & IIf(!StateCode <> "", !StateCode & "+", "") & IIf(!ZipCode <> "", !ZipCode & "+", "") & IIf("USA" <> "", "USA", "")
End If

Call Shell(CmdString, vbNormalFocus)
End If
End With
End If
rst.Close
Else
strSqlSiteID = "SELECT ServiceCallMaster.ServiceCallNbr, SiteMaster.SiteName, SiteMaster.BeaconType, SiteMaster.City, States.StateCode, ServiceCallJobStatusMaster.ConsiderOpen " & _
"FROM States RIGHT JOIN (ServiceCallJobStatusMaster RIGHT JOIN (SiteMaster RIGHT JOIN ServiceCallMaster ON SiteMaster.SiteID = ServiceCallMaster.SiteID) ON ServiceCallJobStatusMaster.JobStatusID = ServiceCallMaster.JobStatusID) ON States.StateID = SiteMaster.State " & _
"WHERE (((ServiceCallJobStatusMaster.ConsiderOpen) = True))ORDER BY ServiceCallMaster.ServiceCallNbr Desc;"
rst.Open strSqlSiteID, CurrentProject.Connection, adOpenStatic, adLockOptimistic


ctr = 0
MarkerStr1 = ""
MarkerStr2 = ""
SEastOnly = True
While (Not rst.EOF)
With rst

Lbl = Left(!BeaconType, 1)
MColor = "green"
If Left(!BeaconType, 1) = "F" Then MColor = "red"
If Left(!BeaconType, 1) = "H" Then MColor = "blue"
If Left(!BeaconType, 1) = "T" Then MColor = "yellow"

MarkerStr1 = MarkerStr1 & "&markers=color:" & MColor & "%7Clabel:" & Lbl & "%7C" & rst!City & "," & rst!StateCode
MarkerStr2 = MarkerStr2 & "&markers=size:tiny%7Ccolor:" & MColor & "%7C" & rst!City & "," & rst!StateCode

ctr = ctr + 1
End With
If (rst!StateCode <> "FL") And (rst!StateCode <> "AL") And (rst!StateCode <> "NC") And (rst!StateCode <> "SC") And (rst!StateCode <> "VA") And (rst!StateCode <> "GA") Then
SEastOnly = False
End If
rst.MoveNext
Wend
rst.Close

CenterAndSize = "center=35.535833,-85.469444&zoom=5&size=640x640&scale=2"
If (SEastOnly = True) Then
CenterAndSize = "center=31.035833,-82.469444&zoom=6&size=640x640&scale=2"
End If

CmdString1 = "c:\Program Files\Internet Explorer\Iexplore.exe http://maps.googleapis.com/maps/api/staticmap?" & CenterAndSize
CmdString2 = "c:\Program Files\Internet Explorer\Iexplore.exe http://maps.googleapis.com/maps/api/staticmap?" & CenterAndSize

CmdString = CmdString1 & MarkerStr1 & "&sensor=false"
Call Shell(CmdString, vbNormalFocus)
CmdString = CmdString2 & MarkerStr2 & "&sensor=false"
Call Shell(CmdString, vbNormalFocus)
End If
Set rst = Nothing
End Sub
Go to the top of the page
 
+
KevinF
post Jan 28 2014, 11:01 AM
Post #7

UtterAccess Member
Posts: 34
From: Pittsburgh PA



I copied your code and used it to show populations within a ZIP code. Thank you!
That I have so far works pretty well if I use a static map and I want the push pin style of marker.
It's also limited by the length of the marker string. I had to remove city and state from the string if I wanted 30-35 markers on the map.
What parameter do I adjust to make different size or shape markers or add values to them? I want to be able to display a count in the marker and vary the marker size if I am summarizing on ZIP code. Denser populations could have a larger marker, for example.
Have you tried adding more than 30 markers to a map?
If we are limited to 30 markers per shell call, is there a way to overlay the maps so I can show hundreds of markers on the final result?
Thanks for your help.
Go to the top of the page
 
+
theDBguy
post Jan 28 2014, 12:04 PM
Post #8

Access Wiki and Forums Moderator
Posts: 58,549
From: SoCal, USA



Hi KevinF,
efore I make any comment, I just want to clarify something. Are you displaying the map on a website?
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 04:47 AM

Tag cloud: