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:

Click to Enlarge

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 = CurrentDbSet ctr = cdb.Containers("Forms&
quot;)

' Set MenuBar, Toolbar properties change on FormsMsgBox "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 ReportsMsgbox "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 = NothingSet 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.