Form Menu Bars and Toolbars ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

During the development of a database, most of our time is spent creating tables, defining relationships, designing forms and reports, and planning process steps that transform raw data into meaningful reports, helping users make timely, informed decisions. Microsoft Access provides a wide range of menus and toolbars that make these design tasks relatively easy.

Once development nears completion, our focus shifts to security and usability—specifically, how users will interact with the database in their daily operations and what actions they should or should not perform. We want to prevent users from tampering with forms, reports, or other design elements and unintentionally disrupting the system.

By properly implementing Microsoft Access security features, we can control what each user or group is allowed to do. Additionally, removing the default menu bars and toolbars—and replacing them with custom menus and toolbars tailored to user needs—helps ensure a clean, intuitive interface for everyday use.

The Form/Report-Property Sheet.

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

When you click the drop-down arrow in the Menu Bar property, a list of all custom menu bars you have created (or imported from another database) will appear. You can select the desired menu bar from this list to assign it to the form. Similarly, you can specify custom toolbars and shortcut menu bars in their respective property fields.

You can also enable or disable a form’s shortcut menu by setting its Shortcut Menu property to Yes or No, respectively.

When the form is opened in Normal View, the assigned custom menus and toolbars will automatically appear according to the property settings.

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

Automating Menus/Toolbars Setting

When your database contains numerous Forms and Reports, opening each one individually in Design View to set these properties manually can quickly drain the enthusiasm you had while building the database. Fortunately, this tedious task can be automated with a simple VBA routine.

This routine can scan the entire database in less than a minute, updating all Forms and Reports by assigning the specified Custom Menu Bar and Tool Bar names to their corresponding properties automatically.

Simply copy and paste the following VBA code into a global module in your database, and then 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

Run the Code from the Debug Window

Since this is purely a design-time task, you can execute the code directly by placing the cursor anywhere within the procedure and pressing the F5 key, or by calling it from the On_Click() event of a Command Button on a form.

However, remember that this form (the one containing the command button) will also be opened in Design View when the code runs to update property values. If you want to prevent that from happening, include an If...Then condition in your code to bypass this form.