Multi Pin Google Maps - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Multi Pin Google Maps

Thomas Corbett

4 years ago

Hi, I am having trouble with a looping statement. I have a command button that when depressed will open google maps and plug in the street address from my form. Now I would like to do this for each quarter of my work. I created a form for Q-1 and have installed my command button. When depressed, unfortunately, the loop opens a new page each time rather than placing the next address in the record set. Here is my code. Any help would be appriciated:

Private Sub Command21_Click()

'Skip to NextRecord

Dim db As Database
Dim rst As DAO.Recordset

Set db = CurrentDb
    Set rst = db.OpenRecordset("Mapping Q-1")
    Do Until rst.EOF
        rst.MoveNext

'Send the forms address to Google Maps

Dim strAddress As String
Dim strLink As String

    strStreet_Address = Me.Street_Address & """" & Me.City_County & ", " & Me.State & " " & Me.Zip_Code
    strLink = "http://maps.google.com/maps?hl=en&q="
    Application.FollowHyperlink strLink & strStreet_Address

'Loop

    Loop
    rst.Close
    Set rst = Nothing
    Set db = Nothing

End Sub

	Kevin Robertson @Reply 4 years ago
A couple of points: You have dimmed a variable called strAddress and later on in your code you use strStreet_Address. Also, you are not getting the elements of the address from the recordset. strAddress = rst!Street_Address & " " & rst!City_County & ", " & rst!State & " " & rst!Zip_Code You will also need some error handling if a record is missing an address

	Thomas Corbett OP @Reply 4 years ago
Hi Kevin, the code works fine. My problem is I am looping and opening a new page each time it loops to the next record set.

	Richard Rost @Reply 4 years ago
What exactly are you trying to accomplish? You want to put multiple pins on the same map? Because, yes, the way you have it, you're going to open a separate browser window for each iteration of your recordset loop.

	Thomas Corbett OP @Reply 4 years ago
Hi professor, yes, multiple pins in a single map. Thank you!!!

	Thomas Corbett OP @Reply 4 years ago
Professor, is there a way to open Google Maps once and insert all the address from the 45 records? Thanks Tom -

Adam Schwanz @Reply

4 years ago

I just made this up, seems to work

Private Sub Command2_Click()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from CustomerT Where CustomerID<6")

    Dim PrefixLink As String
    Dim SuffixLink As String
    PrefixLink = "https://www.google.com/maps/dir/"
        While Not rs.EOF
            If rs!City = "" Or rs!State = "" Or rs!Zip = "" Then
            'Incomplete Record
            Else
                If SuffixLink <> "" Then
                    SuffixLink = SuffixLink & rs!City & ", " & rs!State & " " & rs!Zip & "/"
                Else
                    SuffixLink = rs!City & ", " & rs!State & " " & rs!Zip & "/"
                End If
            End If
        rs.MoveNext
        Wend
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Application.FollowHyperlink PrefixLink & SuffixLink
End Sub

	Thomas Corbett OP @Reply 4 years ago
Thank you Adam, Ill try this tonight!!! Tom.

Richard Rost @Reply

4 years ago

Excellent code, Adam. I tweaked it a little bit. If you add //@ to the end of the string, it doesn't make driving directions. And I added the actual address field.

Dim rs As Recordset, S As String



Set rs = CurrentDb.OpenRecordset("SELECT * FROM CustomerT WHERE Not IsNull(Address)")

S = ""

    While Not rs.EOF

        S = S & rs!Address & " " & rs!City & " " & rs!State & " " & rs!Zip & "/"

    rs.MoveNext

    Wend

rs.Close

Set rs = Nothing



S = S & "//@"

Application.FollowHyperlink "https://www.google.com/maps/dir/" & S

	Richard Rost @Reply 4 years ago
Think I'll throw this into a future lesson. :)

	Thomas Corbett OP @Reply 4 years ago
Thank you Rich!!! This is something that I believe is commonly used if not for jump through records for use in a map then simply for going through a series of records for other uses. Thank You Tom -

	Richard Rost @Reply 4 years ago
You're very welcome. It's Adam's code. I just modified it a bit. :)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen