We can create cascading Menus with Tab Control and Option Group Controls on Form.  Several Menus can be arranged neatly one behind the other and allow the User to make the one he/she would like to see by selecting the Main Menu Option.

For example: the Sample image given below shows a Main Menu with three Options, each representing different category and Group of Sub-Menu Options. 

When Data Files Option is selected in the Main Menu, the Sub-Menu at the right displays its corresponding Options. The User can click on any one of the options shown at the right to open and work with that file.

When the User selects the Option Reports in the Main Menu; Report Options  will appear in the same place replacing the Data Files Options displayed earlier.  The Main Menu Option Views will bring up its Sub-Menu Options replacing the earlier display.  This way several Menus can be arranged and displayed at the same place with a magical touch and can be Programmed with VBA or Macros to run the detail options.

You don’t need to work with any complicated VBA Programs except a few simple lines of Code and Macros.  The design task is very simple and once you know the trick you can implement it anywhere in no time.

The sample Design image of the above Form is given below:

1. Open a new Form in Design view.

    

2. Select the Option Group Control from the Toolbox and draw it near to the left side of the Form in the Detail Section.

    

3. Enter the three Options (Data Files, Reports and Views) pressing Tab Key in each step to advance to the next line in the Wizard.

    

4. Click Finish to create the Option Group Control with Radio Button Type Controls with the Keyed-in Values as Labels.

    

5. Change the Caption Value of the attached Child-Label as Main Menu and position it on top of the Option Group control as shown on the design above.

    

6. Click on the outer frame of the Option Group Control to select it and display its Property Sheet (View – -> Properties).

    

7. Change the Name Property Value to Frame0 and the Border color Property Value to 0.

    

8. Select the Tab Control from the Toolbox and draw a Tab Control to the right of the Option Group Control (check the design image above).

    

   A Tab Control with two Pages will be created.  We must insert one more Page to the Tab Control.

    

9. While the Tab Control is still in selected state (if it is not then click at the right of the Tab Pages) Right-Click on it to display the Shortcut Menu.

    

10. Select Insert Page from the Shortcut Menu to add another Page to the Tab Control.

     

11. While the Tab Control is still in selected state display its Property Sheet.

     

12. Change the Name Property Value to TabCtl9.

     

    NB: No dot (.) at the end of the name when you change it on the control.

     

13. Click on the first Page of the Tab Control to make it current.

     

14. Select Option Group Control from the Toolbox and draw it on the first Page of the Tab Control.

     

15. Enter the following Options (or Form Names of your own Tables in your Database) by pressing Tab Key after each option on the Wizard:

     

    • Employees
    • Orders
    • Order Details
    • Customers
    • Products

     

16. Click Finish to complete and create an Option Group with Radio Button Style options.

     

17. Display the Property Sheet of the Option Group (View – ->Properties).

     

18. Change the following Property Values as shown below:

     

    • Name = Frame1
    • Default Value = 0
    • Border Color = 0

     

19. Change the Caption of the Child-Label attached to the Option Group Control to Data Files, make its width as wide as the Option Group Control and position it above, as shown in the design image above.

     

    We must create two more Option Group Controls on the 2nd and 3rd Pages of the Tab Control with different set of Options.

     

20. Follow Step-13 to 19 to create Option Group Control on 2nd Page of the Tab Control with the following Options and name the Option Group Frame as Frame2 and the Child-Label Caption as Report List:

     

    • Employee Address Book
    • Employee Phone Book
    • Invoice
    • Monthly Report
    • Quarterly Report

     

    You may create Report Names from your own Database replacing the above List.

     

21. Create another Option Group Control on the 3rd Page of the Tab Control with the following Options or create your own Options and name the Option Group as Frame3 and Child-Label Caption as View Options:

     

    • View Inventory
    • View Orders
    • View Customers
    • View Suppliers

     

    Now, we have to write few lines of VBA Code for the Main Menu Option Group to select the detailed Options Page of the Tab Control based on the menu selection.  Even though the Page Captions shows something like Page10, Page11 and Page12 (this may be different on your design) each Page is indexed as 0, 1 and 2.  If you want to select the second Page of the Tab Control to display the Report Options then you must address the Tab Control Page2 in Code as TabCtl9.Pages(1).Setfocus.

     

    We can select individual Page of the Tab Control by clicking on it too.  But, this manual action will not synchronize with the Main Menu selection. The items on the Option Group Menu also have the index numbers 1 to the number of items on the Menu (Report List options 1 to 5).

     

    When the User clicks on one of the items on the Option Group Main Menu we can test its index number and make its corresponding detailed menu on the Tab Control Page current.

     

    In the final refinement of the Menus we will hide the Tab Pages of the Tab Control so that the Sub-Menus on them can be accessed only through program, depending on the selection made on the Main Menu by the User.

     

    First, let us write a small VBA Routine on the On Click Event Procedure of the Frame0 Option Group Control (Main Menu) to allow the User to select one of the Options on it and display its corresponding detailed Sub-Menu on the Tab Control.  By default 1st item (Data Files) on the Main Menu will be in selected state and the Data Files list will be visible on the Sub-Menu.

     

22. Display the Code Module of the Form (View – -> Code) or click on the Module Icon on the Toolbar Button.

     

23. Copy and paste the following VBA Code into the Module:

     

     

    Private Sub Frame0_Click()
    Dim k
    k = Me![Frame0]
    Select Case k
        Case 1
            Me.TabCtl9.Pages(0).SetFocus
        Case 2
            Me.TabCtl9.Pages(1).SetFocus
        Case 3
            Me.TabCtl9.Pages(2).SetFocus
    End Select
    
    End Sub

24. Save and close the Form with the name Main Switchboard.

     

25. Open the Main Switchboard in normal view.

     

26. Click on the 2nd Option Reports on the Main Menu to display the Report List on the 2nd Page of the Tab Control.

     

27. Try selecting other options on the Main Menu and watch the sub-menu changes on the Tab Control Pages.

Now, we will write VBA Code like above example to open Data File Forms, when the User select Options from the Sub-Menu.

1. Open the Main Switchboard in Design View.

    

2. Display the Code Module of the Form (View – ->Code).

    

3. Copy and Paste the following VBA Code into an empty area of the Module:

    

   Private Sub Frame1_Click()
   Dim f1
   f1 = Me![Frame1]
   Select Case f1
       Case 1
           DoCmd.OpenForm "Employees", acNormal
       Case 2
           DoCmd.OpenForm "Orders", acNormal
       Case 3
           DoCmd.OpenForm "Order Details", acNormal
       Case 4
           DoCmd.OpenForm "Customers", acNormal
       Case 5
           DoCmd.OpenForm "Products", acNormal
   End Sub

4. Save and Close the Main Switchboard Form.

    

   For running the Report Options we will create a Macro and attach it to the Option Group Control (with the name Frame2) rather than using VBA routine.

    

5. Select the Macro Tab on the Database window and select New to open a new Macro in design view.

    

6. You must display the Condition Column of the Macro by selecting the Toolbar Button with the Icon Image (or similar image) given below:

    

   

    

7. Write the following Macro lines, as shown in the image given below, with the appropriate Parameter Values at the bottom Property Sheet for opening each Report in Print Preview/Print:

    

   

    

8. Save the Macro with the name RptMac.

    

9. Open the Main Switchboard Form.

    

10. Click on the 2nd Page of the Tab Control to display the Reports Option Group Menu.

     

11. Click on the outer frame of the Option Group Menu to select it.

     

12. Display the Property Sheet (View – ->Properties).

     

13. Find and Click on the On Click Property to select it.

     

14. Click on the drop-down list at the right edge of the Property and select the RptMac name from the list to insert in into the On-Click Event Property.

     

    NB: You may create another Macro/VBA Routine for the third menu and attach it to the Frame3 Option Group Menu, before doing the next step.

     

    In the next step we are going to remove the Pages of the Tab Control, so that the transition of the Tab Pages through Code gives a magical touch to the Sub-Menu as different Menus will appear in the same place inter-changeably.

     

    You can further refine the Sub-Menus by changing the dimension and position, by changing the following values same on all the three Sub-Menus on the Tab Control pages.

     

    • Top
    • Left
    • Width
    • height

     

15. Click on the outer edge of the Tab Control (or click at the right side of the third page) to select it.

     

16. Display the Property Sheet (View - ->Properties).

     

17. Find the Style Property in the Property Sheet and change the value Tabs to None. 

     

18. Save and close the Main Switchboard Form.

     

19. Open the Form in normal view and try out the Menus.

     

Auto Numbering in Query Column

Web Browsing within Access Form

Text Search Filter Web Style

Filter with BuildCriteria Function