New Perspectives Access 2019 | Module 11: SAM Project 1a Global Human Resources Consultants BUILDING CHARTS AND APPLICATION PARTS AND IMPORTING AND EXPORTING DATA   GETTING STARTED Open the file NP_AC19_11a_FirstLastName_1

Subject:MS AccessPrice:20.99 Bought3

Share With

New Perspectives Access 2019 | Module 11: SAM Project 1a

Global Human Resources Consultants

BUILDING CHARTS AND APPLICATION PARTS AND IMPORTING AND EXPORTING DATA

GETTING STARTED

• Open the file NP_AC19_11a_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_11a_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. You work in the Software Division of Global Human Resources Consultants (GHRC) that sells modular Human Resource (HR) software to large international companies. For high-level planning purposes you have created an Access database to track new clients, the HR software modules they have purchased, and the lead consultant for each installation. In this project, you will enhance the database by creating and modifying Visual Basic for Applications, VBA.
   
   Open the ConsultantSalary form in Design View and complete the following:
    
   1. Open the Visual Basic Editor window.
   2. Insert the following comment as the second line of the Form_Current procedure, in the line just above the If statement.
      
      'Must reside in USA and be a contract employee to receive travel allowance
      
      Save and close the Visual Basic Editor and save and close the ConsultantSalary form.
2. Open the ClientEntry form in Design View and complete the following:
    
   1. Add a form event procedure on the On Current event with the VBA code as shown below and as in Figure 1. This procedure checks the value of the Employees field. If the value is greater than 2000, the ConsultantMessage label is visible. If the value is less than or equal to 2000, the ConsultantMessage label is not visible.
      
      
      Private Sub Form_Current()
      If Employees.Value > 2000 Then
          ConsultantMessage.Visible = True
      Else
          ConsultantMessage.Visible = False
      End If
      End Sub
       
   2. Save and close the VBE window, then save and open the ClientEntry form in Form View.
   3. Navigate through several records to test your procedure.
      
      Close the ClientEntry form.

Figure 1: Form_Current VBA Procedure for the ClientEntry Form

3. Open the ConsultantSalary form in Design View and complete the following:
    
   1. Add an event procedure to the After Update event of the ContractEmployee check box that tests whether the ContractEmployee Value property is True. If true, display a message box with the message:
       "Complete contract paperwork!" as shown below and as in Figure 2.
      
      Private Sub ContractEmployee_AfterUpdate()
      If ContractEmployee.Value = True Then
          MsgBox "Complete contract paperwork!"
      End If
      End Sub
       
   2. Save and close the VBE window, then save and open the ConsultantSalary form in Form View.
   3. Click the ContractEmployee check box for the first record to test your procedure. Click OK in the message box, click the ContractEmployee check box again to uncheck it, then close the ConsultantSalary form.

Figure 2: ContractEmployee_AfterUpdate VBA Procedure in the ConsultantSalary Form

4. Create a new, standard module with the name of CustomFunctions and within it, create the following function as shown below and as in Figure 3. Save the CustomFunctions module.
   
   Function Travel(CountryValue, SalaryValue)
   If CountryValue = "USA" Then
       If SalaryValue < 60000 Then
           Travel = 2000
       ElseIf SalaryValue < 70000 Then
           Travel = 1500
       End If
   End If
   End Function

Figure 3: Travel Function VBA Code in the CustomFunctions Module

5. Create a new query in Query Design View based on the Consultant table with the following instructions to test the new custom function, the Travel function, as used within a query:
    
   1. Use LastName, Reside, and Salary fields from the Consultant table.
   2. Create a new calculated field named TravelExpense that uses the new Travel function with the Reside field for the CountryValue argument and the Salary field for the SalaryValue argument. 
   3. Save the query with the name TravelCalculation and then display it in Datasheet View to test your new custom function and calculated field. Close the TravelCalculation query.
6. Copy the FormProcedures module and complete the following:
    
   1. Paste it into the Navigation Pane using ReportProcedures for the name.
   2. Open the ReportProcedures module in the Visual Basic Editor and delete the createNewRecord and printCurrentRecord procedures.
      
      Save and close the ReportProcedures module.
7. Convert the OpenObjects macro to VBA. Include the error handling and macro comments statements. Accept the default module name and save and close any open Visual Basic Editor or Macro Design View windows.
8. Convert the macros in the ClientEntry form to VBA. Include the error handling and macro comments statements. Accept the default module name and save and close any open Visual Basic Editor 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.