SOLUTION AVAILABLE AT: Office 2019 MS-Access Grader YO19_Access_Ch12_PS1 – Automating Tasks in the Putts for Paws Database 1.0 (libraay.com)
STEP-BY-STEP TUTORIAL GUIDE AVAILABLE
Problem:
Putts for Paws is an annual charity event that takes place at the Painted Paradise Golf Resort & Spa and raises money for a local animal shelter. You have been asked to enhance their current database by creating macros that will improve the database’s efficiency and functionality.
Steps to Perform:
| Step | Instructions | Points Possible |
| 1 | Start Access. Download and open the file named Access_Ch12_PS1_PuttsForPaws.accdb. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files. | 0 |
| 2 |
In the next steps, you will create a macro that will import the data contained in the NewParticipants.xlsx spreadsheet into the tblParticipant table. To begin, add a comment to the macro, and type This macro imports the list of new participants into the tblParticipant table. (include the period). |
8 |
| 3 | Add a MessageBox to the macro. Type the message as You are about to import new participants into the tblParticipant table. (include the period). | 8 |
| 4 | Select Yes for the Beep argument and Information for the Type argument. | 8 |
| 5 |
Type Importing Participants for the Title. If necessary, Show All Actions. Add the SetWarnings action to the macro and select No for Warnings On. |
8 |
| 6 | Add the ImportExportSpreadsheet action to the macro and complete the necessary arguments to Import the spreadsheet. Select Import for the Transfer Type. Select Excel Workbook for Spreadsheet Type. Type tblParticipant for Table Name. and then in the File Name argument, type the path to the Excel spreadsheet to be imported. At the end of the file path, type \NewParticipants.xlsx, and then select Yes for Has Field Names argument. |
8 |
| 7 | Save the macro as mcrImportNewParticipants. Run the macro, close the macro, and verify that the new participants have been added to the tblParticipant table (there will now be 48 records). | 6 |
| 8 |
In the next steps, you will create an After Insert Data Macro in the tblOrderLine table so that when a new record is recorded, the quantity ordered (in tblOrderLine) is automatically deducted from the quantity available in the tblItem table. To begin, add a comment at the top of the macro and type This macro automatically deducts the quantity ordered from the quantity available in the tblItem table. (include the period). |
8 |
| 9 |
Add the necessary Data Blocks and Data Actions to complete the task: Use the LookupRecord action to look up the matching ItemID in tblItem. Use the EditRecord action to set the Quantity Available field in tblItem to be the difference between the QuantityAvailable from tblItem and the Quantity from tblOrderLine. Save and close the macro. Save the table. |
8 |
| 10 |
Test the macro by entering the following record into the tblOrderLine table and confirming that the quantity available in the tblItem table for CART Item ID changes from 40 to 37: OrderID: 11 Close the tables. |
6 |
| 11 | Create a command button on the frmParticipant form that, when clicked, will open the frmOrder form to the participant’s order form if an order had been previously placed or a new record if an order has not been placed. Type Place Order for the button’s caption and place it in between the Add Record and Find Record buttons. |
8 |
| 12 | Add a comment to the top of the macro and type: This macro will open the frmOrder form to the participant’s record if applicable and a new record if not. (include the period). | 8 |
| 13 | Add the OpenForm action and fill in the necessary arguments to complete the task. Have the frmOrder form open in Form View, select Edit for Data Mode, and Normal for Window Mode. Type [ParticipantID]=[Forms]![frmParticipant]![ParticipantID] in the Where Condition argument. Save and close the macro. |
8 |
| 14 | Apply an Intense Effect – Red, Accent 2 Quick Style to the button. Change the shape of the button to a Rectangle: Rounded Corners. Test the macro. Save and close the form. | 8 |
| 15 | Close all database objects. Close the database and then exit Access. Submit the database as directed. | 0 |
| Total Points | 100 |
SOLUTION AVAILABLE AT: Office 2019 MS-Access Grader YO19_Access_Ch12_PS1 – Automating Tasks in the Putts for Paws Database 1.0 (libraay.com)