Lesson 10 – Working with Macros and Switchboards
Overview and Tutorial
Database Switchboards
A Switchboard is a form that allows users to easily open objects in a database. Database Switchboards are designed to contain buttons representing objects that users need to access. The buttons on a Switchboard can be used to run macros. Macros are operations that automate certain tasks. Macros can be very complicated and powerful. In this lesson, you will be introduced to some basic macro actions and create a simple Switchboard as an interface for your database.
As you design your macros and Switchboard, think about how the user will interact with the database. The Switchboard should only contain links to those objects users would use. For example, as you built your database, you first created tables which define the fields to be stored in your database. You then developed forms based on the tables for more effective data entry. The user will use the forms to enter data, not the tables; therefore, the Switchboard will not contain access to the tables, only the forms. Make similar decisions for queries used as the basis for reports.
Macros
Before creating the Switchboard for your database, you must first create the macros that will be contained in the Switchboard.
Macro Window
The default view for the macro window includes the Macro tab and the Actioncatalog. Actions are steps the macro takes to achieve a specified outcome. Access has over 60 built-in macro actions which can be found in the Action catalog or in the Add New Action drop-down menu. The Action Catalog is organized by category of action. The Add New Action drop-down list displays all macro actions in alphabetical order. In this lesson, we will experiment with the following very basic built-in macro actions:
- Beep – causes the computer to produce a “beep” sound
- MaximizeWindow – maximize the window referred to in the macro
- OpenForm – opens a form
- OpenQuery – opens a query
- OpenReport – opens a report
- MessageBox – places a message box on the screen displaying instructions or warning to users
- MessageBox types include Critical, Warning?, Warning!, and Information
Many macro actions require additional designer input. This additional information is known as action arguments.
Macro window:
Tutorial Steps
The users of the Belmont database will interact with the Contract Data, Customer, and Invoice Forms, the Contract Details Reports, and the Customers by Specified City Query. Follow the steps outlined below to create macros to access these objects and then assign those macros to a Switchboard.
Create a macro to Open the Contract Data Form
1.Open the Belmont10 Database.
2.From the Create tab, click the Macrobutton.
3.Click the expand button by the Database Objects folder in the Action Catalog. Double-click to choose theOpenFormcommand. The OpenForm action arguments box opens.
4.Click the drop-down arrow for the FormName text box in the Action Arguments box and select the name of the form to be opened—Contract Data.
5.Note that the default view for forms is Form View. By clicking the drop-down arrow in the View field, you can see that additional views include Design, Print Preview, and Datasheet among others. Typically, forms should open in Form View, therefore, we will leave this field at the default setting.
6.You would like this form to open in maximized view. Click the drop-down arrow for the Add New Action text box. Select the MaximizeWindow command. (HINT: Click the first letter of the command to quickly locate it). Notice that no arguments are required for this command.
7.You would also like a message to appear when the user opens this object. Click the drop-down arrow for the Add New Action text box. Select the MessageBox command.
There are four arguments for the MessageBox. Message is used to enter the message you would like displayed to the user.
Beep is used to create a beeping sound with the message box. The default value for Beep is Yes. Set Beep to No if you do not want the beep to sound.
Type is used to indicate what type of message is displaying. By setting the message type, you tell Access what icon to display on the Message Box. Messages can be Critical, Warning?, Warning!, or Information. The default value for this field is None indicating that no message type is selected.
The title argument is used to enter the title to appear on the title bar of the message window.
8.In the Action Arguments box type the following message in the Message text box: Use this form to enter contract data.
9.Type ContractDataForm in the Title text box.
10.Set the MsgBox Type to Information. The information icon will display on the message box.
11.The macro is now complete. Save your work by clicking the save button at the top left of the database window. Name the macro OpenContractDataForm. Notice that a new object heading called Macros appears in the Objects pane. Under that heading is the name of the macro you just saved.
12.Test your macro by double-clicking the macro name in the Objects pane.
An alternative is to click the Run Macro button on the Database Tools tab. A dialog box then displays asking you to indicate the macro you wish to run. Select the macro and click OK.
13.If your macro worked properly, the Contract Data form is open and an information message box displays. To close the message box, click .
14.The Macro window should still be open. To make changes to your macro, click the macro tab. You can now make any desired changes to your macro, save, and test the design again. Continue this process until you achieve the results you want. If the Macro window is closed, right-click the macro you want to edit in the Objects pane and choose Design View.
Create a Macro to Open the Customer Form
1.From the Create tab, click the Macro button.
2.Click the drop-down arrow for the Action text box. Choose OpenForm
3.Select Customer in the FormName text box.
4.Leave the default view set at Form View.
5.Click the drop-down arrow for the Add New Action text box. Add the MaximizeWindow command.
6.Click the drop-down arrow for the Add New Action text box. Add the MessageBox command.
7.Set the message field to: Use this form to enter customer data.
8.Type Customer Data Form in the Title text box.
9.Set the Beep property to No.
10.Set the Type property to Information.
11.Save this macro asOpen Customer Form. Test your macro by clicking the runbutton on the Design tab.
Create a macro to Open the Invoice Form
1.From the Create tab, click the Macro button.
2.Click the drop-down arrow for the Add New Action text box. Choose OpenForm
3. Select Invoice in the FormName text box.
4.Leave the default view set at Form View.
5.Click the drop-down arrow for the Add New Action text box. Add the MaximizeWindow command.
6.Click the drop-down arrow for the Add New Action text box. Add the MessageBox command.
7.Set the message field to:Use this form to enter invoices.
8.Type Invoice Form in the Title text box.
9.Set the Beep property to No.
10.Set the Type property to Information.
11.Save this macro as Open Invoice Form. Test your macro by clicking the button on the Design tab.
Create a macro to Open the Contract Details Report
1.From the Create tab, click the Macro button.
2.Click the drop-down arrow for the Add New Action text box. Choose OpenReport.
3.Select ContractDetails in the Report Name text box.
4.Note that the default view for reports is Report View. Change the view to PrintPreview by clicking the drop-down arrow in the View field.
5.The macro is now complete. Save your work by clicking the save button. Name the macro OpenContract Details Report. Notice that you now have four objects listed under the Macros heading in the Objects pane.
6.Test your macro by clicking the button on the Design tab.
7.Close the Contract Details Report.
Create a macro to Open the Customers By Specified City Query
1.From the Create tab, click the Macro button.
2.Click the drop-down arrow for the Add New Action text box. Choose OpenQuery.
3.Select Customers by Specified City in the Query Name text box.
4.Note that the default view for queries is Datasheet View. Leave the default view.
5.Change the Data Mode property to Read Only. This will keep users from changing data in the database while viewing the query.
6.The macro is now complete. Save your work by clicking the save button. Name the macro OpenCustomers by Specified Query. Notice that you now have five objects listed under the Macros heading in the Objects pane.
7.Test your macro by clicking the button on the Design tab. Note that this query is a parameter query and when it is run, a dialog box appears asking the user to enter the desired city. To complete testing your macro, Enter Hollandand click OK. Your results should display 12 records.
8.Close all open windows.
Add the Switchboard Manager to Access
1.Right-click anywhere in the Ribbon and select Customize the Ribbon.
2.In the right-hand column, click the New Tab command button.
3.A check box with the name New Tab (custom) and a group named New Group (custom) appear in the list.
4.Right-click New Tab (custom) and select Rename. Enter the new name: SwitchboardManager. Right-click New Group (custom) and select Rename. Enter the new name: SwitchboardManager.
5.On the left side of the screen, click the drop-down arrow beside Popular Commands and choose Commands Not in the Ribbon
6.Scroll down the command list and find the Switchboard Manager command. HINT: Commands are in alphabetical order. Select the Switchboard Manager command and then click the Add command button.
7.Click OK in the Access Options dialog box. Your Access ribbon should now contain a tab called Switchboard Manager with one item called Switchboard Manger.
Under most circumstances, once the Switchboard Manager command has been added to your database, you will not need to repeat these steps.
Create a Switchboard for the Belmont Database
1.From the Switchboard Manager tab, click the Switchboard Manager button.
2.The first time you create a Switchboard in any database, you will get the following message:
Choose Yes.
3.The Switchboard Manager dialog box will open indicating that a Switchboard has been created for your database with the default name of Main Switchboard.
4.When first created, the Switchboard has no items assigned to it. The macros you created earlier in this tutorial will be assigned to the Switchboard to create an effective user interface. Begin the process of adding items to the Switchboard by clicking the Edit… button.
5.The Edit Switchboard Pagedialog box opens. Add items to the Switchboard by clicking the New… button. Notice that New and Close are the only available options for the first entry.
6.On the Edit Switchboard Item dialog box, first enter a name for the Switchboard item. For our Switchboard, the first item we’ll add is the Contract Data form. Type Enter Records in theContract Data Form in the Text textbox. Note that the language used in this textbox informs users how they will use the item rather than just naming the item. This is part of effective userinterface.
7.Click the drop-down arrow in the Command textbox and choose Run Macro. (HINT: You can simply type an “r” in the command textbox and the Run Macro command will be inserted.) Press the tab key.
8.Notice that the name of the third textbox has changed to Macro:. Click the drop-down arrow for Macro and choose the name of the macro you want to run -Open Contract Data Form. Your completed Switchboard Item entry should look as follows:
9.Choose OK.
10.Add Switchboard items for your remaining macros following steps 4-8 above. Remember to choose New for each Switchboard item. Enter appropriate Text descriptions for each item.
11.When you have added an item to your Switchboard for each macro, the Edit Switchboard Page should look something like this:
12.Select the Close button when you are finished adding items to the Switchboard, then close the Switchboard Manager window.
Recall that a Switchboard is a form. Notice the Switchboard form that now appears in the list of form objects.
View and Test the Switchboard Form
1.Double-click the Switchboard formin the Objects pane. The following form displays on your screen:
2.Test each command by clicking the button beside each description.
3.To edit items on the Switchboard, select the Switchboard Manager from the Switchboard Manager tab. With the Main Switchboard page selected, choose Edit… Then select the Switchboard item to edit and choose Edit… From here, you can modify the text that appears next to a Switchboard button or change the macro that is run.
4.To make changes to macros, open the macro in Design view. Any changes made to a macro already assigned to a Switchboard will automatically be reflected when the Switchboard operation is selected.
Modify the Switchboard Form
Switchboards are forms and can be edited in much the same way as other forms are edited. You can edit a Switchboard form by adding labels and graphics as well as changing background colors. From Form Design View, you cannot change the text displayed beside a button. This must be done from the Switchboard Manager. Another change that cannot be made in Design View is the default heading (Main Switchboard). This heading is attached to a macro that underlies the Switchboard and this change must be made by changing the Switchboard Name in the Edit Switchboard window.
1.Open the Switchboard in Design View. Your screen will look as follows:
2.You may recall that the default title displayed in Form View is Main Switchboard. In Design View, the file name, Belmont L9, appears in the Form Header label. Remember, this label is not a form object but is tied to a macro stored in a related Switchboard Items table. We will change this title later in the lesson.
3.Expand the Form Header section. Note that what appears to be a teal background is actually a rectangle shape object. Delete the shape object by selecting it and clicking Delete on the keyboard. Your screen now looks as follows:
4.Change the Header background color. Right-click in the background of the form header. Point to Fill/Back Color on the dropdown menu and choose the coral color in the first color row (or any other background color of your choice).
5.Add a label to the Form Header section that will provide instructions to the user. The label should read Click a button below to execute the desired action.
6.Place the label at the left margin of the Form Header.
7.Make the label 14 point, bold, and italic and ensure that the label box is large enough to display the label.
8.Note another teal shape object in the Detail section. Change the color of this object to coral using the same steps used to change the background of the Form Header.
9.Experiment with changing the button colors and style.
10.Switch to Form View to assess your design changes. Your screen should look as follows: