Removing Google Maps Feature from a Form

[Deggsy]

Hi all - excuse me if this has been answered elsewhere (and thanks if you can point me to where it is).



I have a database form (Access 2013) designed to book employees onto courses. The course venue options appear on a drop down list, and once one is selected, there is a command to bring up a Google Maps object based on the post codes that are read from the respective employee and the venue tables.

The Google Maps feature is no longer desired (it takes too long to load it, for one thing), and I have been asked to remove it or turn it off, but am at a lost as to where to start, without causing errors. I am a novice at this sort of thing, so any advice would be appreciated :-)

[orange]

Readers will need to see/know the Access programming (code) that is used for

there is a command to bring up a Google Maps object based on the post codes that are read from the respective employee and the venue tables.

.

Bypassing the routine is probably not too complicated, but without seeing the actual "program code/macro" readers can only guess.

Good luck.

[Deggsy]

Thanks for the quick reply, orange - hoping that this is what you mean, apologies if it isn't:

(Deleted - see subsequent post for the code)

[ItsMe]

Does it open a new browser window or does it display the map within the form? If the Command Button does not have any other function and the map opens in a Browser Window, you could just delete the command button and comment out the VBA associated with the Click Event.

[Deggsy]

Does it open a new browser window or does it display the map within the form? If the Command Button does not have any other function and the map opens in a Browser Window, you could just delete the command button and comment out the VBA associated with the Click Event.

It opens a map within the form. This occurs only after a venue is selected from the appropriate drop down box:
Option Compare Database
Option Explicit
Private Const cGeoCodeWeb As String = "http://googleapis.com/maps/api/geocode/xml?address="
Private Const cGooMapsQry As String = "https://Maps.Google.co.uk/maps?q="
Private Sub Combo28_BeforeUpdate(Cancel As Integer)
Dim strFrom As String
Dim strTo As String
Dim strOutput As String
Dim wb As Object
If IsNull(Me.Employee_Number) = False Then
Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
End If
If IsNull(Forms![Course Booking]!Venue) = False Then
Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
End If
If IsNull(Forms![Course Booking]!txtPost) = False Then
strTo = "+to+" & Forms![Course Booking]!txtPost
End If
strTo = Replace(strTo, " ", "+")

If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
End If
strFrom = Replace(strFrom, " ", "+")
Dim HTML As Object
Dim strWeb As String
strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"


Debug.Print strWeb
Set HTML = Me.wbGoogleMaps.Object
HTML.Silent = True
HTML.Navigate strWeb
Set HTML = Nothing

End Sub

Private Sub Main_Menu_Click()
On Error GoTo Err_Main_Menu_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Main_Menu "
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Main_Menu_Click:
Exit Sub
Err_Main_Menu_Click:
MsgBox Err.Description
Resume Exit_Main_Menu_Click

End Sub
Private Sub New_Booking_Click()
On Error GoTo Err_New_Booking_Click

DoCmd.GoToRecord , , acNewRec
Exit_New_Booking_Click:
Exit Sub
Err_New_Booking_Click:
MsgBox Err.Description
Resume Exit_New_Booking_Click

End Sub
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

DoCmd.GoToRecord , , acFirst
Dim strFrom As String
Dim strTo As String
Dim strOutput As String
Dim wb As Object
If IsNull(Me.Employee_Number) = False Then
Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
End If
If IsNull(Forms![Course Booking]!Venue) = False Then
Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
End If
If IsNull(Forms![Course Booking]!txtPost) = False Then
strTo = "+to+" & Forms![Course Booking]!txtPost
End If
strTo = Replace(strTo, " ", "+")

If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
End If
strFrom = Replace(strFrom, " ", "+")
Dim HTML As Object
Dim strWeb As String
strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"


Debug.Print strWeb
Set HTML = Me.wbGoogleMaps.Object
HTML.Silent = True
HTML.Navigate strWeb
Set HTML = Nothing
Exit_Command24_Click:
Exit Sub
Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

DoCmd.GoToRecord , , acPrevious
Dim strFrom As String
Dim strTo As String
Dim strOutput As String
Dim wb As Object
If IsNull(Me.Employee_Number) = False Then
Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
End If
If IsNull(Forms![Course Booking]!Venue) = False Then
Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
End If
If IsNull(Forms![Course Booking]!txtPost) = False Then
strTo = "+to+" & Forms![Course Booking]!txtPost
End If
strTo = Replace(strTo, " ", "+")

If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
End If
strFrom = Replace(strFrom, " ", "+")
Dim HTML As Object
Dim strWeb As String
strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"


Debug.Print strWeb
Set HTML = Me.wbGoogleMaps.Object
HTML.Silent = True
HTML.Navigate strWeb
Set HTML = Nothing
Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click

DoCmd.GoToRecord , , acNext
Dim strFrom As String
Dim strTo As String
Dim strOutput As String
Dim wb As Object
If IsNull(Me.Employee_Number) = False Then
Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
End If
If IsNull(Forms![Course Booking]!Venue) = False Then
Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
End If
If IsNull(Forms![Course Booking]!txtPost) = False Then
strTo = "+to+" & Forms![Course Booking]!txtPost
End If
strTo = Replace(strTo, " ", "+")

If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
End If
strFrom = Replace(strFrom, " ", "+")
Dim HTML As Object
Dim strWeb As String
strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"


Debug.Print strWeb
Set HTML = Me.wbGoogleMaps.Object
HTML.Silent = True
HTML.Navigate strWeb
Set HTML = Nothing
Exit_Command26_Click:
Exit Sub
Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click

End Sub
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

DoCmd.GoToRecord , , acLast
Dim strFrom As String
Dim strTo As String
Dim strOutput As String
Dim wb As Object
If IsNull(Me.Employee_Number) = False Then
Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
End If
If IsNull(Forms![Course Booking]!Venue) = False Then
Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
End If
If IsNull(Forms![Course Booking]!txtPost) = False Then
strTo = "+to+" & Forms![Course Booking]!txtPost
End If
strTo = Replace(strTo, " ", "+")

If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
End If
strFrom = Replace(strFrom, " ", "+")
Dim HTML As Object
Dim strWeb As String
strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"


Debug.Print strWeb
Set HTML = Me.wbGoogleMaps.Object
HTML.Silent = True
HTML.Navigate strWeb
Set HTML = Nothing
Exit_Command27_Click:
Exit Sub
Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub
Private Sub Text59_AfterUpdate()
If (Text59 & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Employee Number]=" & Text59
If rs.NoMatch Then
MsgBox "Sorry, no such reference '" & Text59 & "' was found.", _
vbOKOnly + vbInformation
Else
Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close
Text59 = Null
End Sub
Private Sub UpdNwBk_Click()
DoCmd.SetWarnings False
On Error GoTo Macro1_Err
Dim FileName As String
Dim fDialog As Office.FileDialog
Dim strFile As Variant
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = False

' Set the title of the dialog box.
.Title = "Select the Booking Form"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Excel 97 - 2003", "*.xls"
.Filters.Add "Excel Files", "*.xlsx"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
'For Each varFile In .SelectedItems

'Me.FileList.AddItem varFile
Dim exApp As Excel.Application
Dim exWB As Excel.Workbook
Dim Password As String
strFile = .SelectedItems.Item(1)
'Pass = InputBox("Please enter the password")

'Forms!Main_Menu!txtPassword = Password
Set exApp = New Excel.Application
Set exWB = exApp.Workbooks.Open(strFile)
DoCmd.TransferSpreadsheet acImport, 8, "Induction_Booking", strFile, True
exWB.Close
exApp.Quit


Set exWB = Nothing
Set exApp = Nothing
Else
Exit Sub
End If
End With

DoCmd.OpenQuery "Induction_Booking_Update", acViewNormal
DoCmd.SetWarnings True
'Forms![Course Booking].Refresh
'DoCmd.GoToRecord acDataForm, "[Course Booking]", 3
DoCmd.DeleteObject acTable, "Induction_Booking"


Dim strFrom As String
Dim strTo As String
Dim strOutput As String
Dim wb As Object
If IsNull(Me.Employee_Number) = False Then
Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
End If
If IsNull(Forms![Course Booking]!Venue) = False Then
Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
End If
If IsNull(Forms![Course Booking]!txtPost) = False Then
strTo = "+to+" & Forms![Course Booking]!txtPost
End If
strTo = Replace(strTo, " ", "+")

If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
End If
strFrom = Replace(strFrom, " ", "+")
Dim HTML As Object
Dim strWeb As String
strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"


Debug.Print strWeb
Set HTML = Me.wbGoogleMaps.Object
HTML.Silent = True
HTML.Navigate strWeb
Set HTML = Nothing

'Dim oExcel As Object, oWb As Object
'Set oExcel = CreateObject("Excel.Application")
'Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
'Password:=strPassword)

'oWb.Close SaveChanges:=False
'oExcel.Quit
'Set oExcel = Nothing
Macro1_Exit:
Exit Sub
Macro1_Err:
MsgBox Error$
Resume Macro1_Exit
End Sub

[Deggsy]

Does it open a new browser window or does it display the map within the form? If the Command Button does not have any other function and the map opens in a Browser Window, you could just delete the command button and comment out the VBA associated with the Click Event.

The map appears within the form, and appears to be triggered after a venue is selected from a drop down list/combo box. It's trying to identify the Click Event to disable it and nothing else that's been the problem for me :-)

[ItsMe]

As mentioned in post #2, without us understanding what event is triggering the map, we won't be able to help. Is it the combo that is triggering or is it a Button that the user "Clicks"?

You can visit the Properties Sheet while in design view of your form. You can check the After Update event of your combo or the Click event of your command button by looking in the Events tab of your property sheet.

Tell us what control and what action is making the map appear and tell us what you see in the corresponding event within the properties sheet.

[pbaldy]

Code was posted but moderated due to links. I approved it so it is above now.

[ItsMe]

OK, that's quite a bit of code. Let's see...

First thing you will need to do is get comfortable with the VBA editor. If you were able to copy and past the code you know what I am talking about. This is the IDE provided by access to interface with the code behind forms and in Modules.

You will want to comment out code that is undesirable. You could erase it. However, erasing code is not recommended. At least until after you test things to make sure it is OK to erase it. So, comment out the code. Code that is commented out will not compile and not be recognized during Run Time.

There is a tool within the IDE that will do this to multiple lines of code. Simply highlight a block of code and click the "Comment Block" tool. This is available within the Edit Toolbar. You can display the Edit Toolbar in your IDE by right clicking a blank area of the toolbar display and selecting "Edit". Here is an illustration of the Edit Toolbar with the Comment Block tool circled.

Click on a single line of code to place your cursor or swoop over multiple lines to highlight. Then, click the Comment Block tool to place an apostrophe in front of the code to be commented out.
.

[ItsMe]

I do not understand why they opted to place code in the before update event for the combo. I would have probably opted for the AfterUpdate event handler. In fact, this alone, may be why the user does not like the map thing. Anyway, in a copy of your DB, make some edits. Comment out all of the code within the Before Update event of the combo.

Highlight the code

After
Private Sub Combo28_BeforeUpdate(Cancel As Integer)

and before
End Sub

Be sure to get the correct End Sub line, there are several. You want the End Sub for the Combo and directly below "Set HTML = Nothing"

This is the code you want to highlight and then comment out.

Dim strFrom As String
 Dim strTo As String
 Dim strOutput As String
 Dim wb As Object
 If IsNull(Me.Employee_Number) = False Then
 Me.txtPost1 = DLookup("[Post Code]", "Del_PostCode", "[Employee Number] = " & Forms![Course Booking]![Employee Number])
 End If
 If IsNull(Forms![Course Booking]!Venue) = False Then
 Forms![Course Booking]!txtPost = DLookup("[Post Code]", "Stores", "[Cost Centre] = " & Forms![Course Booking]!Venue)
 End If
 If IsNull(Forms![Course Booking]!txtPost) = False Then
 strTo = "+to+" & Forms![Course Booking]!txtPost
 End If
 strTo = Replace(strTo, " ", "+")
 If IsNull(Forms![Course Booking]!txtPost1) = False Then
strFrom = Forms![Course Booking]!txtPost1
 End If
strFrom = Replace(strFrom, " ", "+")
 Dim HTML As Object
 Dim strWeb As String
 strWeb = cGooMapsQry & strFrom & strTo & "&tab=wl&output=classic&dg=brw&z=10"

 Debug.Print strWeb
 Set HTML = Me.wbGoogleMaps.Object
 HTML.Silent = True
 HTML.Navigate strWeb
 Set HTML = Nothing

[Deggsy]

I do not understand why they opted to place code in the before update event for the combo.

Having seen some other features on the database, I have a feeling a concise, straightforward approach was not on the agenda :-)

Anyway, your suggestion appears to have worked, I've not had any negative feedback from the people using it as yet, so thanks for your help :-D

[ItsMe]

You can also go after the Command Button the same way. As a rule, I try to change as little as possible before testing what the changes I did actually do. This goes for when I am adding functionality during design time, too.

Another thing you might try, if you are feeling ambitious, is to place the code that is commented out in a new event handler for the combo. I feel the After Update event handler of the combo will provide a different experience for the User.