Ms-Access Advanced VBA Programming Techniques, Tips and Tricks.

Ads

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.

Share:

1 comment:

Trending

Infolinks Text Ads

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards ms-access functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Custom Functions Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Activity Dates and Quarterly Reports

There are four Quarters in a Year: Jan - Mar = 1st Quarter Apr - Jun = 2nd Jul - Sep = 3rd Oct - Dec = 4th First three months of the yea...

Labels

Blog Archive

Recent Posts