Introduction

Bethesda Car Rental is a fictitous company that rents cars to individuals and companies. In this exercise, we will use Microsoft Access to create a database for our company.

Practical Learning: Introducing the Application

1. Start Microsoft Access
2. Click Blank Desktop Database
3. In the File Name text box, type (replace Database1 with) Bethesda Car Rental
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 and, on the list of files, click Bathesda Car Rental

Employees

Employees are people who perform the transactions for an application. We will create a table and forms for them.

Practical Learning: Introducing the Application

1. On the Ribbon, click Create and, in the Table Design
2. While the carret is blinking in the top box under Field Name, type EmployeeID and press Tab
3. Right-click EmployeeID and click Primary Key
4. Complete the design of the table as follows:

   Field Name	Data Type	Field Size	Caption
   EmployeeID	AutoNumber		Employee ID
   EmployeeNumber	Short Text	10	Employee #
   FirstName	Short Text	25	First Name
   LastName	Short Text	25	Last Name
   EmployeeTitle	Short Text	50	Employee Title

5. Close the table
6. When asked whether you want to save it, click Yes
7. Type Employees as the name of the table
8. Click OK
9. On the Ribbon, click Create and, in the Forms section, click Form Design
10. In the Property Sheet, change the following characteristics:
    Record Source: Employees
    Auto Center: Yes
11. Save the form as Employees
12. Design the form as follows:

    

13. Right-click the Close button and click Build Event
14. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

15. Save and close the form

Categories of Cars

To assist customers who want to rent cars, a company has different types or sizes. In our application, we will create a table for categories of cars

Practical Learning: Creating the Cars to Rent

1. On the main menu, click Create and click Table Design
2. Type CategoryID and press Tab
3. Right-click CategoryID and click Primary Key
4. Complete the design of the table as follows:

   Field Name	Data Type	Field Size	Format	Caption
   CategoryID	AutoNumber			Category ID
   Category	Short Text	32
   Daily	Number	Double	Currency
   Weekly	Number	Double	Currency
   Monthly	Number	Double	Currency
   Weekend	Number	Double	Currency

5. Close the table
6. When asked whether you want to save, click Yes
7. Type Categories as the name of the table
8. Click OK
9. On the Ribbon, click Create and click Form Design
10. Using the Properties window, change the following characteristics:
    Record Source: Categories
    Default View: Continuous Forms
    Navigation Buttons: No
    Min Max Buttons: Min Enabled
11. Right-click the body of the form and click Form Header/Footer
12. Save the form as RentalRates
13. Design the form as follows:

    

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

The Cars to Rent

A car rental company rents car. For its business, the company should have a fleet of cars from which to choose when a customer desires a car. In this section, we create the objects that are necessary to manage cars.

Practical Learning: Creating the Cars to Rent

1. On the Ribbon, click Create and click Table Design
2. Type CarID and press Enter
3. Right-click CarID and click Primary Key
4. Complete the design of the table as follows:

   Field Name	Data Type	Field Size	Caption
   CarID	AutoNumber		Car ID
   TagNumber	Short Text	10	Tag Number
   Make	Short Text	40
   Model	Short Text	40
   Passengers	Number	Byte
   Category
   Condition
   Availability

5. To save the table, press Ctrl + S
6. Type Cars as the name of the table
7. Click OK
8. Click Category and press Tab
9. In the corresponding combo box, select Lookup Wizard...
10. In the first page of the Lookup Wizard, click the second radio button

    

11. Click Next
12. In the second page of the wizard, click under Col1 and type Compact
13. Click under Compact and type Economy
14. Click under Economy and type Full Size
15. Click under Full Size and type Mini Van
16. Click under Mini Van and type Passenger Van
17. Click under Passenger Van and type Pickup Truck
18. Click under Pickup Truck and type Standard
19. Click Standard and type SUV
20. Click Next
21. Accept the suggested name of the label as Category and click Finish
22. On the table, click Condition and press Tab
23. In the corresponding Data Type combo box, select Lookup Wizard...
24. In the first page of the Lookup Wizard, click the second radio button: I will type in the values that I want
25. Click Next
26. In the second page of the wizard, click under Col1 and type Unknown
27. Click under Unknown and type Excellent
28. Click under Excellent and type Good Shape
29. Click under Good Shape and type Needs Repair
30. Click under Needs Repair and type Must be Retired
31. Click Next
32. Accept the suggested label name as Condition and click Finish
33. On the table in Design View, click Availability and press Tab
34. Click the arrow of the combo box that corresponds to Availability and select Lookup Wizard...
35. In the first page of the Lookup Wizard, click the I Will Type In The Values That I Want radio button
36. Click Next
37. In the second page of the wizard, click under Col1 and type Other
38. Click under Other and type Rented
39. Click under Rented and type Available
40. Click Next
41. Accept the suggested name of label as Availability and click Finish
42. Close the table
43. Read the message box and click Yes
44. On the Ribbon, click Create and, in the Forms section, click Form Design
45. Using the Properties window, change the Record Source to Cars
46. Save the form as Cars
47. Complete the design of the form as follows:

    

48. Right-click the Close button and click Build Event
49. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:

    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

50. Close the form
51. When asked whether you want to save it, click Yes

Rental Orders

To rent a car to a customer, the company must create a contract. In this section, we will create the objects that are necessary for such a contract.

Practical Learning: Creating Rental Contracts

1. On the Ribbon, click Create and click Table Design
2. Type RentalOrderID and press Tab
3. Right-click RentalOrderID and click Primary Key
4. Complete the design of the table as follows:

   Field Name	Data Type	Field Size	Format	Caption
   RentalOrderID	AutoNumber			Rental Order ID
   ReceiptNumber	Number	Integer		Receipt #
   OrderStartProcessedBy	Short Text	10		Processed By
   OrderEndProcessedBy	Short Text	10		Processed By
   CustomerDrvLicNumber	Short Text	40		Driver's License #
   CustomerName	Short Text	25		First Name
   CustomerAddress	Short Text	100		Address
   CustomerCity	Short Text	40		City
   CustomerState	Short Text	32		State
   CustomerZIPCode	Short Text	16		ZIP Code
   CarTagNumber	Short Text	12		Tag Number
   CarCondition	Short Text	25		Car Condition
   TankLevel	Short Text	12		Tank Level
   MileageStart	Number	Integer		Mileage Start
   MileageEnd	Number	Integer		Mileage End
   TotalMileage	Number	Integer		Total Mileage
   StartDate	Date/Time	Long Date		Start Date
   EndDate	Date/Time	Long Date		End Date
   TotalDays	Number	Integer		Total Days
   RateApplied	Number	Double	Currency	Rate Applied
   TaxRate	Number	Double	Percent	Tax Rate
   TaxAmount	Number	Double	Currency	Tax Amount
   OrderTotal	Number	Double	Currency	Order Total
   OrderStatus	Short Text	25		Order Status

5. To save the table, press Ctrl + S
6. Type RentalOrders as the name of the table
7. Close the table
8. On the Ribbon, click Create and, in the Forms section, click Form Design
9. Using the Property Sheet, change the following characteristics:
   Auto Center: Yes
   Navigation Buttons: No
10. Right-click the form and click Form Header/Footer
11. Save the form as UpdateRentalOrder
12. In the Design tab of the Ribbon, click the Combo Box control and click the middle of the form
13. In the first page of the Combo Box Wizard, click the second radio button: I will type in the values that I want
14. Click Next
15. In the second page of the wizard, click under Col1 and type Unknown
16. Click under Unknown and type Driveable
17. Click under Driveable and type Excellent
18. Click Next
19. In the third page of the wizard, accept the suggested name of the label and click Finish
20. In the Design tab of the Ribbon, click the Combo Box and click the middle of the form
21. In the first page of the Combo Box Wizard, click the second radio button: I will type in the values that I want
22. Click Next
23. In the second page of the wizard, click below Col1 and type Empty and press the down arrow key
24. Under Empty, type 1/4 Empty and press the down arrow key
25. Under 1/4 Empty, type Half and press the down arrow key
26. Under Half, type 3/4 Full and press the down arrow key
27. Under 3/4 Full, type Full
28. Click Next
29. In the third page of the wizard, click Finish
30. In the Design tab of the Ribbon, click the Combo Box and click somewhere in the form
31. In the first page of the Combo Box Wizard, click the second radio button: I will type in the values that I want

    

32. Click Next
33. In the second page of the wizard, click below Col1 and type Reserved and press the down arrow key
34. Under Reserved, type Processing and press the down arrow key
35. Under Processing, type Rented - Car On Road and press the down arrow key
36. Under Rented - Car On Road, type Car Returned/Order Complete
37. Click Next
38. In the third page of the wizard, click Finish
39. Design the form as follows:

    

    Control	Caption	Name	Back Color	Border Color	Other Properties
    Text Box/Label	Receipt #:	txtReceiptNumber
    Button	Find	cmdFind
    Label	Order Start Processed By		#ECD9CA	#7F001F
    Text Box/Label	Employee #:	txtOrderStartProcessedBy
    Text Box/Label		txtOrderStartEmployeeName
    Label	Order End Processed By		#ECD9CA	#7F001F
    Text Box/Label	Employee #:	txtOrderEndProcessedBy
    Text Box/Label		txtOrderEndEmployeeName
    Label	Rental Order Processed For		#ECD9CA	#7F001F
    Label	Car Rented		#ECD9CA	#7F001F
    Rectangle				#000000	Special Effect: Shadowed
    Label/Text Box	First Name:	txtCustomerFirstName
    Label/Text Box	Tag Number:	txtTagNumber
    Label/Text Box	Last Name:	txtCustomerLastName
    Label/Text Box	Make:	txtMake
    Label/Text Box	Address:	txtCustomerAddress
    Label/Text Box	Model:	txtModel
    Label/Text Box	City:	txtCustomerCity
    Label/Text Box	Passengers:	txtPassengers
    Label/Text Box	State:	txtCustomerState
    Label/Combo Box	Condition:	cbxConditions
    Label/Text Box	ZIP Code:	txtCustomerZIPCode
    Label/Combo Box	Tank Level:	cbxTankLevels
    Label	Mileage and Time Information		#ECD9CA	#7F001F
    Label/Text Box	Mileage Start:	txtMileageStart
    Label/Text Box	End:	txtMileageEnd
    Label/Text Box	Total Mileage:	txtTotalMileage			Format: General Number
    Label/Text Box	Start Date:	txtStartDate
    Label/Text Box	End:	txtEndDate
    Text Box	Total Days:	txtTotalDays			Format: General Number
    Label	Cost Summary		#ECD9CA	#7F001F
    Label/Text Box	Rate Applied:	txtRateApplied
    Label/Text Box	Sub Total:	txtSubTotal			Control Source: =Nz([txtRateApplied])*Nz([txtTotalDays])
    Label/Text Box	Tax Rate:	txtTaxRate			Default Value: 0.075
    Label/Text Box	Tax Amount:	txtTaxAmount			Control Source: =CLng(Nz([txtSubTotal])*Nz([txtTaxRate])*100)/100
    Label/Combo Box	Orders Status:	cbxOrdersStatus
    Text Box	Rent Total:	txtRentTotal			Control Source: =Nz([txtSubTotal])+Nz([txtTaxAmount])
    Button	Submit	cmdSubmit
    Button	Reset	cmdReset
    Button	Close	cmdClose

40. On the form, right-click the Find button and click Build Event...
41. In the Chhose Builder dialog box, click Code Builder and click OK
42. On the main menu of Microsoft Visual Basic, click Tools -> References...
43. In the Available References list box, click the check box of Microsoft ActiveX Data Objects 6.1 Library (select the version that is available to you; it should work just fine)
44. Click OK
45. In the Object combo box, select cmdFind
46. Implement the Click event as follows:

    Private Sub cmdFind_Click()
    On Error GoTo cmdFind_Click_Error
    
        Dim rsCars As New ADODB.Recordset
        Dim rsEmployees As New ADODB.Recordset
        Dim rsRentalOrders As New ADODB.Recordset
        
        rsRentalOrders.Open "SELECT * FROM RentalOrders WHERE ReceiptNumber = " & CLng(txtReceiptNumber), _
                            CurrentProject.Connection, _
                            adOpenStatic, _
                            adLockOptimistic, _
                            adCmdText
        
        If IsNull(txtReceiptNumber) Then
            cmdReset_Click
            Exit Sub
        Else
            With rsRentalOrders
                txtOrderStartProcessedBy = .Fields("OrderStartProcessedBy").Value
                txtCustomerDrvLicNumber = .Fields("CustomerDrvLicNumber").Value
                txtCustomerName = .Fields("CustomerName").Value
                txtCustomerAddress = .Fields("CustomerAddress").Value
                txtCustomerCity = .Fields("CustomerCity").Value
                txtCustomerState = .Fields("CustomerState").Value
                txtCustomerZIPCode = .Fields("CustomerZIPCode").Value
                txtCarTagNumber = .Fields("CarTagNumber").Value
                cbxConditions = .Fields("CarCondition").Value
                cbxTankLevels = .Fields("TankLevel").Value
                txtMileageStart = .Fields("MileageStart").Value
                txtStartDate = .Fields("StartDate").Value
                txtRateApplied = .Fields("RateApplied").Value
                cbxOrdersStatus = .Fields("OrderStatus").Value
            End With
        End If
        
        If IsNull(txtOrderStartProcessedBy) Then
            Exit Sub
        Else
            rsEmployees.Open "SELECT * FROM Employees " _
                             , CodeProject.Connection _
                             , adOpenStatic _
                             , adLockOptimistic _
                             , adCmdText
        
            With rsEmployees
                Do While Not .EOF
                    If .Fields("EmployeeNumber").Value = txtOrderStartProcessedBy Then
                        txtOrderStartEmployeeName = .Fields("FirstName").Value & " " & .Fields("LastName").Value & " (" & .Fields("EmployeeTitle").Value & ")"
                        Exit Do
                    End If
                    .MoveNext
                Loop
            End With
        End If
        
        If IsNull(txtCarTagNumber) Then
            Exit Sub
        Else
            rsCars.Open "SELECT ALL * FROM Cars " & _
                        "WHERE TagNumber = '" & txtCarTagNumber & "';", _
                        CodeProject.Connection, _
                        adOpenStatic, _
                        adLockOptimistic, _
                        adCmdText
               
            txtMake = rsCars("Make").Value
            txtModel = rsCars("Model").Value
            txtPassengers = rsCars("Passengers").Value
        End If
        
        rsCars.Close
        rsEmployees.Close
        rsRentalOrders.Close
        
        Set rsCars = Nothing
        Set rsEmployees = Nothing
        Set rsRentalOrders = Nothing
        
        Exit Sub
    
    cmdFind_Click_Error:
        If Err.Number = 3021 Then
            MsgBox "No receipt with that number was found.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Else
            MsgBox "An error occurred when trying to retrieve the car's information.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
    
        Resume Next
    End Sub

47. In the Objects combo box, select txtOrderStartProcessedBy
48. In the Procedure combo box, select LostFocus
49. Implement the event as follows:

    Private Sub txtOrderStartProcessedBy_LostFocus()
    On Error GoTo cmdFind_Click_Error
    
        Dim rsEmployees As New ADODB.Recordset
        
        If IsNull(txtOrderStartProcessedBy) Then
            cmdReset_Click
            Exit Sub
        Else
            rsEmployees.Open "SELECT * FROM Employees " _
                             & "WHERE OrderStartProcessedBy = '" & txtOrderStartProcessedBy & "';" _
                             , CodeProject.Connection _
                             , adOpenStatic _
                             , adLockOptimistic _
                             , adCmdText
        
            With rsEmployees
                Do While Not .EOF
                    If rsEmployees("EmployeeNumber").Value = txtOrderStartProcessedBy Then
                        txtOrderStartEmployeeName = .Fields("FirstName").Value & " " & .Fields("LastName").Value
                        Exit Do
                    End If
                    .MoveNext
                Loop
            End With
        End If
        
        rsEmployees.Close
        
        Set rsEmployees = Nothing
        
        Exit Sub
    
    cmdFind_Click_Error:
        If Err.Number = 3021 Then
            MsgBox "No employee with that number was found.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Else
            MsgBox "An error occurred when trying to retrieve the car's information.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
    
        Resume Next
    End Sub

50. In the Objects combo box, select txtOrderEndProcessedBy
51. In the Procedure combo box, select LostFocus
52. Implement the event as follows:

    Private Sub txtOrderEndProcessedBy_LostFocus()
    On Error GoTo cmdFind_Click_Error
    
        Dim rsEmployees As New ADODB.Recordset
        
        If IsNull(txtOrderStartProcessedBy) Then
            cmdReset_Click
            Exit Sub
        Else
            rsEmployees.Open "SELECT * FROM Employees " _
                             , CodeProject.Connection _
                             , adOpenStatic _
                             , adLockOptimistic _
                             , adCmdText
        
            With rsEmployees
                Do While Not .EOF
                    If rsEmployees("EmployeeNumber").Value = txtOrderEndProcessedBy Then
                        txtOrderEndEmployeeName = .Fields("FirstName").Value & " " & .Fields("LastName").Value
                        Exit Do
                    End If
                    .MoveNext
                Loop
            End With
        End If
        
        rsEmployees.Close
        
        Set rsEmployees = Nothing
        
        Exit Sub
    
    cmdFind_Click_Error:
        If Err.Number = 3021 Then
            MsgBox "No employee with that number was found.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Else
            MsgBox "An error occurred when trying to retrieve the car's information.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
    
        Resume Next
    End Sub

53. In the Object combo box, select txtTagNumber
54. In the Procedure combo box, select LostFocus
55. Implement the event as follows:

    Private Sub txtTagNumber_LostFocus()
    On Error GoTo txtTagNumber_LostFocusError
    
        Dim rsCars As New ADODB.Recordset
        
        If IsNull(txtTagNumber) Then
            Exit Sub
        End If
        
        rsCars.Open "SELECT Make, Model, Passengers " & _
                    "FROM Cars " & _
                    "WHERE TagNumber = '" & txtCarTagNumber & "';", _
                    CodeProject.Connection, _
                    adOpenStatic, _
                    adLockOptimistic, _
                    adCmdText
    
        If IsNull(rsCars("Make")) Then
            MsgBox "Invalid tag number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
            Exit Sub
        Else
            txtMake = rsCars("Make").Value
            txtModel = rsCars("Model").Value
            txtPassengers = rsCars("Doors").Value & " / " & rsCars("Passengers").Value
        End If
        
        Set rsCars = Nothing
        
        Exit Sub
    
    txtTagNumber_LostFocusError:
        If Err.Number = 3021 Then
            MsgBox "No car was found with that tag number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Else
            MsgBox "An error occurred when trying to retrieve the car's information.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
        
        Resume Next
    End Sub

56. In the Object combo box, select txtMileageEnd
57. In the Procedure combo box, select LostFocus
58. Implement the event as follows:

    Private Sub txtMileageEnd_LostFocus()
        If IsNull(txtMileageEnd) Then
            Exit Sub
        End If
        
        txtTotalMileage = CStr(CLng(txtMileageEnd) - CLng(txtMileageStart))
    End Sub

59. Right-click the Reset button and click Build Event...
60. Double-click Code Builder
61. Implement the event as follows:

    Private Sub cmdReset_Click()
        txtReceiptNumber = ""
        txtOrderStartProcessedBy = ""
        txtOrderStartEmployeeName = ""
        txtOrderEndProcessedBy = ""
        txtOrderEndEmployeeName = ""
        txtCustomerDrvLicNumber = ""
        txtCustomerName = ""
        txtCustomerAddress = ""
        txtCustomerCity = ""
        txtCustomerState = ""
        txtCustomerZIPCode = ""
        txtCarTagNumber = ""
        txtMake = ""
        txtModel = ""
        txtPassengers = ""
        cbxConditions = ""
        cbxTankLevels = ""
        txtMileageStart = ""
        txtMileageEnd = ""
        txtTotalMileage = ""
        txtStartDate = Date
        txtEndDate = Date
        txtTotalDays = ""
        txtRateApplied = ""
        cbxOrdersStatus = ""
    End Sub

62. In the Object combo box, select cmdSubmit
63. Implement the event as follows:

    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmit_ClickError
    
        Dim rsRentalOrders As ADODB.Recordset
        
        Set rsRentalOrders = New ADODB.Recordset
        rsRentalOrders.Open "SELECT * FROM RentalOrders " & _
                            "WHERE ReceiptNumber = " & CLng(txtReceiptNumber), _
                            CurrentProject.Connection, _
                            adOpenStatic, _
                            adLockOptimistic, _
                            adCmdText
        
        If IsNull(txtReceiptNumber) Then
            MsgBox "You must enter a valid receipt number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Exit Sub
        End If
        
        With rsRentalOrders
            .Fields("OrderStartProcessedBy").Value = txtOrderStartProcessedBy
            .Fields("OrderEndProcessedBy").Value = txtOrderEndProcessedBy
            .Fields("CustomerDrvLicNumber").Value = txtCustomerDrvLicNumber
            .Fields("CustomerName").Value = txtCustomerName
            .Fields("CustomerAddress").Value = txtCustomerAddress
            .Fields("CustomerCity").Value = txtCustomerCity
            .Fields("CustomerState").Value = txtCustomerState
            .Fields("CustomerZIPCode").Value = txtCustomerZIPCode
            .Fields("CarTagNumber").Value = txtCarTagNumber
            .Fields("CarCondition").Value = cbxConditions
            .Fields("TankLevel").Value = cbxTankLevels
            .Fields("MileageStart").Value = txtMileageStart
            .Fields("MileageEnd").Value = txtMileageEnd
            .Fields("TotalMileage").Value = txtTotalMileage
            .Fields("StartDate").Value = txtStartDate
            .Fields("EndDate").Value = txtEndDate
            .Fields("TotalDays").Value = txtTotalDays
            .Fields("RateApplied").Value = txtRateApplied
            .Fields("TaxRate").Value = txtTaxRate
            .Fields("OrderStatus").Value = cbxOrdersStatus
            .Update
        End With
        
        MsgBox "The customer's record has been updated.", _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
        
        cmdReset_Click
        
        rsRentalOrders.Close
        Set rsRentalOrders = Nothing
    
        Exit Sub
        
    cmdSubmit_ClickError:
        MsgBox "An error occurred when trying to update the customer's rental order. " & _
               "Please report the error as follows." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Resume Next
    End Sub

64. In the Object combo box, select cmdClose
65. Implement the event as follows:

    Private Sub cmdClose_Click()
    On Error GoTo cmdClose_ClickError
    
        DoCmd.Close
    
        Exit Sub
    
    cmdClose_ClickError:
        MsgBox "An error occured as follows." & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Message: " & Err.Description, _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
                
        Resume Next
    End Sub

66. Close Microsoft Visual Basic and return to Microsoft Access
67. Close the form
68. When asked whether you want to save, click Yes
69. In the Navigation Pane, right-click UpdateRentalOrder and click Copy
70. Right-click any section of the Navigation Pane and click Paste
71. Type NewRentalOrder as the name of the new form
72. Click OK
73. In the Navigation Pane, right-click NewRentalOrder and click Design View
74. Change the design of the form as follows (you will mostly delete some controls that are not necessary, then you will adjust the design):

    

75. Right-click the Submit button and click Build Event
76. In the Objects combo box (top-left) of Microsoft Visual Basic, select txtOrderStartProcessedBy
77. In the Events combo box (top-right), select LostFocus
78. Implement the event as follows:

    Private Sub txtOrderStartProcessedBy_LostFocus()
    On Error GoTo txtOrderStartProcessedBy_LostFocusError
    
        Dim rsEmployees As New ADODB.Recordset
        ' This variable will be used to find if the user
        ' entered a valid employee number
        Dim EmployeeFound As Boolean
        
        If IsNull(txtOrderStartProcessedBy) Then
            Exit Sub
        End If
        
        ' To start, assume that we haven't found an employee
        EmployeeFound = False
        
        rsEmployees.Open "Employees", _
                         CodeProject.Connection, _
                         adOpenStatic, _
                         adLockOptimistic, _
                         adCmdTable
    
        If IsNull(rsEmployees) Then
            Rem ("FullName")
            MsgBox "Invalid employee number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
            Exit Sub
        Else
            ' We will refer to the recordset many times
            With rsEmployees
            ' Check each employee record from the first to the last
                Do While Not .EOF
                    ' Every time you get to the EmployeeNumber column,
                    ' if its value is the same as the one entered
                    ' in the Employee #...
                    If rsEmployees("EmployeeNumber").Value = txtOrderStartProcessedBy Then
                        ' ... display its full name
                        txtOrderStartProcessedByName = .Fields("FirstName").Value & " " & .Fields("LastName").Value
                        ' Make a note that we found the employee number
                        EmployeeFound = True
                    End If
                    .MoveNext
                Loop
            End With
        
            ' If no employee was found, let the user know
            If EmployeeFound = False Then
                txtOrderStartProcessedBy = ""
                MsgBox "There is no employee with that number.", _
                       vbOKOnly Or vbInformation, "Bethesda Car Rental"
            End If
        End If
        
        Set rsEmployees = Nothing
        
        Exit Sub
    
    txtOrderStartProcessedBy_LostFocusError:
        If Err.Number = 3021 Then
            MsgBox "No employee was found with that number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Else
            MsgBox "An error occurred when trying to get the employee's information.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
        
        Resume Next
    End Sub

79. In the Objects combo box, select cmdReset
80. Change the code of the event as follows:

    Private Sub cmdReset_Click()
        txtReceiptNumber = ""
        txtOrderStartProcessedBy = ""
        txtOrderStartProcessedByName = ""
        txtCustomerDrvLicNumber = ""
        txtCustomerName = ""
        txtCustomerAddress = ""
        txtCustomerCity = ""
        txtCustomerState = ""
        txtCustomerZIPCode = ""
        txtCarTagNumber = ""
        txtMake = ""
        txtModel = ""
        txtPassengers = ""
        cbxConditions = ""
        cbxTankLevels = ""
        txtMileageStart = ""
        txtStartDate = Date
        txtRateApplied = ""
        cbxOrdersStatus = ""
    End Sub

81. In the Code Editor, find the "Private Sub cmdSubmit_Click()" line and change its event as follows:

    Private Sub cmdSubmit_Click()
    On Error GoTo cmdSubmit_ClickError
    
        Dim rsRentalOrders As ADODB.Recordset
        
        Set rsRentalOrders = New ADODB.Recordset
        rsRentalOrders.Open "RentalOrders", _
                            CurrentProject.Connection, _
                            adOpenStatic, _
                            adLockOptimistic
        
        With rsRentalOrders
            If .Supports(adAddNew) Then
                .AddNew
                .Fields("OrderStartProcessedBy").Value = txtOrderStartProcessedBy
                .Fields("CustomerName").Value = txtCustomerName
                .Fields("CustomerAddress").Value = txtCustomerAddress
                .Fields("CustomerCity").Value = txtCustomerCity
                .Fields("CustomerState").Value = txtCustomerState
                .Fields("CustomerZIPCode").Value = txtCustomerZIPCode
                .Fields("CarTagNumber").Value = txtCarTagNumber
                .Fields("CarCondition").Value = cbxConditions
                .Fields("TankLevel").Value = cbxTankLevels
                .Fields("MileageStart").Value = txtMileageStart
                .Fields("StartDate").Value = txtStartDate
                .Fields("RateApplied").Value = CDbl(Nz(txtRateApplied))
                .Fields("OrderStatus").Value = cbxOrdersStatus
                .Update
            End If
        End With
        
        MsgBox "The new rental order has been processed and submitted.", _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
        
        rsRentalOrders.Close
        Set rsRentalOrders = Nothing
    
        cmdReset_Click
        Exit Sub
        
    cmdSubmit_ClickError:
        MsgBox "An error occurred when trying to create the new rental order. " & _
               "Please report the error as follows." & vbCrLf & _
               "Error #:     " & Err.Number & vbCrLf & _
               "Description: " & Err.Description, _
                vbOKOnly Or vbInformation, "Bethesda Car Rental"
                
        Resume Next
    End Sub

82. Close Microsoft Visual Basic and return to Microsoft Access
83. Close the form
84. When asked whether you want to save, click Yes

Testing the Application

We will test with application with a few values.

Practical Learning: Simulating a Car Rental Business

1. In the Navigation Pane, double-click the Employees form
2. Create the following records:

   Employee #	First Name	Last Name	Title
   92735	Jeffrey	Leucart	General Manager
   29268	Kathleen	Rawley	Technician
   73948	Allison	Garlow	Accounts Associates
   40508	David	Stillson	Accounts Manager
   24793	Michelle	Taylor	Accounts Associates
   20480	Peter	Futterman	Accounts Associates
   72084	Georgia	Rosen	Customer Service Representative
   38240	Karen	Blakney	Accounts Associates

3. Close the Employees form
4. In the Navigation Pane, double-click the Rental Rates form
5. Create the following records:

   Category	Daily	Weekly	Monthly	Weekend
   Economy	34.95	28.75	24.95	24.95
   Compact	38.95	32.75	28.95	28.95
   Standard	45.95	39.75	35.95	34.95
   Full Size	50.00	45.00	42.55	38.95
   Mini Van	55.00	50.00	44.95	42.95
   SUV	56.95	52.95	44.95	42.95
   Truck	62.95	52.75	46.95	44.95
   Grand Van	69.95	64.75	52.75	49.95

6. Close the Employees form
7. In the Navigation Pane, double-click the Cars form
8. Create the following records:

   Tag Number	Make	Model	Passengers	Category	Condition	Availability
   8AL8033	Toyota	Corolla LE	5	Compact	Excellent	Available
   4AF9284	Ford	F-150 Reg Cap 4X4	4	Pickup Truck	Other	Available
   6AP2749	Toyota	Corolla LE	5	Compact	Excellent	Rented
   2MD8382	Toyota	4Runner	5	SUV	Needs Repair	Available
   VGT927	GMC	Sierra 1500	5	Pickup Truck	Excellent	Available
   3AD9283	Toyota	Yaris	5	Economy	Good	Rented
   8AE9294	Lincoln	MKT 3.5L	5	Full Size	Excellent	Available
   KLT840	Ford	E-350 XL	15	Passenger Van	Excellent	Available
   2AM9952	Ford	Fiesta SE	5	Economy	Good	Available
   6AD8274	Mazda	CX-9	5	Mini Van	Driveable	Available
   ADG279	GMC	Acadia SLE	5	SUV	Excellent	Rented
   1AD8049	Dodge	Charger	5	Standard	Excellent	Available
   9MD3974	Toyota	Sienna LE FWD	8	Passenger Van	Needs Repair	Rented
   5AJ9274	BMW	528i	5	Full Size	Excellent	Available
   GTH295	Kia	Rio EX	5	Economy	Other	Available
   8AT2408	Mazda	Miata MX-5	2	Compact	Driveable	Available
   6AP2486	Fiat	500	4	Economy	Excellent	Available
   2AL9485	Chrysler	200	2	Compact	Excellent	Available
   DFP924	Honda	Odyssey	8	Passenger Van	Needs Repair	Available
   KER204	Ford	Focus RS	5	Compact	Good	Needs Repair
   8AR9374	Honda	Accord LX	5	Standard	Other	Rented
   5MD2084	Buick	Enclave	5	Mini Van	Excellent	Available
   BND927	Nissan	Leaf	5	Economy	Excellent	Available
   8AG3584	Chrysler	Pacifica	8	Passenger Van	Driveable	Available
   8AL7394	Ford	F-250 SD Reg Cab 4X4	5	Pickup Truck	Good	Available
   4MD2840	Chevrolet	2500 LS	15	Passenger Van	Excellent	Rented
   G249580	Nissan	Sentra SR	5	Compact	Excellent	Available
   3AK7397	Chrysler	200	2	Compact	Driveable	Available

   

9. Close the Cars form
10. Close the Employees form
11. In the Navigation Pane, double-click the New Rental Order form

    

12. Close the RentalRates form
13. Create a record with the following values:
    
    

    Receipt #:	374683
    Employee #:	20480
    Driver's License #:	270-384-850
    Customer Name:	Merten Fuhler
    Address:	6800 Haxell Crt
    City:	Alexandria
    State:	VA
    ZIP Code	22314
    Tag Number:	8AG3584
    Condition:	Excellent
    Tank Level:	Empty
    Mileage Start:	12728
    Start Date:	04/10/2022
    Rate Applied:	78.55
    Order Status:	Rented - Vehicle With Customer"

    

14. Click the Submit button
15. Create another record with the following value:
    
    

    Receipt #:	648842
    Employee #:	24793
    Driver's License #:	826-384-584
    Customer Name:	Joan Altman
    Address:	3725 South Dakota Ave NW
    City:	Washington
    State:	DC
    ZIP Code	20012
    Tag Number:	KER204
    Condition:	Driveable
    Tank Level:	3/4 Full
    Mileage Start:	24715
    Start Date:	04/14/2022
    Rate Applied:	62.95
    Order Status:	Rented - Vehicle With Customer

16. Click the Submit button
17. Create another record with the following values:
    
    

    Receipt #:	147930
    Employee #:	38240
    Driver's License #:	947-957-395
    Customer Name:	Thomas Filder
    Address:	4905 Herrenden Street
    City:	Arlington
    State:	VA
    ZIP Code	22204
    Tag Number:	8AL8033
    Condition:	Excellent
    Tank Level:	Full
    Mileage Start:	6064
    Start Date:	04/14/2022
    Rate Applied:	34.95
    Order Status:	Rented - Vehicle With Customer

18. Click the Submit button
19. Close the New Rental Order form
20. In the Navigation Pane, double-click the Update Rental Order form

    

21. In the Receipt # text box, type 374683
22. Click Find

    

23. Change the following values, ignoring any information that is not given:
    

    End Processed By:	24793
    Customer Name:	Martin Buhler
    Address:	6800 Haxell Court
    Tank Level:	Half Tank
    Mileage End:	13022
    End Date:	04/15/2022
    Total Days:	4
    Rate Applied:	69.95
    Tax Rate:	7.75%
    Order Status:	Rental Order Complete

    

24. Click the Submit button
25. In the Receipt # text box, type 147930
26. Click Find
27. Change the following values, ignoring any information that is not given:
    

    End Processed By:	40508
    Mileage End:	6298
    End Date:	04/17/2022
    Total Days:	3
    Tax Rate:	7.75%
    Order Status:	Car Returned/Order Complete

28. Click the Submit button
29. Close the Update Rental Order form
30. Close Microsoft Access

Home	Copyright © 2005-2022, FunctionX, Inc	Tuesday 17 May 2022	Home