New Perspectives Access 2019 | Module 11: SAM Project 1b Ridgedale College   CREATING MODULES AND VBA     GETTING STARTED Open the file NP_AC19_11b_FirstLastName_1

Subject:MS AccessPrice:19.99 Bought3

Share With

 

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

Ridgedale College

 

CREATING MODULES AND VBA

• GETTING STARTED

• Open the file NP_AC19_11b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_11b_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 Admissions Office at Ridgedale College to help track grants awarded to professors as well as teaching assistant assignments by class. You have created an Access database to organize these details. In this project you will enhance the database by creating and modifying Visual Basic for Applications, VBA.
   
   Open the GrantEntry 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.
      
      'Large grants require review
      
      Save and close the Visual Basic Editor and save and close the GrantEntry form.
2. Open the ClassEntry form in Design View and complete the following:
    
   1. Add a form event procedure to the On Current event with the VBA code as shown below and as in Figure 1. This procedure checks the value of the first three characters of the ClassNo field. If the first three characters start with SCI (for Science) the Lab Credits label named lblLabCredits and the LabCredits text box named txtLabCredits should be visible.
      If the ClassNo field does not start with SCI, the label named lblLabCredits and the LabCredits text box named txtLabCredits should be not be visible.
      
      Private Sub Form_Current()
      If Left([ClassNo], 3) = "SCI" Then
        lblLabCredits.Visible = True
        txtLabCredits.Visible = True
      Else
        lblLabCredits.Visible = False
        txtLabCredits.Visible = False
      End If
      End Sub
       
   2. Save and close the VBE window, then save the ClassEntry form and switch to Form View.
   3. Navigate through 10 records to test your procedure.
      Close the ClassEntry form.

3. Open the ProfessorEntry form in Design View and complete the following:
   1. Add an event procedure to the After Update event of the HireDate text box that tests whether the HireDate Value property is equal to or greater than 1/1/2020. If true, display a message box with the message:
       "New health savings plans available in 2020!"  as shown below and as in Figure 2.
      
      Private Sub HireDate_AfterUpdate()
      If HireDate.Value >= #1/1/2020# Then
        MsgBox ("New health savings plans available in 2020!")
      End If
      End Sub
       
   2. Save and close the VBE window, then save the ProfessorEntry form and switch to Form View.
      Click the HireDate text box for the first record, enter 01/01/2020, and tab out of the HireDate text box to test your procedure. Click OK in the message box, then save and close the ProfessorEntry form.

4. Create a new, standard module with the name of SalaryFunctions and within it, create the following custom function named ComputerStipend as shown below and as in Figure 3. Save and close the SalaryFunctions module.
   
   Function ComputerStipend(Department, Salary)
       If Department = "CIS" Then
           ComputerStipend = 0.05 * Salary
       Else
           ComputerStipend = 0
       End If
   End Function

5. Create a new query in Design View based on the Professors table with the following instructions to test the new custom function, the ComputerStipend function, as used within a query:
   1. Use ProfLastName, Department, and Salary fields from the Professors table.
   2. Create a new calculated field named Stipend that uses the new ComputerStipend function with the Department field for the Department argument and the Salary field for the Salary argument. 
   3. Save the query with the name ComputerStipendCalculation and then display it in Datasheet View to test your new custom function and calculated field as shown in Figure 4. Close the ComputerStipendCalculation query.

6. Copy the FormProcs module and complete the following:
   1. Paste it into the Navigation Pane using ReportProcs for the new module name.
   2. Open the ReportProcs module in the Visual Basic Editor and delete the goToNewRecord and printRecord procedures.
      Save and close the ReportProcs module.
7. Convert the RunTAReports 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 SectionEntry 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.

Ridgedale College

CREATING MODULES AND VBA

• GETTING STARTED

• Open the file NP_AC19_11b_FirstLastName_1.accdb, available for download from the SAM website.
• Save the file as NP_AC19_11b_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 Admissions Office at Ridgedale College to help track grants awarded to professors as well as teaching assistant assignments by class. You have created an Access database to organize these details. In this project you will enhance the database by creating and modifying Visual Basic for Applications, VBA.
   
   Open the GrantEntry 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.
      
      'Large grants require review
      
      Save and close the Visual Basic Editor and save and close the GrantEntry form.
2. Open the ClassEntry form in Design View and complete the following:
    
   1. Add a form event procedure to the On Current event with the VBA code as shown below and as in Figure 1. This procedure checks the value of the first three characters of the ClassNo field. If the first three characters start with SCI (for Science) the Lab Credits label named lblLabCredits and the LabCredits text box named txtLabCredits should be visible.
      If the ClassNo field does not start with SCI, the label named lblLabCredits and the LabCredits text box named txtLabCredits should be not be visible.
      
      Private Sub Form_Current()
      If Left([ClassNo], 3) = "SCI" Then
        lblLabCredits.Visible = True
        txtLabCredits.Visible = True
      Else
        lblLabCredits.Visible = False
        txtLabCredits.Visible = False
      End If
      End Sub
       
   2. Save and close the VBE window, then save the ClassEntry form and switch to Form View.
   3. Navigate through 10 records to test your procedure.
      Close the ClassEntry form.

3. Open the ProfessorEntry form in Design View and complete the following:
   1. Add an event procedure to the After Update event of the HireDate text box that tests whether the HireDate Value property is equal to or greater than 1/1/2020. If true, display a message box with the message:
       "New health savings plans available in 2020!"  as shown below and as in Figure 2.
      
      Private Sub HireDate_AfterUpdate()
      If HireDate.Value >= #1/1/2020# Then
        MsgBox ("New health savings plans available in 2020!")
      End If
      End Sub
       
   2. Save and close the VBE window, then save the ProfessorEntry form and switch to Form View.
      Click the HireDate text box for the first record, enter 01/01/2020, and tab out of the HireDate text box to test your procedure. Click OK in the message box, then save and close the ProfessorEntry form.

4. Create a new, standard module with the name of SalaryFunctions and within it, create the following custom function named ComputerStipend as shown below and as in Figure 3. Save and close the SalaryFunctions module.
   
   Function ComputerStipend(Department, Salary)
       If Department = "CIS" Then
           ComputerStipend = 0.05 * Salary
       Else
           ComputerStipend = 0
       End If
   End Function

5. Create a new query in Design View based on the Professors table with the following instructions to test the new custom function, the ComputerStipend function, as used within a query:
   1. Use ProfLastName, Department, and Salary fields from the Professors table.
   2. Create a new calculated field named Stipend that uses the new ComputerStipend function with the Department field for the Department argument and the Salary field for the Salary argument. 
   3. Save the query with the name ComputerStipendCalculation and then display it in Datasheet View to test your new custom function and calculated field as shown in Figure 4. Close the ComputerStipendCalculation query.

6. Copy the FormProcs module and complete the following:
   1. Paste it into the Navigation Pane using ReportProcs for the new module name.
   2. Open the ReportProcs module in the Visual Basic Editor and delete the goToNewRecord and printRecord procedures.
      Save and close the ReportProcs module.
7. Convert the RunTAReports 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 SectionEntry 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.