Fill This Form To Receive Instant Help

Help in Homework
trustpilot ratings
google ratings


Homework answers / question archive / New Perspectives Access 2019 | Module 11: End of Module Pro Homestead Partners Creating Modules and VBA     GETTING STARTED Open the file NP_AC19_EOM11-1_FirstLastName_1

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

MS Access

New Perspectives Access 2019 | Module 11: End of Module Pro

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.

 

The figure shows the VBA code for the Form_Current procedure for the PropertyEntry form. The code checks the value of the Baths text box, and sets the background color of the text box to yellow if it is greater than three and white if it is less than three.

 

  1. 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.
  2. 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 test your new function by typing ?AnnualManagementFee(500, 2) and hitting Enter. The return value should be 3000. Save and close the CustomFunctions module.

* Figure 2: CustomFunctions Module

 

The figure shows the VBA code for the AnnualManagementFee function in the CustomFunctions module. The code contains a function which multiplies the DepositValue field by the BathsValue field plus 4 to return the AnnualManagementFee value.

 

  1. 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

 

The figure shows the AnnualFees query in Datasheet View.  The Fee field uses the custom AnnualManagementFee function to determine the value based on the Deposit and Baths fields.

 

  1. 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.
  2. 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.
  3. 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.

Option 1

Low Cost Option
Download this past answer in few clicks

19.99 USD

PURCHASE SOLUTION

Already member?


Option 2

Custom new solution created by our subject matter experts

GET A QUOTE