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.

Although 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


This post has been edited by highwayrob: Sep 9 2011, 11:14 AM

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.

With some help from the Google Maps Forum...taking out the '=' in "=color" fixed the issue. Works great now!

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!

ctr = 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

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!

I understand 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

EXPGoogleMap was a test routine used earlier in development.

I 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