Solved

Embedded Google Earth map/location in Access Form

Posted on 2014-12-04
32
840 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
Comment Utility
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
Comment Utility
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
Comment Utility
...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
 

Author Comment

by:yoducati
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
A working mdb sample that let's me get into your headspace is a thing of joy
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
Have a gander at this
map.mdb
0
 

Author Comment

by:yoducati
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Nick67
Comment Utility
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
Comment Utility
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
Comment Utility
Try this out
mapV1.mdb
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
ZipCode/PostalCode are optional.
Occasionally Google fubars them.

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

Expert Comment

by:Helen_Feddema
Comment Utility
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
Comment Utility
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
Comment Utility
 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Creating and Managing Databases with phpMyAdmin in cPanel.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now