Need help. Access Macros, VBA, and SQL

In this hands-on project, you will be downloading a database from the Lifetime Media Company, a film and graphics company that does onsite filming and photos. The company uses this database to manage equipment assigned to its employees. You will be completing the following tasks in Microsoft Access:

1. Create a stand-alone macro 

2. Create an embedded macro.

3. Create an event-driven data macro

4. Create a query by writing an SQL statement 

Project Preparation

1. Download the  Lesson 12_Hands On Project(opens in a new tab)  file to your course lesson folder. 

2. Extract the file to the same folder.

3. Open the file in Access.

4. Save your file as " Lesson12_Project1_MEID.accdb" where MEID is your MEID.

Project Directions

Part 1: Stand-Alone Macro

This macro will open a table and a form.

1. Create a new macro using the  Macro Designer.

2. Add an  OpenTable action with the following arguments:

a. Table Name: Lifetime Media Equipment

b. View: Datasheet (default)

c. Date Mode: Edit (default)

3. Save the Macro as ' Open  Name Objects'  where ' Name' is your first name (example: Open John's Objects).

4. Add an  OpenForm action with the following arguments:

a. Form Name: Equipment Checkout Form

b. View: Form (default)

c. Window Mode: Normal (default)

5. Save and close the Macro. 

Part 2: Embedded Macro

This macro will deliver a pop-up message reminder when the user closes a report.

1. Right-click the  Equipment Assignments Report and open it in  Design View. The  Property Sheet should also be on the right of your screen. If it does not display, click the "Property Sheet" button in the Tools group on the Ribbon. 

2. In the Property Sheet, select ' On Close' from the Event tab. 

3. Use the  Macro Builder to  create an embedded macro that will display a message box.

a. In the Macro Builder, choose  Message Box from the  Actions drop-down menu.

b. In the Message box type: ' Send Report to Auditor each Friday'.

c. Beep: Yes

d. Type: Information

e. Title: ' Reminder'

4. Save and close the macro. 

5. Save and close the report.

6. You can  test your macro by opening the Equipment Assignments report from the Navigation pane and then closing the report. If you created the macro correctly, a pop-up message should appear on your screen.

Part 3: Event-Driven Data Macro

This macro will calculate a replacement value if the user indicates that replacement is required.

1. Open the  Lifetime Media  Equipment table if not already opened. You will be adding fields to indicate if a lost/damaged item needs to be replaced and a field indicating the replacement amount per item.  

2. Add a field titled ' ReplacementRequired' and  format the field as  Short Text. 

3. Add a field titled ' ReplacementAmountPerItem' and  format the field as  Currency. Save the Table before moving on to the next step.

4. Create an  event-driven data macro to calculate a 60% replacement amount when the ' ReplacementRequired' field has a value of  "YES". Use an "If-Else" statement that runs Before the record updates. The If-Else logic is as follows:

a. If the ReplacementRequired field is "YES", the ReplacementAmountPerItem field should be set to 60% of the PerUnitCost field.

b. Otherwise (Else), set the ReplacementAmountPerItem field to a value of 0.  Compare your If-Else statement with the illustration below.

5. Save and Close the macro. 

6. In the  Lifetime Media  Equipment table enter  "YES" for ' ReplacementRequired' for records 2, 8, 9, 24, and 25. Enter  "NO" for the rest. If you created your macro correctly, you should see calculated values in the ReplacementAmountPerItem field. 

7. In the  Lifetime Media  Equipment table,  add the following 2 records:

a. One DSLR HD Camera for Jones at a per unit cost of $875.00 (Total Item Cost is $875.00). Replacement will not be required.

b. One Studio HD Camera for Bernard at a per unit cost of $1200.00 (Total Item Cost is $1200). Replacement will be required.

8. Note: The replacement amounts are automatically calculated. 

9. Save and Close the  Lifetime Media  Equipment Table.