Access: Enhancing a MenuBy
Rather than using a plain, static menu with many different command buttons for opening objects or reports, an interactive menu that shows users appropriate options and allows them to input criteria can create a more dynamic and intuitive user experience.
Last month, we built a menu with command buttons to open reports for viewing information and forms for editing data. Now let’s organize the menu form and create controls to collect criteria.
ADD A TAB CONTROL
When a menu contains a number of different controls—such as text boxes, combo boxes, and/or command buttons—you can organize them better by adding a tab control. Grouping related controls into separate pages of the tab control ensures that users will only see the controls that are relevant to the task they want to accomplish.
Open the fMENU form in Design view. If the header and footer of the form aren’t visible, right-click somewhere in the Detail section (away from any control) and select Form Header/Footer to display them. Select the MENU label and drag it to the Form Header section.
On the Design ribbon tab, click on the Tab Control tool. Place a tab control to the right of the command buttons (we’ll move it to the left later on). Select the first tab. In the property sheet, change its name property to “pgReports” and its caption to “Reports.” Change the name of the second tab to “pgForms” and its caption to “Forms.”
Select the two command buttons that open reports: Monthly Sales by Category Detail Report and Monthly Sales by Category Summary Report. Press Ctrl+X to cut them. Click on the Reports tab and press Ctrl+V to paste the buttons. Next, cut and paste the Edit Products button into the Forms page of the tab control.
Select the entire tab control by clicking to the right of the two tabs. Handles will appear around the entire control. Drag the handle on the left side to create more space next to the command buttons. Set the width of each report command button to 3″ and the Left property to 2.25″.
ADD START AND END DATE CRITERIA
It’s common to want to look at the same information for different date ranges, such as this week, this month, last month, this quarter, this year, last year, and so forth. We will modify the main menu to collect start and end dates.
Click on the Reports label to select that page of the tab control. Right-click on the text box tool in the Controls group of the Design ribbon tab and choose Drop Multiple Controls. In the tab control, place a text box about 1″ from the left side of the tab control. Click below it to place a second textbox control. Press Esc to end the mode to drop controls. Name the textbox controls Date1 and Date2, and set their format to Short Date.
Name the labels Label_Date1 and Label_Date2, and enter “Start Date:” and “End Date:” for their respective Caption properties. Double-click on a resizing handle for each label to get the best fit. Then select both labels, right-click on the selection, and choose Size > To Widest from the shortcut menu.
Select the new labels and textboxes, and right-click on the selection. Choose Align > Left. Right-click again and choose Align > Top. Drag the selection to where you want it by placing the cursor over the selection and clicking and dragging when the cursor becomes a four-headed arrow.
Click off the selection to clear it, then select just the two labels. From the Text Formatting group of the Home ribbon tab, choose Align Right. Note the difference between aligning objects, which lines up controls with each other, vs. aligning the contents of a control.
FINISH UP DESIGN
Close the extra space in the form: Move the cursor over the right boundary of the design space. When it becomes a vertical line with a double-headed arrow, click and drag to the left to decrease the width of the menu. Repeat the process along the bottom boundary, dragging up to decrease the height.
Save the form, then switch to Form view. Click on the tab control pages and test the command buttons, which should still work the same. The Reports page of the tab control now has places to enter start and end date, and command buttons to open reports, as shown in Figure 1.
You can also set the menu form to open automatically when the database opens. Go to File, Options, Current Database. Set the Display Form to fMENU (see Figure 2).
Currently, both reports still show all records. (Download this month’s database: SF_Aug_2016_TabControl.) Next month, we will write VBA code to implement the date criteria so reports only show the desired data.