Watts' A Loan is a fictitious company that provides small to mid-level loans to persons and small businesses. An individual can request a small loan in cash. The company also has a channel of business partners as furniture stores, musical instruments stores, car dealers, boat manufacturers, tractor sellers, etc. A person who applies for financing in one of those stores may get a loan from Watts' A Loan that will actually finance the loan.

Practical Learning: Inserting Nodes

1. Start Microsoft Access
2. Click Blank Desktop Database

   

3. Set the file name to WattsALoan
4. Click Create
5. On the Ribbon, click File and click Options
6. In the left list of the Access Options dialog box, click Current Database
7. In the main list, click Overlapping Windows

   

8. Click OK on the dialog box
9. Click OK on the message box
10. On the Ribbon, click File and click Close
11. On the Ribbon, click File
12. In the list of files, click WattsALoan

Employees

The employees of a company are the people who perform all types of transactions such as creating loans or processing payments. To represent the employees, we will create a table and related objects.

Practical Learning: Creating Employees

1. On the Ribbon, click Create and click Table Design

   

2. In the first empty box under Field Name, Type EmployeeID and press Tab
3. In the corresponding Data Type combo box, select AutoNumber
4. Right-click EmployeeID and click Primary Key
5. In the lower section of the table, click Caption and type Employee ID
6. Create the other fields as follows:
   
   

   Field Name	Data Type	Field Size	Caption
   EmployeeID	AutoNumber	Long Integer	Employee ID
   EmployeeNumber	Short Text	10	Employee #
   FirstName	Short Text	20	First Name
   LastName	Short Text	20	Last Name
   EmploymentTitle	Short Text	50	Employee Title

7. Close the table
8. When asked whether you want to save the table, click Yes
9. Type Employees as the name of the table
10. Click OK
11. On the Ribbon, click Create and click Form Design
12. While the form is displaying, in the Object Properties window, in the All tab, click Record Source and select Employees
13. In the Property Sheet, click the All tab and change the following characteristics:
    Record Source: Employees
    Caption: Watts' A Loan - Employees
    Auto Center: Yes
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
14. Right-click the middle of the form and click Form Header/Footer
15. On the Ribbon, click the Form Design tab
16. In the Form Design tab, click Add Existing Fields
17. In the Field List window, click one of the item, then press Ctrl + A to select all fields
18. Drag the group of fields from the Field List window to the Detail section of the form
19. Complete the design of the form as follows:

    

    Control	Text	Font Name	Font Size	Other Properties
    FormHeader				Back Color: Background 2, Darker 50% Special Effect: Raised
    Label	Watts' A Loan	Bodoni MT Black	26	Fore Color: Background 1, Darker 25%
    Line				Border Color: Background 1, Darker 5%
    Label	Employee	Bodoni MT Black	26	Fore Color: Background 1
    Labels	Employee ID:, Employee #:, First Name:, Last Name:, Employee Title:	Bell MT	12	Fore Color: Text 1, Lighter 40%
    Text Box	EmployeeID	Bell MT	12	Fore Color: Text 1, Lighter 25% Enabled: No Locked: Yes
    Text Boxes	EmployeeNumber:, FirstName:, LastName:, EmployeeTitle:	Bell MT	12	Fore Color: Text 1, Lighter 25% Border Color: Text 1
    FormFooter				Back Color: Text 1, Lighter 25% Special Effect: Raised
    Button	Close	Bell MT	12	Name: BtnClose Back Color: Accent 1, Lighter 40% Font Weight: Bold Fore Color: Text 1, Lighter 25%

20. In the FormFooter section of the form, click the Close button. On the Ribbon, click the Form Design tab. In the Tools section of the Ribbon.
    If the Convert Form's Macros to Visual Basic button appears, click it. In the Convert Form Macros: Form1 dialog box, click Convert
21. In the FormFooter section of the form, right-click the Close button and click Build Event...
22. In the Choose Builder dialog box, click Code Builder and click OK
23. Implement or change the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

24. Close Microsoft Visual Basic and return to Microsoft Access
25. Switch the form to Form View:

    

26. Close the form
27. When asked whether you want to save, click Yes
28. Change the form name to Employees
29. Click OK

Loans Types

A typical lending company provides various types of loans. To prepare for the types of loans that our company will deal with, we will create a table.

Practical Learning: Creating Types of Loans

1. On the Ribbon, click Create and click Table Design
2. In the top empty box under Field Name, type LoanTypeID and press Tab
3. Type the letter a (that should select AutoNumber) and press F6
4. In the lower part of the window, in the Caption field, type Loan Type ID
5. Click the first empty box under Field Name
6. Type LoanType and press F6
7. In the lower part of the window, change the following properties:

   Field Size: 25
   Caption: Type of Loan

8. In the top section of the window, right-click LoanTypeID and click Primary Key
9. Close the table
10. When asked whether you want to save, click Yes
11. Type LoansTypes as the name of the table
12. Click OK
13. On the Ribbon, click Create and click Form Design
14. In the All tab of the Property Sheet, change the Record Source to LoansTypes
15. On the Ribbon, click Add Existing Fields
16. Complete the design of the form as follows:

    

    Control	Text	Font Name	Font Size	Other Properties
    FormHeader				Back Color: Background 2, Darker 50% Special Effect: Raised
    Label	Watts' A Loan	Bodoni MT Black	26	Fore Color: Background 1, Darker 25%
    Line				Border Color: Background 1, Darker 5%
    Label	Loan Type	Bodoni MT Black	26	Fore Color: Background 1
    Labels	Loan Type ID:, Loan Type:	Bell MT	12	Fore Color: Text 1, Lighter 40%
    Text Box	LoanTypeID	Bell MT	12	Fore Color: Text 1, Lighter 25% Enabled: No Locked: Yes
    Text Box	LoanType:, FirstName:, LastName:, EmployeeTitle:	Bell MT	12	Fore Color: Text 1, Lighter 25% Border Color: Text 1
    FormFooter				Back Color: Text 1, Lighter 25% Special Effect: Raised
    Button	Close	Bell MT	12	Name: BtnClose Back Color: Accent 1, Lighter 40% Font Weight: Bold Fore Color: Text 1, Lighter 25%

17. Right-click the Close the button and click Build Event...
18. In the Choose Builder dialog box, click Code Builder and click OK
19. Implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

20. Close Microsoft Visual Basic and return to Microsoft Access
21. Switch the form to Form View
22. Close the form
23. When asked whether you want to save, click Yes
24. Change the form name to LoansTypes
25. Click OK

Loan Evaluation

To assist the employees who will process loans, we can create a form that will be used to perform calculations.

Practical Learning: Evaluating Loans

1. On the Ribbon, click Create and click Form Design
2. In the Property Sheet, click the All tab and change the following characteristics:
   Caption: Watts' A Loan - Loan Evaluation
   Auto Center: Yes
   Record Selectors: No
   Navigation Buttons: No
   Border Style: Dialog
3. Right-click the body of the form and click Form Header/Footer
4. Save the form as LoanEvaluation
5. Design the form as follows:

   

   Control	Text	Name	Font Name	Font Size	Other Properties
   FormHeader					Fore Color: Background 1, Darker 25%
   Label	Watts' A Loan				Back Color: Background 2, Darker 50% Special Effect: Raised
   Line					Border Color: Background 1, Darker 5%
   Label	Loan Evaluation		Bodoni MT Black	26	Fore Color: Background 1
   Label/Text Box	Loan Amount:	txtLoanAmount	Bell MT	12	Fore Color: Background 1
   Label/Text Box	Interest Rate:	txtInterestRate	Bell MT	12	Fore Color: Background 1
   Label	%		Bell MT	12	Fore Color: Background 1
   Label/Text Box	Periods:	txtPeriods	Bell MT	12	Fore Color: Background 1
   Label	Months		Bell MT	12	Fore Color: Background 1
   Button	Calculate	cmdCalculate	Bell MT	12	Fore Color: Background 1
   Line			Bell MT	12	Fore Color: Background 1
   Label/Text Box	Periodic Payment:	txtPeriodicPayment	Bell MT	12	Fore Color: Background 1
   Label	/Month		Bell MT	12	Fore Color: Background 1
   Label/Text Box	Interest Amount:	txtInterestAmount	Bell MT	12	Fore Color: Background 1
   Label/Text Box	Future Value:	txtFutureValue	Bell MT	12	Fore Color: Background 1
   Button	Close	cmdClose	Bell MT	12	Fore Color: Background 1

6. On the form, right-click the Calculate button and click Build Event...
7. In the Choose Builder dialog box, double-click Code Builder
8. Implement the event as follows:

   Private Sub cmdCalculate_Click()
       Dim periods
       Dim loanAmount
       Dim futureValue
       Dim interestRate
       Dim interestPaid
       Dim interestAmount
       Dim periodicPayment
       
       loanAmount = CDbl(Nz(txtLoanAmount))
       interestRate = CDbl(Nz(txtInterestRate)) / 100#
       periods = CDbl(Nz(txtPeriods))
   
       periodicPayment = Pmt(interestRate / 12#, periods, -loanAmount)
       futureValue = FV(interestRate / 12#, periods, -periodicPayment)
       interestAmount = futureValue - loanAmount
       
       txtPeriodicPayment = FormatNumber(periodicPayment)
       txtFutureValue = FormatNumber(futureValue)
       txtInterestAmount = FormatNumber(interestAmount)
   End Sub

9. In the Object combo box, select cmdClose
10. Implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

11. Close Microsoft Visual Basic and return to Microsoft Access
12. Swicth the form to Form View:

    

13. Close the form
14. When asked whether you want to save, click Yes

Loans Contracts

To get a loan, a customer must request it. At that time, a loan contract must be established. We will create a table and form that can be used to create and manage loans.

Practical Learning: Procesing Loans Contracts

1. On the Ribbon, click Create and click Table Design
2. In the first empty box under Field Name, type LoanContractID and press Tab
3. Right-click LoanContractID and click Primary Key
4. Press Ctrl + S to save the table
5. Type LoansContracts as the name of the table
6. Click OK
7. Complete the design of the table as follows:

   

   Field Name	Data Type	Field Size	Format	Caption	Other Properties
   LoanContractID	AutoNumber				Loan Contract ID
   LoanNumber	Short Text	20		Loan #
   DateAllocated	Date/Time		Long Date	Date Allocated	Input Mask: 00->L<LL-00
   EmployeeID				Employee
   CustomerFirstName	Short Text	20		First Name
   CustomerLastName	Short Text	20		Last Name
   LoanTypeID				Loan Type
   LoanAmount	Number	Double	Currency	Loan Amount
   InterestRate	Number	Double	Percent	Interest Rate
   Periods	Number	Byte	Standard		Decimal Places: 0
   MonthlyPayment	Number	Double	Currency	Monthly Payment
   FutureValue	Number	Double	Currency	Future Value
   InterestAmount	Number	Double	Currency	Interest Amount
   PaymentStartDate	Date/Time		Long Date	Payment Start Date	Input Mask: 00->L<LL-00

8. In the Data Type combo box that corresponds to EmployeeID, select Lookup Wizard
9. In the first page of the Lookup Wizard, make sure the first radio button is select. Click Next
10. In the second page of the Lookup Wizard, make sure Table: Employees is selected. Click Next
11. In the third page of the Lookup Wizard, double-click EmployeeNumber, FirstName, LastName, and EmploymentTitle

    

12. Click Next
13. In the fourth page of the wizard, click Next
14. In the fifth page of the wizard, click Next
15. In the sixth page of the wizard, click Finish
16. Read the text on the message box and click Yes
17. In the bottom section of the table, click the Lookup tab
18. Change the following values:

    Column Heads:  Yes
    Column Widths: 0";0.85";0.85";0.85";2.5"
    List Width:    5.05

19. In the Data Type combo box that corresponds to LoanTypeID, select Lookup Wizard
20. In the first page of the Lookup Wizard, make sure the first radio button is select. Click Next
21. In the second page of the Lookup Wizard, click Table: LoansTypes:

    

22. Click Next
23. In the third page of the Lookup Wizard, double-click LoanType:
24. In the second page of the Lookup Wizard, click Table: LoansTypes:

    

25. Click Next
26. In the fourth page of the wizard, click Next
27. In the fifth page of the wizard, click Next
28. In the sixth page of the wizard, click Finish
29. Read the text on the message box and click Yes
30. In the bottom section of the table, in the Lookup tab, change the following values:

    Column Widths: 0";1.5"
    List Width:    1.5

31. Close the table
32. When asked whether you want to save, click Yes
33. On the Ribbon, click Create and click Form Design
34. Double-click the Properties button of the form
35. In the Property Sheet, click the All tab and change the following characteristics:
    Record Source: LoansContracts
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
36. Right-click the body of the form and click Form Header/Footer
37. Set the Back Color of the Form Header section to Text 1, Lighter 50%
38. Set the Back Color of the Detail section to Background Form
39. Set the Back Color of the Form Footer section to Black, Text 1
40. Save the form as LoanAllocation
41. Change the Caption to Watts' A Loan - Loan Allocation
42. Design the form as follows:

    

    Control	Caption	Name	Other Properties
    Label	Watts' A Loan
    Label	New Loan Allocation
    Label/Text Box	Loan Number	txtLoanNumber
    Label/Text Box	Date Allocated	txtDateAllocated
    Label	Processed By		Back Color: #727272
    Label/Text Box	Employee #:	txtEmployeeNumber
    Text Box		txtEmployeeName
    Label	Processed For Customer		Back Color: #727272
    Label/Text Box	First Name::	txtCustomerFirstName
    Label/Text Box	Last Name:	txtCustomerLastName
    Line			Border Width: 2 pt Border Color: Dark Gray 5
    Combo Box	Loan Type:	cbxLoansTypes	Row Source:
    Label/Text Box	Loan Amount:	txtLoanAmount
    Label/Text Box	Interest Rate:	txtInterestRate
    Label/Text Box	Periods (Months):	txtPeriods
    Button	Evaluate Loan	cmdEvaluateLoan
    Line			Border Width: 2 pt Border Color: Dark Gray 5
    Text Box	Interest Amount:	txtInterestAmount
    Label/Text Box	Future Value:	txtFutureValue
    Label/Text Box	Monthly Payment:	txtMonthlyPayment
    Label/Text Box	Payment Start Date:	txtPaymentStartDate
    Button	Submit	cmdSubmit
    Button	Close	cmdClose

43. On the form, click the txtEmployeeNumber text box
44. In the Property Sheet, click the Event tab and double-click On Lost Focus
45. Click its ellipsis button to open Microsoft Visual Basic
46. On the main menu of Microsoft Visual Basic, click Tools -> References...
47. In the References dialog box, make sure the check box of Microsoft Office 16.0 Access Database Engine Object is checked (if not, check it)
    Implement the Lost Focus event as follows:

    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT FirstName, LastName " & _
                                                     "FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & Me.txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
        End If
        
        rsEmployees.Close
        dbWattsALoan.Close
    End Sub

48. In the Objects combo box, selec cmdEvaluateLoan
49. Implement the event as follows:

    Private Sub cmdEvaluateLoan_Click()
        DoCmd.OpenForm "LoanEvaluation"
    End Sub

50. In the Object combo box, select cmdSubmit
51. Implement the Click event as follows:

    Private Sub cmdSubmit_Click()
        If Not IsDate(txtDateAllocated) Then
            MsgBox "Please enter a valid allocation date, such as the date the loan was approed.", _
                   vbOKOnly, "Watts' A Loan - New Loan Allocation"
            Exit Sub
        End If
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "Please enter the employee number of the clerk who processed or approved the loan.", _
                   vbOKOnly, "Watts' A Loan - New Loan Allocation"
            Exit Sub
        End If
        
        If IsNull(cbxLoansTypes) Then
            MsgBox "Please specify the type of the loan that was processed.", _
                   vbOKOnly, "Watts' A Loan - New Store Item"
            Exit Sub
        End If
        
        Dim emplId As Long
        Dim LoanTypeID As Long
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        Dim rsLoansTypes As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT EmployeeID FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & Me.txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            emplId = rsEmployees!EmployeeID
        End If
        
        Set rsLoansTypes = dbWattsALoan.OpenRecordset("SELECT LoanTypeID FROM LoansTypes " & _
                                                      "WHERE LoanType LIKE '" & Me.cbxLoansTypes & "';")
        
        If rsEmployees.RecordCount > 0 Then
            LoanTypeID = rsLoansTypes!LoanTypeID
        End If
        
        DoCmd.RunSQL "INSERT INTO LoansContracts(LoanNumber, DateAllocated, EmployeeID, CustomerFirstName, CustomerLastName, " & _
                     "                           LoanTypeID, LoanAmount, InterestRate, Periods, " & _
                     "                           InterestAmount, MonthlyPayment, FutureValue, PaymentStartDate) " & _
                     "VALUES('" & txtLoanNumber & "', #" & CDate(txtDateAllocated) & "#, '" & emplId & "', '" & txtCustomerFirstName & _
                     "', '" & txtCustomerLastName & "', '" & LoanTypeID & "', " & CDbl(Nz(txtLoanAmount)) & ", " & _
                     CDbl(Nz(txtInterestRate)) & ", " & CDbl(Nz(txtPeriods)) & ", " & CDbl(Nz(txtInterestAmount)) & ", " & _
                     CDbl(Nz(txtMonthlyPayment)) & ", " & CDbl(Nz(txtFutureValue)) & ", #" & CDate(txtPaymentStartDate) & "#);"
        DoCmd.Close
    End Sub

52. In the Object combo box, select cmdClose
53. Implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

54. Close Microsoft Visual Basic
55. Swicth the form to Form View:

    

56. Close the form
57. When asked whether you want to save, click Yes

Loans Payments

When a loan has been issued, a customer must make monthly payments. To take care of this, we will create a table and related objects.

Practical Learning: Making Loans Payments

1. On the Ribbon, click Create and click Table Design
2. In the top tex box under Field Name, type PaymentID and press Tab
3. Right-click PaymentID and click Primary Key
4. To save the table, press Ctrl + S
5. In the Table Name text box, type Payments
6. Click OK
7. Complete the design of the form as follows:
   
   

   Field Name	Data Type	Field Size	Format	Input Mask	Caption
   PaymentID	AutoNumber				Payment ID
   ReceiptNumber	Short Text	10			Receipt #
   PaymentDate	Date/Time		Long Date	Short Date	Payment Date
   EmployeeID					Employee
   LoanContractID	Number				Loan Contract
   PaymentAmount	Number	Double	Currency		Payment Amount
   Balance	Number	Double	Currency

8. In the Data Type combo box that corresponds to EmployeeID, select Lookup Wizard
9. In the first page of the Lookup Wizard, make sure the first radio button is select. Click Next
10. In the second page of the Lookup Wizard, make sure Table: Employees is selected. Click Next
11. In the third page of the Lookup Wizard, double-click EmployeeNumber, FirstName, LastName, and EmploymentTitle
12. Click Next
13. In the fourth page of the wizard, click Next
14. In the fifth page of the wizard, click Next
15. In the sixth page of the wizard, click Finish
16. Read the text on the message box and click Yes
17. In the bottom section of the table, click the Lookup tab
18. Change the following values:

    Column Heads:  Yes
    Column Widths: 0";0.85";0.85";0.85";2.5"
    List Width:    5.05"

19. Close the table
20. When asked whether you want to save, click Yes
21. On the Ribbon, click Database Tools and click Relationships
22. Relate the tables

    

23. To close the Relationships window, click its Close button
24. On the Ribbon, click Create and click Form Design
25. Double-click the Properties button of the form
26. In the Property Sheet, click the All tab and change the following characteristics:
    Caption: Watts' A Loan - New Payment
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
    Min Max Buttons: Min Enabled
27. Right-click the body of the form and click Form Header/Footer
28. Complete the design of the form as follows:

    

    Control	Caption	Name	Other Properties
    Label:	Watts' A Loan		Fore Color: Background 2, Darker 10%
    Line			Border Color: Accent 2, Lighter 80%
    Label	New Payment		Fore Color: Background 2, Darker 10%
    Label/Text Box:	Receipt #:	txtReceiptNumber
    Label/Text Box:	Payment Date:	txtPaymentDate
    Label	Payment Processed By		Back Color: #727272
    Label/Text Box	Employee #:	txtEmployeeNumber
    Text Box		txtEmployeeName
    Label	Payment Processed For		Back Color: #727272
    Label/Text Box	Loan Number	txtLoanNumber
    Text Box		txtLoanDetails
    Label/Text Box	Amount Paid:	txtAmountPaid
    Label	Balance		Back Color: #727272
    Label/Text Box	Before Payment:	txtBalanceBeforePayment
    Label/Text Box	After Payment:	txtBalanceAfterPayment
    Button	Submit	cmdSubmit
    Button

29. Save the form as NewPayment
30. On the form, click the txtEmployeeNumber text box
31. In the Property Sheet, click the Event tab and double-click On Lost Focus
32. Click its ellipsis button and implement the event as follows:

    Private Sub txtEmployeeNumber_LostFocus()
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT FirstName, LastName " & _
                                                     "FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & Me.txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            txtEmployeeName = rsEmployees!LastName & ", " & rsEmployees!FirstName
        End If
        
        rsEmployees.Close
        dbWattsALoan.Close
    End Sub

33. In the Object combo box, select txtLoanNumber
34. In the Procedure combo box, select Lost Focus
35. Implement the event as follows:

    Private Sub txtLoanNumber_LostFocus()
        Dim loanId As Long
        Dim balance As Double
        Dim rsPayments As Recordset
        Dim dbWattsALoan As Database
        Dim rsLoansAllocations As Recordset
        
        If IsNull(txtLoanNumber) Then
            Exit Sub
        End If
        
        balance = 0
        
        Set dbWattsALoan = CurrentDb
        Set rsLoansAllocations = dbWattsALoan.OpenRecordset("SELECT LoanContractID, " & _
                                                            "       LoanNumber, " & _
                                                            "       CustomerFirstName, " & _
                                                            "       CustomerLastName, " & _
                                                            "       LoanAmount, " & _
                                                            "       MonthlyPayment, " & _
                                                            "       FutureValue " & _
                                                            "FROM   LoansContracts " & _
                                                            "WHERE  LoanNumber = '" & txtLoanNumber & "';")
        
        If rsLoansAllocations.RecordCount > 0 Then
            txtLoanDetails = "Loan granted to " & rsLoansAllocations!CustomerFirstName & ", " & _
                             rsLoansAllocations!CustomerLastName & " for " & _
                             rsLoansAllocations!LoanAmount & " paid at " & _
                             rsLoansAllocations!MonthlyPayment & "/Month"
    
            loanId = rsLoansAllocations!LoanContractID
            balance = rsLoansAllocations!FutureValue
            
            Set rsPayments = dbWattsALoan.OpenRecordset("SELECT Balance " & _
                                                        "FROM   Payments  " & _
                                                        "WHERE  LoanContractID = " & loanId & ";")
                                                        
            If rsPayments.RecordCount > 0 Then
                rsPayments.MoveLast
                
                balance = rsPayments!balance
            End If
    
            txtBalanceBeforePayment = balance
            
            txtAmountPaid = rsLoansAllocations.Fields("MonthlyPayment").Value
            txtBalanceAfterPayment = FormatNumber(CDbl(Nz(txtBalanceBeforePayment)) - CDbl(Nz(txtAmountPaid)))
        End If
        
        rsLoansAllocations.Close
        dbWattsALoan.Close
    End Sub

36. In the Object combo box, select cmdSubmit
37. Implement the event as follows:

    Private Sub cmdSubmit_Click()
        If Not IsDate(txtPaymentDate) Then
            MsgBox "Please enter a valid payment date.", _
                   vbOKOnly, "Watts' A Loan - New Payment"
            Exit Sub
        End If
        
        If IsNull(txtEmployeeNumber) Then
            MsgBox "Please enter the employee number of the clerk who processed the Payment.", _
                   vbOKOnly, "Watts' A Loan - New Loan Allocation"
            Exit Sub
        End If
        
        Dim emplId As Long
        Dim loanId As Long
        Dim dbWattsALoan As Database
        Dim rsEmployees As Recordset
        Dim rsLoansContracts As Recordset
        
        If IsNull(txtEmployeeNumber) Then
            Exit Sub
        End If
        
        Set dbWattsALoan = CurrentDb
        Set rsEmployees = dbWattsALoan.OpenRecordset("SELECT EmployeeID FROM Employees " & _
                                                     "WHERE EmployeeNumber LIKE '" & txtEmployeeNumber & "';")
        
        If rsEmployees.RecordCount > 0 Then
            emplId = rsEmployees!EmployeeID
        End If
        
        Set rsLoansContracts = dbWattsALoan.OpenRecordset("SELECT LoanContractID FROM LoansContracts " & _
                                                          "WHERE LoanNumber LIKE '" & txtLoanNumber & "';")
        
        If rsLoansContracts.RecordCount > 0 Then
            loanId = rsLoansContracts!LoanContractID
        End If
        
        DoCmd.RunSQL "INSERT INTO Payments(ReceiptNumber, PaymentDate, EmployeeID, " & _
                     "                     LoanContractID, PaymentAmount, Balance) " & _
                     "VALUES('" & txtReceiptNumber & "', #" & CDate(txtPaymentDate) & "#, " & _
                     emplId & ", " & loanId & ", " & CDbl(Nz(txtAmountPaid)) & ", " & _
                     CDbl(Nz(Me.txtBalanceAfterPayment)) & ");"
        DoCmd.Close
    End Sub

38. In the Object combo box, select cmdClose and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

39. Close Microsoft Visual Basic and return to Microsoft Access
40. Close the NewPayment form
41. When asked whether you want to save, click Yes

Data Entry

We will now test the database

Practical Learning: Testing the Database

1. In the Navigation Page, under Forms, double-click LoansTypes

   

2. Click Loan Type, type each of the following values and press Tab each time:

   Personal Loan
   Car Financing
   Boat Financing
   Furniture Purchase
   Musical Instrument

3. Close the form
4. In the Navigation Pane, under Forms, double-click Employees

   

5. Create a few records as follows:
   
   

   Employee #	First Name	Last Name	Employee Title
   293-747	Catherine	Watts	Owner - General Manager
   836-486	Thomas	Felton	Accounts Representative
   492-947	Caroline	Wernick	Assistant Manager
   240-750	Catherine	Donato	Accounts Representative
   804-685	Melissa	Browns	Customer Accounts Representative
   429-374	Denise	Leighton	Accounts Manager

6. Close the Employees form
7. In the Navigation Pane, under Forms, double-click LoanAllocation

   

8. Click the Evaluation Loan button

   

9. Enter some values as follows:

   Loan Amount:   2500
   Interest Rate: 15.63
   Periods:       36

   

10. Click the Calculate button:

    

11. In the New Loan Allocation form, in the text boxes and the combo box, type and select the following values:

    Loan Number:        100001
    Date Allocated:     01/18/2021
    Employee #:         429-374
    First Name:         Joanne
    Last Name:          Kennan
    Loan Type:          Personal Loan
    Loan Amount:        2500
    Interest Rate:      15.63
    Periods:            36
    Interest Amount:    1,483.51
    Future Value:       3,983.51
    Monthly Payment:    87.44
    Payment Start Date: 03/01/2021

    

12. Click Submit
13. Create another loan allocation with the following values:

    Loan Number:        100002
    Date Allocated:     01/22/2021
    Employee #:         492-947
    First Name:         Stephen
    Last Name:          Haller
    Loan Type:          Boat Financing
    Loan Amount:        46500
    Interest Rate:      4.95
    Periods:            60
    Interest Amount:    13,027.79
    Future Value:       59,527.79
    Monthly Payment:    876.45
    Payment Start Date: 03/01/2021

    

14. Create another loan with the following values:

    Loan Number:        100003
    Date Allocated:     03/12/2021
    Employee #:         429-374
    First Name:         Annette
    Last Name:          Vargas
    Loan Type:          Furniture Purchase
    Loan Amount:        2258.75
    Interest Rate:      17.35
    Periods:            36
    Interest Amount:    1,528.29
    Future Value:       3,787.04
    Monthly Payment:    80.92
    Payment Start Date: 05/01/2021

    

15. Create another loan with the following values:

    Loan Number:        100004
    Date Allocated:     03/12/2021
    Employee #:         836-486
    First Name:         Gérard
    Last Name:          Maloney
    Loan Type:          Car Financing
    Loan Amount:        22748
    Interest Rate:      10.25
    Periods:            60
    Interest Amount:    15,146.30
    Future Value:       37,894.30
    Monthly Payment:    486.13
    Payment Start Date: 05/01/2021

16. In the Navigation Pane, under Forms, double-click New Payment

    

17. In the text boxes, enter the following numbers:

    Receipt #:      385970
    Payment Date:   03/03/2021
    Employee #:     429-374
    Loan Number:    100001
    Amount Paid:    87.44
    Before Payment: 3,869.52
    After Payment:  3783.28

18. Click the Submit button
19. Create another payment with the following values:

    Receipt #:      953746
    Payment Date:   03/30/2021
    Employee #:     492-947
    Loan Number:    100002
    Amount Paid:    369.12
    Before Payment: 30,348.75
    After Payment:  29,979.63

20. Create another payment with the following values:

    Receipt #:      503940
    Payment Date:   04/30/2021
    Employee #:     836-486
    Loan Number:    100002
    Amount Paid:    369.12
    Before Payment: 29,979.63
    After Payment:  29,610.51

21. Create another payment with the following values:

    Receipt #:      522840
    Payment Date:   05/22/2021
    Employee #:     836-486
    Loan Number:    100003
    Amount Paid:    486.13
    Before Payment: 37,894.30
    After Payment:  37,408.17

22. Create another payment with the following values:

    Receipt #:      184603
    Payment Date:   05/28/2021
    Employee #:     429-374
    Loan Number:    100001
    Amount Paid:    79.58
    Before Payment: 3,654.94
    After Payment:  3,575.36

23. Create another payment with the following values:

    Receipt #:      620381
    Payment Date:   05/28/2021
    Employee #:     429-374
    Loan Number:    100002
    Amount Paid:    369.12
    Before Payment: 29,610.51
    After Payment:  29,241.39

24. Close Microsoft Access

Home	Copyright © 2001-2025, FunctionX	Tuesday 12 December 2024, 10:36	Home