New Perspectives Access 365 | Module 10: SAM Project A Tech Builders
CREATING MACROS
· Excel Projects Help
GETTING STARTED
1. Save the file NP_AC365_10A_FirstLastName_1.accdb as NP_AC365_10A_FirstLastName_2.accdb
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.
2. With the file NP_AC365_10A_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.
a. If the table does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. You work in the data division of Tech Builders, which sells computer and network system hardware to a variety of companies. For high level planning purposes you have created an Access database to track new customers, the hardware modules they have purchased, and the lead representative for each installation. In this project you will improve the database by creating macros. Complete the following:
a. Create a new macro in Macro Design View and add the OpenForm macro action.
b. Modify the OpenForm macro action to open the RepresentativeSalary form in Form View using the other default macro argument values.
c. Save the macro with the name OpenRepresentativeSalaryForm and run it.
d. Close the RepresentativeSalary form, then close the OpenRepresentativeSalaryForm macro.
2. Open the OpenCustomerReports macro in Macro Design View and complete the following:
a. Collapse the first OpenReport action that opens the CustomerDetails report.
b. Expand the second OpenReport action and use Report as the value for the View argument.
c. Delete the third OpenReport action that opens the Revenue report. Save and close the OpenCustomerReports macro.
3. Open the OpenRepresentativeReports macro in Macro Design View and complete the following:
a. Expand both of the macro actions.
b. Open the Action Catalog if it is not already open and show all actions within it.
c. Add the OpenQuery action in the macro.
d. Set Query Name to SortedRepresentativeImport, leave the other settings at their default values.
e. Position the OpenQuery action as the second action in the macro. Save and close the OpenRepresentativeReports macro.
4. Open the OpenObjects macro in Macro Design View and complete the following:
a. Collapse all existing macro actions within the Submacro, then collapse the Submacro.
b. After the existing Submacro, add a new Submacro with the name OpenQueries and one action to open the Revenue query in Datasheet View in Edit Data Mode. Save and close the OpenObjects macro.
5. Create a new macro in Macro Design View with the following instructions:
a. Add an If program flow block.
b. Use the conditional expression [Reside] = "CAN" in the If block.
c. Add a SetProperty macro action within the If block.
d. Use TravelAllowance for the Control Name argument, BackColor for the Property argument, and #0000FF for the Value argument. (Hint: #0000FF is blue.)
e. Add an Else block and add a SetProperty macro action within it.
f. Use TravelAllowance for the Control Name argument, BackColor for the Property argument, and #FFFFFF for the Value argument in the Else block. (Hint: #FFFFFF is white.) Save the macro with the name Travel as shown in Figure 1, and then close it.
Figure 1: Travel Macro in Design View
6. Open the RepresentativeSalary form in Design View and complete the following:
a. Attach the Travel macro to the On Current event of the form.
b. Switch to Form View and navigate through several records. Those with "CAN" in the Reside text box should display a blue background in the Travel Allowance text box. Save and close the RepresentativeSalary form.
7. Open the CustomerEntry form in Design View and complete the following:
a. Use #FFFF00 for the Hover Fore Color property for the Close command button. (Hint: #FFFF00 is yellow.)
b. Save the form, display it in Form View and hover over the Close command button to test the change. Close the CustomerEntry form.
8. In Design View of the Representative table complete the following:
a. Create a data macro on the Before Change event.
b. If the Reside field value is "CAN" and the ContractEmployee field value is True use the SetField action to set the value of the TravelAllowance field to 3000 as shown in Figure 2.
c. Save and close the data macro then open the table in Datasheet View. (Hint: Select Yes in the Microsoft Access window that appears asking if you want to save the table now.) Check the ContractEmployee field value to True (Yes) for the first two records, RepresentativeID 110345 Haris Howard and 110347 Roberto Reyes to test your data macro. Close the Representative table.
Figure 2: Data Macro in Representative Table
Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.