Form Menu Bars and Toolbars
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:
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.