Menus with Option Group Control

Introduction

We can create cascading Menus with Tab Control and Options 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 the Main Menu with three Options, each representing a different category, and a Group Sub-Menu Options.

When the 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 on 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 in the same place with a magical touch and can be Programmed with VBA or Macros to run the detail options.

Simple Interface Design and Code

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 the Design view.

2. Select the Option Group Control from the Toolbox and draw it near 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 Options Group control as shown on the design above.

6. Click on the outer frame of the Options 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 Options Group Control (check the design image above).

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

9. While the Tab Control is still in the selected state (if it is not, then click on 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 the 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.

    Data Tables Menu.

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 Options 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 Options 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 a different set of Options.

    The Reports Menu.

20. Follow Step-13 to 19 to create Option Group Control on the 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.

    Data View Menu.

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 a 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 Page Captions show 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 an 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 the program, depending on the selection made on the Main Menu by the User.

    Code for Main Menu.

    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 the 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

    Trial Run of Menu.

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

25. Open the Main Switchboard in a 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.

Forms Menu.

Now, we will write VBA Code like the 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.
   
   

   Macros for Report Menu.

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

5. Select the Macro tab in the Database window and select New to open a new Macro in the 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.

   Attach Macro to Report Options.

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 Options 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 it 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 on 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 Menu.

• Data Upload Controls
• Auto Numbering in Query Column
• Web Browsing within Access Form
• Text Search Filter Web Style
• Filter with BuildCriteria Function