Solved

Embedded Google Earth map/location in Access Form

Posted on 2014-12-04
32
1,004 Views
Last Modified: 2014-12-05
I am in the process of building an asset management database.  I have an exiting asset form with a tabbed control and one of the tabs is "location information".  I'd like to be able to dynamically display a google map or earth image with the location info for whatever asset the user is currently viewing on this tab.  I have it working now by using the built in web control and bing maps but it seems to be unreliable, and doesn't allow me to control display properties like having the map open maximized to fill the area on my form, or eliminate the "get directions" tab.  To me it seems like its one step above simply passing a hyperlink address to internet explorer in the onclick event of a button.  I'd like to make this a little more polished than it currently is and was wondering if some other method like vba would be able to handle this.  I've looked at some of the other posts on this subject but can't seem to find exactly what Im looking for.  Even a static map image would work and honestly might be better.
0
Comment
Question by:yoducati
  • 15
  • 12
  • 4
  • +1
32 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40481629
You can display the location easily with Google *Maps* using code like this:

'Opens Google Maps with the location "pinned"
Dim strLocation As String
Dim strTemp As String
    strLocation = Me.locAddress & "+" & Me.locCity & "+" & Me.locState & "+" & Me.locZip
    strTemp = "http://maps.google.com/maps?q=" & strLocation
    Application.FollowHyperlink strTemp
End Sub

In this code, Me.locAddress,  Me.locCity,  Me.locState  and Me.locZip, ...
...are all address fields on the form.
0
 

Author Comment

by:yoducati
ID: 40481647
Ok.  Im just getting back into the development world so I'm a little rusty.  Where would I put the code?  My first guess would be in the on current event of the form but Im not sure.  Currently I have one concatenated field that is locked and simply displays the address info but won't allow the user to modify it.  Would I still use the web control as the "container" for that tab on the form?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40481662
...Oh, I see you want this to be opened in your web browser control and you want to customize the output...
I don't think there is an easy way to "customize" the view (turn off the Directions box, ...etc)
I have even tried to edit the actual address that Google Maps appends after the code string:
ex:
/@43.9445134,-79.0318074,17z/data=!4m2!3m1!1s0x89e841b121b4382f:0xdabb52d0b4762676
...but a refresh puts it all back again...
:-(

Lets see if another Expert knows more...
;-)

Jeff
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:yoducati
ID: 40481675
Can you tell me how to use the web control with google maps?  I tried it before with no luck.  Bing maps worked fine but sometimes when I open the form the map doesnt display.  If I close and reopen it pops right up so I wanted to try it with google maps instead and see if that helps.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40481690
To answer your development question.

The code would go on the Current event of the form (so changing each "Location" would update the WebBroweser control...)

...I am not sure what you mean by:
" Would I still use the web control as the "container" for that tab on the form? "
... in my scenario the web browser control would be on a simple form (along with that address fields)...
My guess is that it could be on a tab control, but you can put it on a simple form just to see if it works...
0
 

Author Comment

by:yoducati
ID: 40481705
I called in container meaning a place for the map to display.  Im not sure what the correct terminology is.  When I put the web control on any form it asks for the address and parameters.  Do I leave that blank if the code is in VBA?  I did it with the web browser control before but everything went in the web control itself not VBA.  If put it in the web control Im not sure how to provide the parameters for google maps which is what got this whole thing started.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40481725
Can you tell me how to use the web control with google maps?  I tried it before with no luck.  Bing maps worked fine but sometimes when I open the form the map doesnt display.  If I close and reopen it pops right up so I wanted to try it with google maps instead and see if that helps.

Here is a simple sample:

This may never be what you want while using a webrowser control...

...this is why I simply open it up in a Firefox/IE/Chrome. (using the code I posted
Database42.mdb
0
 

Author Comment

by:yoducati
ID: 40481747
I think this would work for now for what I want.  I think the web control feature works differently in 2010.  For me its like this, but it only works with bing maps.  I couldnt get google to work.

https://www.youtube.com/watch?v=GHoy6hsAltc
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40481827
Even a static map image would work and honestly might be better
I mean you could open Google maps, ALT-PrintScreen the map, crop and save it, and then it just becomes an image displaying routine.
The making the map images is scutwork, and the displaying is Me.Image0.picture = "SomeValidPath"

Or a dynamically taken image?
That's maybe a bigger task than I want to take on for giggles.
Open Google Maps on the fly, screenshot it, dump the clipboard to WIA and pound out an image to be displayed, and then close Google Maps.
Neat idea, and doable.  Just a bit complex, though
0
 

Author Comment

by:yoducati
ID: 40481839
Yeah that sounds too complex.  I just want to get the web control working with google maps like it does with bing maps for now, but I dont know how to specify the parameters for google.  I havent been able to find anything on it either.
0
 

Author Comment

by:yoducati
ID: 40481850
Could I do something like this but have the address change dynamically based on the current record?  This is over my head but it seems like Im close since it works (ish) with bing maps.  Next step would be getting the web control to work with google, and third step maybe somthing like this?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40481971
A working mdb sample that let's me get into your headspace is a thing of joy
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40482074
Ok,
The static map thing of the Google API is pretty straight forward.
Latitude to 4 digits, Longitude to 4 digits, and integer for zoom, bigger number, tighter view, size pixels by pixels
<html>
<body>
<img src="https://maps.googleapis.com/maps/api/staticmap?center=55.1500,-118.80008&zoom=10&size=800x800">
</body>
</html>

Open in new window


Now, it can go in a webcontrol, or we can try to capture the image out of it to a temp spot and display it
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40482090
Have a gander at this
map.mdb
0
 

Author Comment

by:yoducati
ID: 40482767
That would be perfect.  Is there a way to replace the lat/long with a regular address and have it do the same thing.  Thats exactly what I am looking to do as far as format and function but I don't have a way to track or capture the lat/long for existing stuff in the system.  As far as the database sample itself goes I have to look into how much I can post.  Its not classified or anything but I don't think I am allowed to just post it online. I hear you though.  It would make everything easier, like making a custom search form for example.
0
 

Author Comment

by:yoducati
ID: 40482781
I forgot about the pointer.  If we can add the pointer and make it work by address it will be exactly what Im looking for.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483195
The marker is likely a go
https://developers.google.com/maps/documentation/staticmaps/#Markers
In very short order, you'll know as much about the subject as I do

Maybe addresses too
https://developers.google.com/maps/documentation/staticmaps/#Addresses
The following example generates a static map for Berkeley, CA:
https://maps.googleapis.com/maps/api/staticmap?center=Berkeley,CA&zoom=14&size=400x400


How much have you played with the sample?
The textboxes on the side could be hidden, but they are live data.
Throw a requery button on and you could move, zoom and resize by changing numbers and hitting requery
0
 

Author Comment

by:yoducati
ID: 40483241
Been playing with it a little.  Been distracted by a stupid text box.  I have the address sort of working.  I havent been able to figure out how to concatenate it so that it will display correctly.  In my concatenated address field I have the street address, city, state and zip code.  The map opens and displays but it only goes to the city and state.  Now that I am thinking about it though it just occurred to me that maybe thats because of the zoom level and lack of a marker.  Let me play with it some more.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40483242
Try this out
mapV1.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483256
ZipCode/PostalCode are optional.
Occasionally Google fubars them.

Of course, ONLY a postalcode/zipcode works too!
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 40483345
See my Access Archon article on this topic:

http://www.helenfeddema.com/Files/accarch193.zip

Here are some screen shots:

 Google map on Access form
Google map distance on Access form
0
 

Author Comment

by:yoducati
ID: 40483423
Thanks for everyones input.  Nick67 got it working just like I want it to.  I was going out of my way to avoid using the standard bing/google maps interface because of all the unrelated links etc, and I didn't want the user to see the "directions" tab either.  I just felt like it made things a little muddled.  The static map is the way to go for me.  I'm going to add a button to the form to allow the user to explore the location further in google earth (hopefully) if not just google maps if they so choose.  For the form itself I wanted to have more control over the format of the display.  Which brings me to one final tweek if it can be done....is there a way to reference the display properties of the web control?  I have the map and marker working beautifully but the map shows up being "sunken" and has an empty vertical scroll bar though it isn't needed because the map size is already specified.  I've turned everything off in the form and subform properties but the map itself still shows up with the empty scroll bar and sunken both of which I would like to eliminate.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483456
 I have the map and marker working beautifully but the map shows up being "sunken" and has an empty vertical scroll bar though it isn't needed because the map size is already specified.

The disabled scrollbar you'll have to live with, short of putting a background color to the form, creating a box control and filling it with that color and covering it over.
.SpecialEffects controls appearance (sunken, chiseled, shadow etc.)
.SpecialEffects = 0 is Flat

And interestingly enough, the maps are only returned at max 640 x 640.  Perhaps you need a Google account to get bigger ones.
0
 

Author Comment

by:yoducati
ID: 40483599
Ok the formatting for the scroll bars isn't that big of a deal.  I actually tried putting a box control over it and it seems that access forces the web control to be on top even if I specifically position it behind other controls.  New problem though.  I got back from lunch and the map doesnt work anymore.  I haven't changed any of the coding.  I just get a message that the webpage cannot be displayed.  My browser is working.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483643
I got dumb originally and named the fields height and width.
Try this version.
You can test your addresses and strings in the simplest html page ever
mapV2.mdb
map.htm
0
 

Author Comment

by:yoducati
ID: 40483657
No worries.  That wasn't it.  I had already named the fields MapHeight and MapWidth.  Everything is working again.  I discovered in my test addresses that somehow I had only put one address in with the zoom, height, and width fields completed.  The other four addresses only had the address info in the table.  Corrected that and its back to working beautifully.  In the event the system can't display the map either because information was missing from the table or the address is invalid, is there a way I can display a custom message in place of the map so I don't get the stupid "internet explorer cannot display the webpage" that takes up the whole area where the map should be?
0
 

Author Comment

by:yoducati
ID: 40483711
Thanks again to everyone for sharing their ideas.  I was really trying to avoid using the interactive google maps which probably sounds counter intuitive but the static map worked out well for me.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483714
The web browser control is best researched through the Object Browser.
intellisense is always hit and miss with ActiveX controls
It has a NavigateError event
You should be able to use that

Private Sub WebBrowser0_NavigateError(ByVal pDisp As Object, URL As Variant, Frame As Variant, StatusCode As Variant, Cancel As Boolean)
Me.WebBrowser0.Visible = False
MsgBox "Your URL was rejected because it wasn't formed correctly"
End Sub

Open in new window

0
 

Author Comment

by:yoducati
ID: 40483756
I tried this:

If Me.WebBrowser0.NavigateErr Then
MsgBox "Test", vbOKOnly
End If

But I get an error saying object doesnt support this property of method.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483777
You saw what I posted?
The control throws an event that can be caught
See the sample.
Get the focus off the map
Make it visible
Navigate it
If it fails to navigate, catch the event
make the browser invisible
msgbox "badness!"
mapV3.mdb
0
 

Author Comment

by:yoducati
ID: 40483830
Sorry.  Our browser is outdated.  It didnt show the window with the snippet you posted before.  It was just the text above it.  Got it working.  Thanks again!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40483839
No problem.
It was interesting!

I am going to have to get wet with O365 and all the REST stuff that it can/must do so the research on the web end of it has been informative

Nick67

PS Nice touch ;)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question