New Perspectives Access 2019 | Module 11: SAM Critical Thinking Project 1c Global Human Resources Consultants

GETTING STARTED

1. Open the file NP_AC19_CT11c_FirstLastName_1.accdb, available for download from the SAM website.

2. Save the file as NP_AC19_CT11c_FirstLastName_2.accdb by changing the “1” to a “2”.

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

3. 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), which sells modular human resources (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, and then 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 the ConsultantSalary form.

2. Open the ClientEntry form, and then modify the form to run the VBA code shown below and in Figure 1 as the On Current event executes every time a new entry is navigated to. 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 Save and close the VBE window and the ClientEntry form.

Figure 1: Form_Current VBA Procedure for the ClientEntry Form

3. Open the ConsultantSalary form, and then 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 in Figure 2. Private Sub ContractEmployee_AfterUpdate() If ContractEmployee.Value = True Then MsgBox "Complete contract paperwork!" End If End Sub Save and close the VBE window and the ConsultantSalary form.

Figure 2: ContractEmployee_AfterUpdate VBA Procedure in the ConsultantSalary Form

4. Create a new, standard module named CustomFunctions that contains the function shown below and in Figure 3: 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 Save the CustomFunctions module.

Figure 3: Travel Function VBA Code in the CustomFunctions Module

5. Create a new query based on the Consultant table as follows to test the new custom function (the Travel function) within a query:

a. Use the LastName, Reside, and Salary fields from the Consultant table.

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

c. Save the query using TravelCalculation as the name and then close it.

6. Copy the FormProcedures module and complete the following tasks:

a. Use ReportProcedures for the name of the copied form.

b. Delete the createNewRecord and printCurrentRecord procedures from the ReportProcedures module. 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 then 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 then 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.