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. 

image1.png