In the development process of a Database we spent most of our time on creating Tables, setting Relationships, Designing Forms, Reports, planning process steps to bring the data in Reports so that Users can make right decisions on time. Microsoft Access is loaded with plenty of Menus and Tool Bars to make these designing tasks effortless.

Once that part is over and is on the finishing stage we think about the Security issues and how the Users are going to handle the Database in day-to-day activities and what they should do or shouldn’t do and so on. We don’t want the Users to mess around with the Forms, Reports or any other objects and start implementing their own ideas (may be better than ours) directly, which can be disastrous.

By effectively implementing Microsoft Access Security features we can regulate what a particular User or Group of Users can do or cannot do. Along with that one other aspect we consider is removal of the Default Menu Bars, Tool Bars and replacing them with carefully organized Custom Menus and Tool Bars so that Users can find the necessary work-tools conveniently for their every day tasks.

When you open a Form or Report in Normal View certain settings on the Form/Report’s Properties influences the display of Menus or Tool Bars associated with them. An image of the Form’s Property Sheet is given below:

When you click on the Drop Down control on the Menu Bar Property it will display the List of Custom Menu Bars you have designed in the Database (or imported from other Database) and you can select to insert it here. Like-wise Custom Tool Bars and Shortcut Menu Bar can be inserted in other Properties as well. You can turn On or Off the Shortcut Menu Bar of a Form by setting the Property Shortcut Menu = Yes or No respectively.

When you open the Form in Normal View the Custom Menus and Tool Bar will display based on the settings on the above Properties.

NB: You can go through the following Posts to learn more about designing Custom Menus and Toolbars:

Calendar and Toolbars

Custom Menus and Toolbars

Custom Menus and Toolbars-2

When you have several Forms and Reports in your Database opening each one of them in Design View and settings these Properties manually takes away all the excitement that we had in designing the Database. But, we can give this job to a VBA Routine and it takes hardly a minute to scan through the entire database for Forms, Reports and to insert the Custom Menu Bar and Tool Bar names into the above Properties.

Copy and Paste the Following VBA Routine into a Global Module in your Database and save the Module.

Public Function MenuToolbarSetup()
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date   : September, 1998
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim ctr As Container, doc As Document
Dim docName As String, cdb As Database
Dim msg As String, msgbuttons As Long

On Error GoTo MenuToolbarSetup_Err

Set cdb = CurrentDb
Set ctr = cdb.Containers("Forms")
msgbuttons = vbDefaultButton2 + vbYesNo + vbQuestion
' Set MenuBar, toolbar properties of Forms
msg = "Custom Menus/Toobar Setup on Forms. " & vbCr & vbCr _& "Proceed...?"
If MsgBox(msg, msgbuttons, "MenuToolbarSetup()") = vbNo Then
   GoTo NextStep
End If
For Each doc In ctr.Documents
  docName = doc.Name
'Open the Form in Design View and hidden mode
DoCmd.OpenForm docName, acDesign, , , , acHidden
   With Forms(docName)
     .MenuBar = "MyMainMenu"
     .Toolbar = "MyMainToolBar"
     .ShortcutMenu = True
     .ShortcutMenuBar = "MyShortCut"
   End With
'Save and Close the Form after change
   DoCmd.Close acForm, docName, acSaveYes
Next

NextStep:
'MenuBar,Toolbar properties of Reports
msg = "Custom Menus/Toobar Setup on Reports. " & vbCr & vbCr _& "Proceed...? "

If MsgBox(msg, msgbuttons, "MenuToolbarSetup()") = vbNo Then
   GoTo MenuToolbarSetup_Exit
End If

Set ctr = cdb.Containers("Reports")
'Reports cannot be opened in hidden mode
For Each doc In ctr.Documents
 docName = doc.Name
 DoCmd.OpenReport docName, acViewDesign
 Reports(docName).MenuBar = "MyMainMenu"
Reports(docName).Toolbar = "MyReportToolBar"
DoCmd.Close acReport, docName, acSaveYes
Next

msg = "Custom Menus/Toobar Setup Completed successfully. "

MsgBox msg

Set ctr = Nothing
Set cdb = Nothing

MenuToolbarSetup_Exit:
Exit Function

MenuToolbarSetup_Err:
MsgBox Err.Description
Resume MenuToolbarSetup_Exit
End Function

Since, it is only a design time task you can run this Code directly by placing the Cursor in the body of the Code and by pressing F5 Key. Or Call it from the On_Click() Event Procedure of a Command Button on a Form.

But remember this Form (with the Command Button) also will be opened in Design View for setting the Property Values. If you don't need that to happen then write an If . . .Then statement in the Code to bypass this Form.