Menus with Option Group Control ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

We can create cascading menus on a form using Tab Controls and Option Group controls. Multiple menus can be arranged neatly, one behind the other, allowing the user to select the main menu option to display the corresponding submenu.

For example, the sample image below illustrates a Main Menu with three options, each representing a different category, along with a corresponding group of sub-menu Options for each category.

Right-click to open Large Image in New Tab/Window.

When the Data Files option is selected in the main menu, the corresponding Submenu appears on the right, allowing the user to click any option to open and work with that file.

Similarly, selecting Reports in the main menu displays the report options in the same area, replacing the previously shown data file options. Choosing the Views option brings up its respective sub-menu, again replacing the previous display.

In this way, multiple menus can be arranged and transitioned in the same space with a seamless, dynamic interface. These menus can be programmed using VBA or macros to run the detailed options associated with each selection.

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

Right-click to open Large Image in New Tab/Window.

Open a new Form in the Design view.
Select the Option Group Control from the Toolbox and draw it near the left side of the Form in the Detail Section.
Enter the three Options (Data Files, Reports, and Views), pressing the Tab Key in each step to advance to the next line in the Wizard.
Click Finish to create the Option Group Control with Radio Button type Controls, with the Keyed-in Values as Labels.
Change the Caption Value of the attached child label to Main Menu and position it on top of the Options Group control in the design above.
Click on the outer frame of the Options Group Control to select it and display its Property Sheet (View -> Properties).
Change the Name Property Value to Frame0 and the Border color Property Value to 0.
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.
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.
Select Insert Page from the Shortcut Menu to add another Page to the Tab Control.
While the Tab Control is still in the selected state, display its Property Sheet.
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.
Click on the First Page of the Tab Control to make it current.
Select Option Group Control from the Toolbox and draw it on the First Page of the Tab Control.
Enter the following Options (or Form Names of your own Tables in your Database) by pressing the Tab Key after each option on the Wizard:
- Employees
- Orders
- Order Details
- Customers
- Products
Click Finish to complete and create an Option Group with Radio Button Style options.
Display the Property Sheet of the Options Group (View ->Properties).
Change the following Property Values as shown below:
- Name = Frame1
- Default Value = 0
- Border Color = 0
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 Option Group Controls on the 2nd and 3rd Pages of the Tab Control with a different set of Options.
The Reports Menu.
Follow Steps 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
Create Report Names from your own Database, replacing the above List.
Data View Menu.
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 from 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, 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.
Display the Code Module of the Form (View -> Code) or click on the Module Icon on the Toolbar Button.

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.

Save and close the Form with the name Main Switchboard.
Open the Main Switchboard in a normal view.
Click on the 2nd Option Reports on the Main Menu to display the Report List on the 2nd Page of the Tab Control.
Try selecting other options on the Main Menu, and monitor the Submenu changes on the Tab Control Pages.

Forms Menu.

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

Open the Main Switchboard in Design View.
Display the Code Module of the Form (View ->Code).

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

Save and Close the Main Switchboard Form.

Macros for Report Menu.
To run 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.
Select the Macro tab in the Database window and select New to open a new Macro in the design view.
You must display the Condition Column of the Macro by selecting the Toolbar Button with the Icon Image (or similar image) given below:

Right-click to open Large Image in New Tab/Window.
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:

Right-click to open Large Image in New Tab/Window.
Save the Macro with the name RptMac.

Attach Macro to Report Options.
Open the Main Switchboard Form.
Click on the 2nd Page of the Tab Control to display the Reports Option Group Menu.
Click on the outer frame of the Options Group Menu to select it.
Display the Property Sheet (View ->Properties).
Find and click on the On Click Property to select it.
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 will remove the pages of the Tab Control. Transitions between tab pages can be controlled entirely through code. This creates a seamless, “magical” effect for the sub-menu, allowing different menus to appear interchangeably in the same location.

You can further refine the sub-menus by adjusting their dimensions and positions. Ensure to apply the same settings consistently to all three sub-menus on the Tab Control pages for a uniform appearance.
- Top
- Left
- Width
- height
Click on the outer edge of the Tab Control (or click on the right side of the third page) to select it.
Display the Property Sheet (View ->Properties).
Find the Style Property in the Property Sheet and change the value of Tabs to None.
Save and close the Main Switchboard Form.
Open the Form in normal view and try out the Menu.

MEOctober 12, 2010 at 11:22 AM
Another nice one.

Private Sub Frame1_Click() needs an end select statement.

Is there anyway to change the color of the tab control? I can't seem to find anything, but there must be some way.
MEOctober 12, 2010 at 11:26 AM
This is another nice one. Thanks.

By the way, I found that the select statements need an End Select statement as well or it crashes.

Is there a way to change the color of the tab control?