New Perspectives Access 2019 | Module 11: End of Module Project 1 Homestead Partners Creating Modules and VBA GETTING STARTED Open the file NP_AC19_EOM11-1_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

New Perspectives Access 2019 | Module 11: End of Module Project 1

Homestead Partners

Creating Modules and VBA

GETTING STARTED

• Open the file NP_AC19_EOM11-1_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_EOM11-1_FirstLastName_2.accdb by changing the “1” to a “2”.
  • If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Open the ManagerEntry form in Design View and open the Visual Basic Editor (VBE) window. Insert the following comment as the second line of the Form_Current procedure, right above the If statement:
   
   'AverageTestScore is the average of Test1, Test2, and Test3
   
   Save and close the Visual Basic Editor, and then save and close the ManagerEntry form.
2. Open the PropertyEntry form in Design View. Add a form event procedure on the On Current event with the VBA code shown below and in Figure 1. This procedure checks the value of the Baths text box. If the value is greater than 3, the background color of the text box is set to yellow. If the value is 3 or less, the background color of the text box is set to white.
   
   Private Sub Form_Current()
     If Baths.Value > 3 Then
       Baths.BackColor = RGB(255, 255, 0) 'yellow
     Else
       Baths.BackColor = RGB(255, 255, 255) 'white
     End If
   End Sub
   
   Save the VBA code and close the VBE window. Save and switch the PropertyEntry form to Form View. Navigate through several records to test your procedure.

• Figure 1: Form_Current Procedure for the PropertyEntry form.

3. Switch to Design View of the PropertyEntry form, and then add an event procedure to the After Update event of the ScenicView check box that tests whether the ScenicView Value property is True. If true, display a message box with the message as shown below:
   
   Private Sub ScenicView_AfterUpdate()
     If ScenicView.Value = True Then
       MsgBox "Ask property owner to send a picture of the view!"
     End If
   End Sub
   
   Save the VBA code and close the VBE window. Save the PropertyEntry form and switch to Form View. Click the ScenicView check box for the first record to test your procedure. Click OK in the message box, click the ScenicView check box again to uncheck it, and then close the PropertyEntry form.
4. Create a new, standard module with the name CustomFunctions, and within it, create the function shown below and in Figure 2. Save the CustomFunctions module.
   
   Function AnnualManagementFee(DepositValue, BathsValue)
      AnnualManagementFee = DepositValue * (4 + BathsValue)
   End Function
   
   With the CustomFunctions module still open in the VBE window, open the Immediate window and run the new custom function using 500 for the DepositValue and 2 for the BathsValue to test your function. The return value should be 3000. Save and close the CustomFunctions module.

• Figure 2: CustomerFunctions Module

5. Create a new query in Design View with the PropertyNo, Baths, and Deposit fields from the Properties table. In the fourth column, create a new calculated field named Fee that uses the new AnnualManagementFee function with the Deposit field for the DepositValue argument and the Baths field for the BathsValue argument. Save the query with the name AnnualFees, and then display it in Datasheet View as shown in Figure 3 to test your new custom function and calculated field. Close the AnnualFees query.

• Figure 3: AnnualFees Query in Datasheet View

6. Copy the CommonFormProcedures module and paste it into the Navigation Pane using CommonReportProcedures for the name. Open the CommonReportProcedures module in the VBE window and delete the createNewRecord procedure. Save and close the CommonReportProcedures module.
7. Convert the HomeReports macro to Visual Basic. Include the error handling and macro comments statements. Accept the default module name and close any open VBE or Macro Design View windows.
8. Convert the macros in the HomeStyles form to Visual Basic. Include the error handling and macro comments statements. Accept the default module name and close any open VBE and Form Design View windows.

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 SAM website to submit your completed project.