In some cases, especially when trying to upgrade older databases (mdb -> accdb) it becomes useful to automate certain processes instead of having to manually edit every form/report … One aspect that can be very useful to automate is the switch from using a Toolbar to a Ribbon, as Toolbars get relinquished to the Add-in tab which is less than ideal.
In a recent discussion with MVP Alumni Bill Mosca he brought to our attention an issue he was having trying to perform such an update. After some digging and testing, we were indeed able to confirm that there appears to be a bug in VBA with regards to trying to set a value for the Form/Report Toolbar property.
So a line such as the following does not work
Reports("YourReportName").Report.Toolbar = ""
'Or
Reports("YourReportName").Toolbar = ""
'Or
Reports("YourReportName").Report.Toolbar = "YourToolbarName"
You can set the Ribbon Name without issue, but the Toolbar does not respond in any manner to VBA code. Manually there is no issue, but VBA it is unresponsive (no error, but it just doesn’t work).
Workarounds
Manual
As always, you can manually make the changes. This is obviously less than ideal as this requires Editing each object one by one!
Through Code
You can always use the undocumented SaveAsText/LoadFromText to export your objects, then process the resulting text files to remove/edit the offending line. Thank you to Jack Stockton for the solution (based off of John Viesca FixBroken utility).
I’m extremely grateful for this post, as it saves me a lot of time searching for my own VBA errors – instead of simply accepting that Microsoft made a mistake.
And now I tackle the task of manually deleting all my toolbar names…
This property is available in VBA when a report object is opened in design mode.
Garry Robinson (Microsoft Access MVP 2006-2017) made a routine that will set or clear this property in all report objects and described it in his book “Real World Microsoft Access Database Protection and Security”.
I’ve posted the code below but Daniel may want to add it to this article with better formatting.
‘ From Securing Your Microsoft Access Databases
‘ by Garry Robinson. (Apress)
‘ Copyright 2003. All Rights Reserved.
Option Compare Database
Option Explicit
Private Sub cmdSafeShortcuts_Click()
‘ Add a safe toolbar and right click menu to all reports
‘ This subroutine requires a reference to
‘ Microsoft DAO 3.6 or 3.51 Object Library
On Error GoTo err_cmdSafeShortcuts
Dim dbs As DAO.Database, cnt As DAO.Container, doc As DAO.Document
Dim mdl As Module, objName As String, strDocName As String
Dim i As Integer, modifyMenus As Integer
Dim FilePath As String
Dim fileType As String
Const CONDOCSTATECLOSED = 0
Const RPRTTOOLBAR = “mnuPreviewPrint” ‘ <- Customize this for your needs
Const RPTRPOPUP = "mnuReportPopup" ' <- Customize this for your needs
Const RPRTSAVEMODE = acSave ' Use acSavePrompt if you want to confirm changes
If Application.IsCompiled Then
modifyMenus = MsgBox("Would you like to make " & vbCrLf & vbCrLf _
& RPRTTOOLBAR & " the default toolbar for your reports & " & vbCrLf & vbCrLf _
& RPTRPOPUP & " the default shortcut menu for your reports ? ", _
vbYesNoCancel, "Report Toolbar and Shortcut Menu Properties")
If modifyMenus = vbYes Or modifyMenus = vbNo Then
Set dbs = CurrentDb()
Set cnt = dbs.Containers("reports")
For Each doc In cnt.Documents
strDocName = doc.Name
'Test if the report is already open and close it with a prompt
If SysCmd(acSysCmdGetObjectState, acReport, strDocName) _
CONDOCSTATECLOSED Then
DoCmd.Echo True, “Closing an open report”
DoCmd.Close acReport, strDocName, acSavePrompt
End If
DoCmd.OpenReport strDocName, acDesign
‘Change the settings for the toolbar menu
If Len(Reports(strDocName).Toolbar) = 0 Or _
(Reports(strDocName).Toolbar = RPRTTOOLBAR And modifyMenus = vbNo) Then
‘We only want to change reports with no Toolbar or our Toolbar
If modifyMenus = vbYes Then
‘Make this the default toolbar
Reports(strDocName).Toolbar = RPRTTOOLBAR
Else
‘Clear the toolbar menu from the report
Reports(strDocName).Toolbar = “”
End If
‘Change the settings for the shortcut menu
If Len(Reports(strDocName).ShortcutMenuBar) = 0 Or _
Reports(strDocName).ShortcutMenuBar = RPTRPOPUP Then
‘We only want to change reports with no shortcut menu or our shortcut menu
If modifyMenus = vbYes Then
‘Make this the default popup menu
Reports(strDocName).ShortcutMenuBar = RPTRPOPUP
Else
‘Clear the new popup menu from the report
Reports(strDocName).ShortcutMenuBar = “”
End If
DoCmd.Echo True, “Closing the report that has had its popup properties modified”
DoCmd.Close acReport, strDocName, RPRTSAVEMODE
Else
‘No action to be taken on this report, close the report
DoCmd.Close acReport, strDocName, acSaveNo
End If
End If
On Error Resume Next
DoCmd.Close acReport, strDocName
On Error GoTo err_cmdSafeShortcuts
DoEventsTrigger 2, strDocName
Next doc
End If
Else
MsgBox “This database needs to be compiled first for safety reasons”, _
vbInformation, “Choose menu { Debug … Compile All Modules} from the VBA window”
End If
Exit_cmdSafeShortcuts:
On Error Resume Next
Set doc = Nothing
Set cnt = Nothing
Set dbs = Nothing
‘ The next line displays the current subroutine and can be deleted
If chkViewCode Then DoCmd.OpenModule “Form_” & Me.Name, Me.ActiveControl.Name & “_Click”
Exit Sub
err_cmdSafeShortcuts:
Select Case Err.Number ‘Problems with unload process
Case vbObjectError + 999
‘To see line immediately after the error line, Hit Ctrl+Break keys
‘pull yellow arrow to Resume Next (below) then hit F8 key ”
Resume Next
Case Else
MsgBox “Error No. ” & Err.Number & ” -> ” & Err.Description
End Select
Resume Exit_cmdSafeShortcuts
End Sub
That’s the whole point though, in certain conditions, automating will fail.
The line
will not work anymore in his code.
It is/was some type of bug and Garry’s code does not get around it as it uses the exact same code.