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

Friday, October 17, 2008

Form Menu Bars and Toolbars

Introduction.

In the development process of a Database, we spent most of our time on creating Tables, setting Relationships, Designing Forms, Reports, and planning process steps to bring the data into Reports so that Users can make the 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 in 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, Report Design, or any other objects and start implementing their own ideas (maybe 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 removing the Default Menu Bars, and Toolbars, and replacing them with carefully organized Custom Menus and Tool Bars so that Users can find the necessary work tools conveniently for their everyday tasks.

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 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 another Database) and you can select to insert it here. Likewise, 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 ToolBar 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:

Automating Menus/Toolbars Setting

When you have several Forms and Reports in your Database opening each one of them in Design View and settings these Properties manually take 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

Run the Code from Debug Window

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 the 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.