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.

---

Opening Excel Database Directly
Display Excel Value Directly on Form
Opening dBase Files Directly
Opening External Data Sources
PIE Chart Object and VBA

Labels: msaccess menus

Continued from earlier Post: Custom Menus and Toolbars

Open your database where we have created the Custom Menu Bar with the name MyMenuBar. Select Customize from Toolbars sub-menu of View Menu. Or you can right-click anywhere on the Menu Bar or Toolbar above and select Customize from the displayed Shortcut Menu.

We can add built-in menu options from the Customize Control also without copying/dragging from other menus as we did in step 11 in the earlier Post. Click on the Commands Tab on the Customize Control. When you click on any of the Menu items on the left (in categories list) the built-in commands pertaining to that Menu is displayed in the Commands List at the right window. From here you can click and drag (no need to press and hold the Ctrl key) any option and place it on your Custom Menu Bar or Toolbar to organize the Built-in Options for the User.

12. We will concentrate on our totally independent Menu Options: Data Files, Process & Reports and create our own Programs/Macros to define the action on each one of them. A sample image of the Data File Options that I have added to the menu is given below:




When you click on any of the options in the Data Files Menu it will open a Form. I have selected three types of Command items to build the Menu Options. Custom for a single item with only description, New Menu to create a Sub-Menu option with a drop-down control & New… for Buttons with icon or without it, from the Menu Bar/Toolbar Customize Control.

13. Select Files in the Categories List of Customize Control, click and drag the Custom control from the Commands List and place it on the Data Files menu’s drop-down control. Right-click on the control and type Address Book in the Name: control.

NB: The name that you have typed in the Name: control is used for referencing the menu item in Programs and should not be the same name of a Built-in Menu. If it is, then Access will show a warning message. By default the Menu Name is displayed for its Caption also (or Menu description).

For example: Assume that we have named Data File as DataFile for our Menu without a space in between to use it as a single word in programs but we need Add/Edit Data as the menu description. Right-click on the Data Files menu, select Properties from the displayed menu and type Add/Edit Data in the Caption: box.

Repeat the process for the Department List option. The third item Other Data is a group item.

14. Find the New Menu item at the end of the Categories list and select it to display the New Menu control in Commands List. Click, drag and place it on the menu as shown and rename it as Other Data. The sub-menu have a dropdown control at the right side, where we will add few New… control buttons.

15. Click on the Files Category in the Categories List (in the Customize Control). The Custom & New… options are visible in the Commands list. Click and drag the New… button and drop it on the Other Data option’s drop-down control at the right side and change the name as Staff List. Add one more item similarly and change its name as Branches in the Name: control and select a button image from the Change Button Image option.

16. Add 3 Options on the Reports menu following the same procedure as explained above. Name the Options as Address Labels, New Employees List & Department Code List.

17. On the Process Menu add a single Custom control and name it as Process Report. Our Menu designing is complete. All that is left to do is to define some Action on each of these Buttons.

I have news for you, the difficult part is over. It was difficult because you are doing it for the first time. If you understood the overall concept then you can quickly assemble the menu within no time. Besides, if you design the menu to some extend as a standard one, keeping in mind your forthcoming Applications, then you can Import this Menu Bar into the New Application and easily customize it and redefine Actions on them.

Tip: You can redefine the Action of any built-in Menu Option as well.

We need two small Programs to run our Data Files and Reports Menu Options and a Macro to run the Process Report Option. VB Routines for Data Files & Reports are given below:

Public Function DataFiles(ByVal intOption As Integer)

Select Case intOption
    Case 1
        DoCmd.OpenForm "Address Book", acNormal
    Case 2
        DoCmd.OpenForm "Staff List", acNormal
    Case 3
        DoCmd.OpenForm "Department List", acNormal
End Select

End Function

Before going for the Menu let us look at the code above. When we call the Function DataFiles() we have to pass a number for the Menu Option in the Order in which we have placed them on the Menu. For Example: The Address Book Option is in the top of the list on the Data files Menu and we will pass 1 as parameter to the Function like =DataFiles(1) in the Action Control of the Menu Option. We test this value with the Select Case statement in the code and the action can be programmed underneath Case 1 statement to open the Form. For the Next Button number 2 as parameter and so on.

18. Let us go for the first button to define its action. Display the Toolbar Customize Control (Right-click on the Toolbar above and select Customize) if it is not already on. Click on the Data Files menu to display the drop-down list and right-click on the Address Book Option and select Properties. Type =DataFiles(1) in the Action Control. Repeat the process with successive numbers as Parameter to the function for other buttons keeping in mind that this number you are going to check in the program and sequence the action there.

19. Follow the same procedure for Reports Menu too. In the Reports() Function given below, under the Case 2 statement I have introduced a Macro running line before opening the Report in Print Preview assuming that a sequence of Action Queries that we have already organized in the Macro to run and create the Report Table before opening the Report that linked to it.

Public Function Reports(ByVal intOption As Integer)

Select Case intOption
    Case 1
        DoCmd.OpenReport "Address_Labels", acViewPreview
    Case 2
        If MsgBox("Re-process Report Data...?", _
           vbYesNo +vbQuestion + vbDefaultButton2, _
           "Reports()") = vbYes Then
              Docmd.RunMacro "ProcessData"
        End if
        DoCmd.OpenReport "New_Employees_List", acViewPreview
    Case 3
        DoCmd.OpenReport "Dept_Codes", acViewPreview
End Select
End Function

We have not forgotten our Process button in the middle. The word Process itself denotes that a lot of action goes into it. Since, the entire process running is mostly sequenced in a Macro the Action of this button is only to call this macro directly rather than call a function and check the number and run the macro etc. The result of the process may be displayed or printed later.

20. Assume that we have prepared a Macro with the name MyProcess and we will connect this macro to the Process Option. Display the Toolbars Customize control, if it is not already on, click on the Process menu and right-click on the Process Report Option and select Properties. In the Action control type MyProcess. Note, here we are not typing the = sign for the macro action.

I don’t like this method at all. Assume that the User clicks on this button by mistake the macro runs and goes through with all the Action Queries put in there. A better method is passing the control into a Program as we have seen in the Reports() function under the Case 2 statement where we are asking the user whether to run the macro and re-create the Report Table or not. If she says no then macro is not touched the Report is open with earlier data and displayed.

Macro can be used for opening Forms and Reports. If the user opens it by mistake she can easily close it. Then, if you have several Forms, each form should have one macro and you need a lengthy list of macros in your macro Tab of your database.

Toolbars & Shortcut Menus.

Now, that you have become expert on creating Custom Menus, it is not different for creating Toolbars or Shortcut Menus. I don’t want to talk at length on this subject and bore you to death. You can organize your Custom Toolbar with your own buttons and define action to it combined with most frequently used built-in toolbar buttons like Sort, Filter, Copy, Paste etc. for the User.

For the custom Shortcut Menu Bar create a Custom Toolbar and define it as a Shortcut Menu by selecting Popup in the Type control on the Toolbar Properties. Create only one Drop-down sub-Menu (by placing the New Menu option from the Categories List on the Customize control ) and add built-in menu options like Cut, Copy, Paste, etc. and add custom menu buttons to run your own programs like we did for the Calendar Control. When you change the Type of the Toolbar to Popup, Access will display a message saying that the Toolbar will be placed under the Custom Group in the built-in Shortcut Menu. Built-in Shortcut Menu Bar we have already seen in the Animated Floating Calendar topic.

Here, naturally a question arises, how we are going to use the Custom Shortcut Menu on our Forms? We have seen that when we right-click on a control on the Form the built-in Shortcut Menu will appear on which we have inserted the Calendar displaying button and used earlier.

Before going into that let us look into two basic concept of presenting the Application to the User. One method that we have already seen is, design a Control Screen and organize all our centrally controllable options like Opening the Data Files, Processing Data & Printing Reports etc. on the Screen and keep this screen open till the user shut downs the Application.

Another method is that design a Menu Structure as we did just now with Menu bars and Toolbars instead of a Control Screen. In that case the Application background will be always empty, except the Menus on the top, provided if we remove the check mark from the Display Database Window option in the Startup Setup. When a Screen or Report is open that object will occupy the empty area.

The Startup options on the Tools menu can be modified to disable all the built-in Menus to prevent them from appearing and prevent the user from modifying the Menus or Toolbars as well. An Image of the Application Startup Option Setting is given below:




On the Display Form/Page control we have inserted the Start_up Screen that we have designed earlier so that when the Application is open the Start-up screen will show up for a few seconds and disappear advertising that the Application is loaded and ready for use.

The Menu Bar and Shortcut Menu Bar controls on the Startup Setup control above are set with the Menu names that we have designed ensuring that the Custom Menu Bar is displayed in place of the normal built-in Menu Bar. When we right-click on a text control on a form our custom Shortcut Menu Bar will show up instead of the built in one.

There is one more setting for all the three menu types that we can update to ensure that our Menus and Toolbars are up all the time. On the Form & Report Properties, there are four Properties dedicated for the Menu Bar & Toolbars setup one each for Custom Menu Bar, Custom Toolbar & two for Custom Shortcut Menu where you can insert your Menu and Toolbar Names. Short Cut Menu have one more setting, which is to show or not to show the menu where Yes to show the menu and No to prevent the shortcut menu from appearing when right clicked on a Screen, like Control Screen, where you don't need a shortcut menu.




For Reports, a special Toolbar must be designed with printing specific built-in options like Page Setup, Print, Export to Word or Excel etc.

If several Forms and Reports are there in our Application then opening them one by one in design view and changing these Menu Bar/Toolbar Properties and saving them is a difficult task. Instead we can do this at design time of each object or write a program to do this job for us.

Design a small Form with a Command Button on it and change the Name property of the Command button as cmdMenus. Create a label on the Top of the Form with the Caption: Menu Bar/Toolbar Setup. Change the properties of the form to remove unwanted features like, Record Navigation Buttons, Record Selector, Scroll bars, Dividing Lines etc. Display the Code Module of the Form (select Code from View menu when the Form is open in design view). Copy and paste the following code into the Module of the Form:

Private Sub cmdMenus_Click()
Dim msg As String, resp As Integer, X As Integer

msg = "Menu Bar/Toolbar Setting " & vbCr & vbCr _
& "Changes on Forms/Reports" & vbCr & vbCr _
& "Proceed...?"
resp = MsgBox(msg, vbYesNo +vbQuestion + vbDefaultButton2, _ 
"cmdMenus_click")
If resp = vbYes Then
 X = MenuToolSet()
End If
End Sub


Private Function MenuToolSet()
Dim ctr As Container, doc As Document
Dim docName As String, cdb As Database

On Error GoTo MenuToolSet_Err

Set cdb = CurrentDb
Set ctr = cdb.Containers("Forms")

' Set MenuBar, Toolbar properties change on Forms
MsgBox "Form Toolbars Setup."

For Each doc In ctr.Documents
 docName = doc.Name
 If docName = "ToolbarSetup" Then ‘ Not on this Form
   GoTo nextform
 Else
  DoCmd.OpenForm docName, acDesign, , , , acHidden
  With Forms(docName)
   .MenuBar = "MyMenuBar" ‘ change name, if different.
   .Toolbar = "AgrMainToolBar" ‘ change name, if different.
   .ShortcutMenu = True
   .ShortcutMenuBar = "AgrShortCut" ‘ change if different.
  End With
  DoCmd.Close acForm, docName, acSaveYes
 End If
nextform:
Next

'MenuBar toolbar properties change on Reports
Msgbox "Menu Bar/Toolbar Setup on Reports."

Set ctr = cdb.Containers("Reports")

For Each doc In ctr.Documents
 docName = doc.Name
 DoCmd.OpenReport docName, acViewDesign
 Reports(docName).MenuBar = "MyMenuBar"
 Reports(docName).Toolbar = "AgrmReport"
 DoCmd.Close acReport, docName, acSaveYes
Next

Msgbox ("Menubars/Toobars : On Forms & Reports" _
& vbCr & vbCr & "Setup Finished.")

Set ctr = Nothing
Set cdb = Nothing

MenuToolSet_Exit:
Exit Function

MenuToolSet_Err:
MsgBox Err.Description
Resume MenuToolSet_Exit
End Function

Save the Form with the name ToolbarSetup. Open and Click the command button on the Form to run the program. Each form will open in design view, in hidden mode, and changes the Toolbar properties and saves them automatically. In the case of Reports there is no hidden mode for design view. So we can see the Reports are opening and closing very fast after changing the properties by the above program.

Labels: msaccess menus

We already had a formal introduction to Custom Tool Bar, Submenus & Shortcut Menus through the earlier posts: Animated Floating Calendar & Calendar and Toolbars. We have seen how to reference individual Button to enable/disable it based on situations that demands it.

Here, we will take a closer look at Custom Menu bars, Toolbars & Shortcut Menus. Since, it is a lengthy subject we are forced to split this into 2 or 3 Posts.

MENU BARS

Windows based Applications have a Menu bar at the top with titles like File, Edit, View, Insert etc. When we click on them a long list of Options like Sub-menus, Items with Description alone or with Button Images and Description appears in a drop down list. When we click on any of them the designated action defined on the menu option runs and we will get our work done. We can create a Custom Menu bar with our Application specific Menu Descriptions like Data Files to open Forms linked to Tables/Queries, Reports to preview and print Reports, Utilities for other common tasks etc., add menu items into them and assign Actions to each of them. Initially we will concentrate on designing the Menu Bar alone. When this is understood the task of designing Toolbars and Shortcut Menus become easier, because the approach is same and you will be able to do it yourself with little guidance.

1. Select Customize command from Toolbars submenu of View Menu.


2. Select New… from the Toolbars Tab.


3. Type MyMenuBar in Toolbar Name: control and click OK. The new Toolbar Item is added in the Toolbars List and a small empty Toolbar appears in the Application surface, not necessarily at the top.


You may be wondering that we have started discussing Menu bars then why we have to deal with Toolbars. We need to create a Toolbar first and then define it as a Menu bar, that’s how it goes. For Shortcut Menu, create a Toolbar and define it as a Popup Menu (Shortcut Menu). So, the starting point is Toolbar. The Toolbar’s name alone will not change its class, except referencing it in programs, and we must see that the new name is not clashing with any of the other Built-in Menus. Let us define our MyMenuBar Toolbar into a real Menu bar.


4. Click on the MyMenuBar item in the Toolbars List in the Customize Dialogue Control, if it is not already selected.


5. Click Properties…


6. The Selected Toolbar & Toolbar Name Controls already filled with the menu name. In the Type Property drop down list we can see three options: Menu Bar, Toolbar & Popup(Shortcut Menu). To define our Toolbar into a Menu Bar select Menu Bar from the list.




Leave the other properties as it is. While installing your Application for the Users you can change these properties to determine how to behave the Menu Bar. Click Close to come out of the Toolbar Properties Sheet. It is likely that the small empty toolbar that we have seen earlier while creating it, is now disappeared and wondering where it went. To find out remove the check mark on MyMenuBar item in the Toolbars Tab and put the check mark back again and repeat this process a few times in quick succession and you can see an empty Menu bar is playing hide and seek on the top. Once we have spotted our celebrity we can continue our work.

First we must create some meaningful menu headings with dropdown facility where we can add our Menu Options. Click on the Commands Tab of the Customize Control. Here, we have two set of List: Categories & Commands. In the Categories List the File item is already selected, if it is not select it now. In the Commands List we will be using the first two items: Custom & New… for building our Menu Options. The Custom item is used for creating a single menu item with description only and New… is used for creating single option with a Button Image and Description, which can be displayed with Image only, Text only or both. But both of these items will not work for a Drop-down List, which we see normally when we click on File, Edit, or View Menus. We have to use another control for that.

Move the Scrollbar down in the Categories List and you will find an item called New Menu at the bottom of the list, select it. After selecting it, in the Commands List at the right side you will find a New Menu option. This Menu is a drop-down list like control. We need all these three options to build our Custom Menu Bar.


7. Click and drag the New Menu item from the Commands window and drop it on our new empty Menu Bar. After that click on it to display a small empty dropdown control where we will be adding our menu options.


8. Right click on the New Menu to display the options to change its Name. Type &Main (we need a different name in place of File we see everywhere) in place of New Menu in the Name: control and press Enter to close. The & (ampersand) symbol at the left side of M character of the word Main enables us to select the Main Menu by using Alt+M Keyboard shortcut instead of clicking. You can put the & symbol left side of any character in the name to use that character as a keyboard shortcut. The character that you have chosen will appear with an underline in the Menu Name. If you look at the Built-in Menu descriptions at the top you can see one character in each Menu Name is underlined. But you must be very careful while selecting the character and see that you are not setting up a clash with the built-in keyboard shortcuts.


For example: Assume that you have given & symbol at the left side of D in Data Files Menu and you are in the database window and the Forms Tab or Report Tab is active, if you execute the Alt+D keyboard shortcut, the selected Form, Report or whatever object currently have the focus on will open up in Design View. For safer side we will not give ampersand symbol in any of the other menu options for now.

We will create three more Menu headings: Data Files, Process, Reports, Utilities (another name for Tools). Follow the same steps explained above under 8 & 9 and create the Menu Headings.

We will mainly concentrate on Data Files, Process and Reports options for our own program/macro driven methods and in Main & Utilities Menus we will copy and add some essential built-in Options.

We will do the easier part first, adding built-in options into the Main & Utilities menu. For easy handling of our MyMenuBar we need to push it up above the Built-in Menubar so that we can easily copy and drop (I didn’t say drag and drop and you guessed it right) the Submenus or Options into our Menu. If you are not already in Customize option select Customize from Toolbars Submenu from View Menu. We will introduce four sample options: Close, Page Setup, Print… and Exit into our Main menu from the Built-in File Menu.

There are too many references to use for creating Custom Menus/Toolbars. First time anything is difficult or may find too cumbersome, but further on you will find them easy and rewarding too.


9. Click on the built-in File menu to display the drop-down list. Click and hold the mouse button on the Close menu item and press Ctrl key with the other hand. When you press the Ctrl key, a plus (+) symbol will appear next to the selection indicating that a copy of the selected option is going to be made rather than drag the option out and leave that area empty for the built-in File Menu. Now, drag the mouse up and point to the Main item of our Menu Bar to display the empty dropdown control. Drop the Close option there.


10. Follow the same procedure for Page Setup, Print… & Exit options to make a copy of each and place it one below the other, or in between wherever you prefer.

After dropping the menu items, if you prefer to define the option as part of a Group (for example: Page Setup & Print… can be members of a common group) then right click only on the top item within the group and select Begin a Group from the displayed options menu. A horizontal line will appear above the selected option.

NB: Any changes that you have made in any of the Built-in MenuBars (for example: If you have dragged the Print… option out of the File Menu rather than copying an instance of it, it will not be available in File Menu), Toolbars or Shortcut Menus can be restored to its original state. Select Menu Bars in the Toolbars Tab of the customize control and click the Reset… Button.


11. Follow the same procedure to introduce two options from the built-in Tools Menu into Utilities Menu: Spelling…, User and Group Accounts from Security Submenu. The User and Group Accounts option is used by Users for changing their Login Password in a secured Application.

We will continue our discussion shortly on Data Files, Process & Reports Menu options where we plan to introduce our own simple Programs & and Macros to open Forms, Reports etc.

Labels: msaccess menus