Home

Example Application: Bethesda Car Rental
     

Introduction

Bethesda Car Rental is a fictitious company that rents cars (vehicles) to customers. The company has many employees who take care of cars, customers, related transactions, and other maintenance tasks.
   

To rent a car, a customer must come to the store, fill out any application to provide the necessary information, select a car to rent, and use it for the desired period.

Practical LearningPractical Learning: Starting the Application
  1. Start Microsoft Access
  2. In the File Name text box, type (replace Database1 with) BethesdaCarRental
  3. Click Create
  4. On the Ribbon, click File and click Options
  5. In the left click, click Current Database
  6. Click Overlapping Windows
  7. Click OK
  8. Click OK
  9. Close Microsoft Access and re-open it with the BethesdaCarRental2 database
  10. On the Ribbon, click Database Tools and click Visual Basic
  11. On the main menu of Microsoft Visual Basic, click Tools -> References...
  12. Scroll down in the Available References list box until you find Microsoft ActiveX Data Objects...
    Double-click the one with one with the highest version:
     
    References
  13. Click OK
  14. Return to Microsoft Access

The Categories of Cars

The cars are provided in various categories from small (compact) to trucks, from mini vans and SUVs (sport utility vehicles) to large vans of many seats. Instead of storing a constant list of categories, we will create a table for them.

Practical LearningPractical Learning: Creating the Categories of Cars
  1. On the Ribbon, click Create
  2. In the Tables section, click Table Design
  3. Create the fields of the table as follows:
     
    Field Name Data Type Field Size
    Category Text 30
    Notes Memo  
  4. Right-click Category and click Primary Key
  5. To switch the table to Datasheet View, in the Views section of the Ribbon, click the View button Datasheet View
  6. When asked whether you want to save the table, click Yes
  7. Type Categories and click OK
  8. Close the table
  9. On the Ribbon, click Create and, in the Forms section, click Form Design
  10. Using the Properties window, set the Record Source to Categories
  11. Save the form as Categories
  12. Complete its design as follows:
     
    Bethesda Car Rental - Cars Categories
  13. Save and close the Categories form

The Cars Rented to Customers

The company has many cars that customers can rent. Customers have different needs, desires, and requirements. Therefore, the company has various cars of different categories as we mentioned in the previous section. Before renting a car, a customer may ask various questions about the available cars. The employee helping the customer must then check the company's inventory of cars to see which one suits a customer.

The record of each car must provide all the necessary information to let a customer choose the necessary one. To assist with this, we will create a list of cars.

Practical LearningPractical Learning: Creating the Cars
  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Create the fields of the table as follows:
     
    Field Name Data Type Caption Field Size
    TagNumber Text Tag Number 20
    Category Text   30
    Make Text   40
    Model Text   40
    Doors Number   Integer
    Passengers Number   Integer
    Condition Text   40
    Available Text   10
    Pictures Attachment    
    Notes Memo    
  3. Right-click TagNumber and click Primary Key
  4. Right-click the title bar or tab of the table and click Save
  5. Type Cars and click OK
  6. Set the Data Type of the Category column to Lookup Wizard...
  7. In the first page of the wizard, make sure the first radio button is selected and click Next
  8. In the second page of the wizard, click Table: Categories
  9. Click Next
  10. In the third page of the wizard, double-click Category
  11. Click Next
  12. Click Next
  13. Click Finish
  14. When asked to save the table, click Yes
  15. Set the Data Type of the Available column to Lookup Wizard...
  16. In the first page of the wizard, click the second radio button
  17. Click Next
  18. Under Col1, type Yes and press the down arrow key
  19. Type No
  20. Click Next
  21. Click Finish
  22. Close the table
  23. On the Ribbon, click Create and, in the Forms section, click Form Design
  24. Using the Properties window, set the Record Source to Cars
  25. Save the form as Cars
  26. Design the form as follows:
     
    Bethesda Car Rental - Cars
  27. Save and close the Cars form

The Rental Rates

As mentioned already, each car is considered as belonging to a certain category. The price of renting a car depends on its category. Furthermore, customers rent cars for different periods. There are people who need a car for one or more days during the week. There are people who need a car for the weekend. And there are people who need a car for a long period, such as a month or an undetermined period based on the customer's circumstances.

We will create a list of prices based on categories and periods. An employee can then refer to such a table to let a customer know how much it would cost to rent a certain car.

Practical LearningPractical Learning: Creating a Table in ADO
  1. On the Ribbon, click Create and, in the Forms section, click Form Design
  2. From the Controls section of the Ribbon, click the Button Button and click the form. If the wizard starts, click Cancel
  3. Using the Properties window, change the following characteristics of the button:
    Name: cmdCreateRentalRates
    Caption: Create Rental Rates
  4. Right-click the button and click Build Event...
  5. Double-click Code Builder
  6. Implement the event as follows: 
    Private Sub cmdCreateRentalRates_Click()
    Dim dbConnection As New ADODB.Connection
    
    Set dbConnection = CurrentProject.Connection
    
    dbConnection.Execute "CREATE TABLE RentalRates" & _
                        "(" & _
                        "   Category Text(32), " & _
                        "   Daily Double, " & _
                        "   Weekly Double, " & _
                        "   Monthly Double, " & _
                        "   Weekend Double " & _
                        ");"
        
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Economy', 34.95, 28.75, 24.95, 24.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Compact', 38.95, 32.75, 28.95, 28.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Standard', 45.95, 39.75, 35.95, 34.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Full Size', 50.00, 45.00, 42.55, 38.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Mini Van', 55.00, 50.00, 44.95, 42.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('SUV', 56.95, 52.95, 44.95, 42.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Truck', 62.95, 52.75, 46.95, 44.95);"
    dbConnection.Execute "INSERT INTO RentalRates(Category, Daily, Weekly, Monthly, Weekend)" & _
                        "VALUES('Grand Van', 69.95, 64.75, 52.75, 49.95);"
                        
    dbConnection.Close
    Set dbConnection = Nothing

    Application.RefreshDatabaseWindow
End Sub
  7. Return to Microsoft Access
  8. Swith the form to Form View
  9. Click the Create Rental Rates button
  10. Close the form
  11. When asked whether you want to save, click No
  12. On the Ribbon, click Create and, in the Forms section, click Form Design
  13. Using the Properties window, change the following characteristics:
    Record Source: RentalRates
    Default View: Continuous Forms
    Navigation Buttons: No
  14. Save the form as RentalRates
  15. Design the form as follows:
     
    Bethesda Car Rental - Rental Rates
  16. Save and close the form

The Employees of the Company

Employees are staff members who use and manage the database. We will use a simple list with minimal information such as the name and title. Each employee must be identified with a unique employee number. Also, we will use a feature new in Microsoft Access 2010 to create a field that represents the full name of the employee. This means that this database cannot be used in versions of Microsoft Access prior to 2010.

Practical LearningPractical Learning: Creating Employees
  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Create the fields of the table as follows:
     
    Field Name Data Type Caption Field Size
    EmployeeNumber Text Employee Number 20
    FirstName Text First Name 25
    LastName Text Last Name 25
    FullName Calculated    
  3. After setting the data type of FullName to Calculated, in the Expression Builder, type LastName & ', ' & FirstName
  4. Click OK
  5. Complete the definition of the table as follows:
     
    Field Name Data Type Caption Field Size
    EmployeeNumber      
    FirstName      
    LastName      
    FullName Text Full Name  
    Title Text   50
    Notes Memo    
  6. Right-click EmployeeNumber and click Primary Key
  7. Close the ttable
  8. When asked whether you want to save it, click Yes
  9. Type Employees and click OK
  10. On the Ribbon, click Create and, in the Forms section, click Form Design
  11. Using the Properties window, set the Record Source to Employees
  12. Save the form as Employees
  13. Design the form as follows:
     
    Employees
  14. Save and close the Employees form

The Customers

Customers are individuals who rent a car for a fee. To rent a car, besides having a driver's license, a customer must provide certain minimum information, then select a car. We will use only just some information such as a name and an address. Usually there is more information than that but we will not need it.

Practical LearningPractical Learning: Creating Customers
  1. On the Ribbon, click Create and, in the Forms section, click Form Design
  2. Save the form as NewCustomer
  3. Save and close the form
  4. Using the Properties window, change the following characteristics:
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
  5. Design the form as follows:
     
    Bethesda Car Rental - New Customer
    Control Name Caption
    Text Box Text Box txtDrvLicNumber Driver's Lic. #:
    Text Box Text Box txtFirstName First Name
    Text Box Text Box txtLastName Last Name:
    Text Box Text Box txtAddress Address:
    Text Box Text Box txtCity City:
    Text Box Text Box txtState State:
    Text Box Text Box txtZIPCode ZIP Code:
    Text Box Text Box txtNotes Notes:
    Button Button cmdSubmit Submit
    Button Button cmdReset Reset
    Button Button cmdClose Close
  6. Right-click the Reset button and click Build Event...
  7. Double-click Code Builder
  8. Implement the event as follows: 
    Private Sub cmdReset_Click()
    txtDrvLicNumber = ""
    txtFirstName = ""
    txtLastName = ""
    txtAddress = ""
    txtCity = ""
    txtState = ""
    txtZIPCode = ""
    txtNotes = ""
End Sub
  9. Return to Microsoft Access
  10. Close the NewCustomer form
  11. When asked whether you want to save, click Yes
  12. On the Ribbon, click Create and, in the Forms section, click Form Design
  13. From the Controls section of the Ribbon, click the Button Button and click the form. If the wizard starts, click Cancel
  14. Using the Properties window, change the following characteristics of the button:
    Name: cmdCreateCustomers
    Caption: Create Customers
  15. Right-click the button and click Build Event...
  16. Double-click Code Builder
  17. Implement the event as follows: 
    Private Sub cmdCreateCustomers_Click()
    Dim dbConnection As New ADODB.Connection
    
    Set dbConnection = CurrentProject.Connection
    
    dbConnection.Execute "CREATE TABLE Customers" & _
                        "(" & _
                        "   CustomerNumber Counter(10001, 1), " & _
                        "   DrvLicNumber Text(30), " & _
                        "   FirstName Text(25), " & _
                        "   LastName Text(25), " & _
                        "   Address Text(60), " & _
                        "   City Text(50), " & _
                        "   State Text(40)," & _
                        "   ZIPCode Text(20)," & _
                        "   Notes Memo, " & _
                        "   CONSTRAINT PK_Customers PRIMARY KEY(CustomerNumber)" & _
                        ");"
    
    dbConnection.Close 
    Set dbConnection = Nothing
    Application.RefreshDatabaseWindow
End Sub
  18. Return to Microsoft Access
  19. Switch the form to Form View
  20. Click the Create Customers button
  21. Close the form
  22. When asked whether you want to save the table, click No
  23. In the Navigation Pane, right-click Customers and click Design View
  24. Right-click Address under Field Name and click Insert Rows
  25. In the new empty box, type FullName
  26. Set its Data Type to Calculated
  27. In the Expression Builder, type LastName & ', ' & FirstName
  28. Click OK
     
    Bethesda Car Rental - Customers
  29. Save and close the Customers table
  30. In the Navigation Pane, right-click NewCustomer and click Design View
  31. On the form, right-click the Submit button and click Build Event...
  32. Double-click Code Builder
  33. Implement the event as follows: 
    Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_ClickError

    Dim rsCustomers As ADODB.Recordset
    
    Set rsCustomers = New ADODB.Recordset
    
    rsCustomers.Open "Customers", _
                     CurrentProject.Connection, _
                     adOpenStatic, _
                     adLockOptimistic
    
    If IsNull(txtDrvLicNumber) Then
        MsgBox "You must enter the customer's driver's license number.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
	Exit Sub
    End If
    
    With rsCustomers
        If .Supports(adAddNew) Then
            .AddNew
            .Fields("DrvLicNumber").Value = txtDrvLicNumber
            .Fields("FirstName").Value = txtFirstName
            .Fields("LastName").Value = txtLastName
            .Fields("Address").Value = txtAddress
            .Fields("City").Value = txtCity
            .Fields("State").Value = txtState
            .Fields("ZIPCode").Value = txtZIPCode
            .Fields("Notes").Value = txtNotes
            .Update
        End If
    End With
    
    MsgBox "The customer's record has been created.", _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
    
    cmdReset_Click
    rsCustomers.Close
    Set rsCustomers = Nothing

    Exit Sub
    
cmdSubmit_ClickError:
    MsgBox "An error occurred when trying to create the customer. " & _
           "Please report the error as follows." & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
    Resume Next
End Sub
  34. In the Object combo box, select cmdClose
  35. 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
  36. Close Microsoft Visual Basic and return to Microsoft Access
  37. Close the NewCustomer form
  38. When asked whether you want to save, click Yes
 

 

 
 
 
 

Rental Order Processing

Rental order processing is done in various stages:

  • A customer has to be registered
  • The customer must select a car or be allocated one
  • The customer must take over the car and drive away

After these steps, the order must have a certain status but the order is not complete. To keep track of the information in this stage, we will create a form for new rental processing.

After using the car, the customer must bring back the car. This would be the next and usually the last stage of the order. This time, the final information of the order is entered. To keep track of this, we will create another form that is used to update the order.

To keep track of all the stages of renting a car, we will create a table for order processing.

Practical LearningPractical Learning: Processing Rental Orders
  1. On the Ribbon, click Create and, in the Forms section, click Form Design
  2. Save the form as UpdateRentalOrder
  3. Using the Properties window, change the following characteristics:
    Auto Center: Yes
    Navigation Buttons: No
    Dividing Lines: Yes
  4. Design the form as follows:

    Bethesda Car Rental - Update Rental Order

    Control Name Caption Other Properties
    Text Box Text Box txtReceiptNumber Receipt #:  
    Button Button cmdFind Find  
    Text Box Text Box txtEmployeeNumber Employee #:  
    Text Box Text Box txtEmployeeName    
    Text Box Text Box txtDrvLicNumber Drv. Lic. #:  
    Text Box Text Box txtTagNumber Tag Number:  
    Text Box Text Box txtCustomerName Full Name:  
    Text Box Text Box txtMake Make:  
    Text Box Text Box txtCustomerAddress Address:  
    Text Box Text Box txtModel Model:  
    Text Box Text Box txtCity City:  
    Text Box Text Box txtDoorsPassengers Doors/Seats:  
    Text Box Text Box txtState State:  
    Combo Box Combo Box cbxConditions Condition: Column Count: 1
    Column Widths: 1"
    List Width: 1"
    Row Source: "Excellent";"Driveable";"Unknown"
    Row Source Type: Value List
    Text Box Text Box txtZIPCode ZIP Code:  
    Combo Box Combo Box cbxTankLevels Tank Levels: Column Count: 1
    Column Widths: 1"
    List Width: 1"
    Row Source: "Empty";"1/4 Empty";"Half";"3/4 Full";"Full"
    Row Source Type: Value List
    Text Box Text Box txtMileageStart Mileage Start:  
    Text Box Text Box txtMileageEnd End:  
    Text Box Text Box txtTotalMileage Total Miles: Format: General Number
    Text Box Text Box txtStartDate Start Date:  
    Text Box Text Box txtEndDate End:  
    Text Box Text Box txtTotalDays Total Days: Format: General Number
    Text Box Text Box txtRateApplied Rate Applied:  
    Text Box Text Box txtSubTotal Sub Total: Control Source: =Nz([txtRateApplied])*Nz([txtTotalDays])
    Text Box Text Box txtTaxRate Tax Rate: Default Value: 0.075
    Text Box Text Box txtTaxAmount Tax Amount: Control Source: =CLng(Nz([txtSubTotal])*Nz([txtTaxRate])*100)/100
    Combo Box Combo Box cbxOrdersStatus Orders Status: Column Count: 1
    Column Widths: 1"
    List Width: 1"
    Row Source: "Reserved";"Processing";"Rented - Car On Road";"Car Returned/Order Complete"
    Row Source Type: Value List
    Text Box Text Box txtRentTotal Rent Total: Control Source: =Nz([txtSubTotal])+Nz([txtTaxAmount])
    Text Box Text Box txtNotes Notes:  
    Button Button cmdSubmit Submit  
    Button Button cmdReset Reset  
    Button Button cmdClose Close  
  5. Right-click the Reset button and click Build Event...
  6. Double-click Code Builder
  7. Implement the event as follows: 
    Private Sub cmdReset_Click()
    txtReceiptNumber = ""
    txtEmployeeNumber = ""
    txtEmployeeName = ""
    txtDrvLicNumber = ""
    txtCustomerName = ""
    txtCustomerAddress = ""
    txtCustomerCity = ""
    txtCustomerState = ""
    txtCustomerZIPCode = ""
    txtTagNumber = ""
    txtMake = ""
    txtModel = ""
    txtDoorsPassengers = ""
    cbxConditions = ""
    cbxTankLevels = ""
    txtMileageStart = ""
    txtMileageEnd = ""
    txtTotalMileage = ""
    txtStartDate = Date
    txtEndDate = Date
    txtTotalDays = ""
    txtRateApplied = ""
    cbxOrdersStatus = ""
    txtNotes = ""
End Sub
  8. Close Microsoft Visual Basic and return to Microsoft Access
     
    Bethesda Car Rental - Update Rental Order
  9. Close the form
  10. When asked whether you want to save, click Yes
  11. In the Navigation Pane, right-click UpdateRentalOrder and click Copy
  12. Right-click any section of the Navigation Pane and click PasteForm Design
  13. Type NewRentalOrder as the name of the new form
  14. Click OK
  15. In the Navigation Pane, right-click NewRentalOrder and click Design View
  16. Change the design of the form as follows (you will mostly delete some controls that are not necessary, then you will adjust the design):
     
    Bethesda Car Rental - New Rental Order
  17. Right-click the Reset button and click Build Event
  18. Change the code of the event as follows: 
    Private Sub cmdReset_Click()
    txtEmployeeNumber = ""
    txtEmployeeName = ""
    txtDrvLicNumber = ""
    txtCustomerName = ""
    txtCustomerAddress = ""
    txtCustomerCity = ""
    txtCustomerState = ""
    txtCustomerZIPCode = ""
    txtTagNumber = ""
    txtMake = ""
    txtModel = ""
    txtDoorsPassengers = ""
    cbxConditions = ""
    cbxTankLevels = ""
    txtMileageStart = ""
    txtStartDate = Date
    txtRateApplied = ""
    cbxOrdersStatus = ""
    txtNotes = ""
End Sub
  19. Return to Microsoft Access
  20. Close the form
  21. When asked whether you want to save, click Yes
  22. On the Ribbon, click Create and, in the Forms section, click Form Design
  23. From the Controls section of the Ribbon, click the Button Button and click the form. If the wizard starts, click Cancel
  24. Using the Properties window, change the following characteristics of the button:
    Name: cmdCreateRentalOrders
    Caption: Create Rental Orders
  25. Right-click the button and click Build Event...
  26. Double-click Code Builder
  27. Implement the event as follows: 
    Private Sub cmdCreateRentalOrders_Click()
    Dim dbConnection As New ADODB.Connection
    
    Set dbConnection = CurrentProject.Connection
    
    dbConnection.Execute "CREATE TABLE RentalOrders" & _
                        "(" & _
                        "   ReceiptNumber Counter(100001, 1), " & _
                        "   EmployeeNumber Text(20), " & _
                        "   DrvLicNumber Text(30), " & _
                        "   TagNumber Text(20), " & _
                        "   CarCondition Text(50), TankLevel Text(30), " & _
                        "   MileageStart Integer, MileageEnd Integer, " & _
                        "   TotalMileage Integer, " & _
                        "   StartDate Date, EndDate Date, TotalDays Integer, " & _
                        "   RateApplied Double, " & _
                        "   TaxRate Double Default 0.0750, " & _
                        "   OrderStatus Text(40) Default 'Unknown', " & _
                        "   Notes Memo, " & _
                        "   CONSTRAINT FK_Employees Foreign Key(EmployeeNumber)  " & _
                        "      REFERENCES Employees(EmployeeNumber), " & _
                        "   CONSTRAINT FK_Cars Foreign Key(TagNumber)  " & _
                        "      REFERENCES Cars(TagNumber), " & _
                        "   CONSTRAINT PK_RentalOrders PRIMARY KEY(ReceiptNumber)" & _
                        ");"
    
    dbConnection.Close 
    Set dbConnection = Nothing
    Application.RefreshDatabaseWindow
End Sub
  28. Return to Microsoft Access
  29. Switch the form to Form View
  30. Click the Create Rental Orders button
  31. Close the form
  32. When asked whether you want to save the table, click No
  33. In the Navigation Pane, right-click NewRentalOrder and click Design View
  34. On the form, double-click the Employee # text box
  35. In the Properties window, click Event and double-click On Lost Focus
  36. Click its ellipsis button and implement the event as follows: 
    Private Sub txtEmployeeNumber_LostFocus()
On Error GoTo txtEmployeeNumber_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 there is no employee number, don't do anything
    If IsNull(txtEmployeeNumber) 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
        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 = txtEmployeeNumber Then
                    ' ... display its full name
                    txtEmployeeName = .Fields("FullName").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
            txtEmployeeName = ""
            MsgBox "There is no employee with that number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
    End If
    
    Set rsEmployees = Nothing
    
    Exit Sub

txtEmployeeNumber_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
  37. In the Object combo box, select txtDrvLicNumber
  38. In the Procedure combo box, select LostFocus
  39. Implement the event as follows: 
    Private Sub txtDrvLicNumber_LostFocus()
On Error GoTo txtDrvLicNumber_LostFocusError

    Dim rsCustomers As New ADODB.Recordset
    ' This variable will be used to find if the user
    ' entered a valid employee number
    Dim CustomerFound As Boolean
    
    If IsNull(txtDrvLicNumber) Then
        Exit Sub
    End If
    
    ' To start, assume that we haven't found an employee
    CustomerFound = False
    
    rsCustomers.Open "Customers", _
                     CodeProject.Connection, _
                     adOpenStatic, _
                     adLockOptimistic, _
                     adCmdTable

    If IsNull(rsCustomers) Then
        MsgBox "Invalid customer number.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
        Exit Sub
    Else
        With rsCustomers
            Do
                If rsCustomers("DrvLicNumber").Value = txtDrvLicNumber Then
                    txtCustomerName = .Fields("FullName").Value
                    txtCustomerAddress = .Fields("Address").Value
                    txtCustomerCity = .Fields("City").Value
                    txtCustomerState = .Fields("State").Value
                    txtCustomerZIPCode = .Fields("ZIPCode").Value
                    CustomerFound = True
                End If
                .MoveNext
            Loop While Not .EOF
        End With
    
        ' If no employee was found, let the user know
        If CustomerFound = False Then
	    txtDrvLicNumber = ""
            txtCustomerName = ""
            txtCustomerAddress = ""
            txtCustomerCity = ""
            txtCustomerState = ""
            txtCustomerZIPCode = ""
            
            MsgBox "There is no customer with that number.", _
                   vbOKOnly Or vbInformation, "Bethesda Car Rental"
        End If
    End If
    
    Set rsCustomers = Nothing
    
    Exit Sub

txtDrvLicNumber_LostFocusError:
    If Err.Number = 3021 Then
        MsgBox "No employee was found with that number.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
    Else
        MsgBox "There was an error when trying to retrieve the customer's information.", _
               vbOKOnly Or vbInformation, "Bethesda Car Rental"
    End If
    
    Resume Next
End Sub
  40. In the Object combo box, select txtTagNumber
  41. In the Procedure combo box, select LostFocus
  42. 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, Doors, Passengers " & _
                "FROM Cars " & _
                "WHERE TagNumber = '" & txtTagNumber & "';", _
                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
        txtDoorsPassengers = 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
  43. In the Object combo box, select cmdSubmit
  44. 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 "RentalOrders", _
                        CurrentProject.Connection, _
                        adOpenStatic, _
                        adLockOptimistic
    
    With rsRentalOrders
        If .Supports(adAddNew) Then
            .AddNew
            .Fields("EmployeeNumber").Value = txtEmployeeNumber
            .Fields("DrvLicNumber").Value = txtDrvLicNumber
            .Fields("TagNumber").Value = txtTagNumber
            .Fields("CarCondition").Value = cbxConditions
            .Fields("TankLevel").Value = cbxTankLevels
            .Fields("MileageStart").Value = txtMileageStart
            .Fields("StartDate").Value = txtStartDate
            .Fields("RateApplied").Value = txtRateApplied
            .Fields("OrderStatus").Value = cbxOrdersStatus
            .Fields("Notes").Value = txtNotes
            .Update
        End If
    End With
    
    MsgBox "The new rental order has been processed and submitted.", _
            vbOKOnly Or vbInformation, "Bethesda Car Rental"
    
    cmdReset_Click
    rsRentalOrders.Close
    Set rsRentalOrders = Nothing

    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
  45. In the Object combo box, select cmdClose
  46. 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
  47. Return to Microsoft Access
  48. Close the NewRentalOrder form
  49. When asked whether you want to save, click Yes
  50. In the Navigation Pane, right-click UpdateRentalOrder and click Design View
  51. On the form, right-click the Find button and click Build Event...
  52. Double-click Code Builder
  53. Implement the Click event as follows: 
    Private Sub cmdFind_Click()
On Error GoTo cmdFind_ClickError

    Dim rsCars As New ADODB.Recordset
    Dim rsEmployees As New ADODB.Recordset
    Dim rsCustomers As New ADODB.Recordset
    Dim rsRentalOrders As New ADODB.Recordset
    
    rsRentalOrders.Open "SELECT * FROM RentalOrders " & _
			"WHERE ReceiptNumber = " & CLng(Me.txtReceiptNumber), _
                        CurrentProject.Connection, _
                        adOpenStatic, _
                        adLockOptimistic, _
                        adCmdText
    
    If IsNull(txtReceiptNumber) Then
        Exit Sub
    Else
        With rsRentalOrders
            txtEmployeeNumber = .Fields("EmployeeNumber").Value
            txtDrvLicNumber = .Fields("DrvLicNumber").Value
            txtTagNumber = .Fields("TagNumber").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
            txtNotes = .Fields("Notes").Value
        End With
    
        rsEmployees.Open "SELECT * FROM Employees " & _
                         "WHERE EmployeeNumber = '" & txtEmployeeNumber & "';", _
                         CodeProject.Connection, _
                         adOpenStatic, _
                         adLockOptimistic, _
                         adCmdText
    
        With rsEmployees
            Do While Not .EOF
                If rsEmployees("EmployeeNumber").Value = txtEmployeeNumber Then
                    txtEmployeeName = .Fields("FullName").Value
                    Exit Do
                End If
                .MoveNext
            Loop
        End With
    
        rsCustomers.Open "SELECT ALL * FROM Customers " & _
                         "WHERE DrvLicNumber = '" & txtDrvLicNumber & "'", _
                         CodeProject.Connection, _
                         adOpenStatic, _
                         adLockOptimistic, _
                         adCmdText
        With rsCustomers
            Do
                If rsCustomers("DrvLicNumber").Value = txtDrvLicNumber Then
                    txtCustomerName = .Fields("FullName").Value
                    txtCustomerAddress = .Fields("Address").Value
                    txtCustomerCity = .Fields("City").Value
                    txtCustomerState = .Fields("State").Value
                    txtCustomerZIPCode = .Fields("ZIPCode").Value
                    Exit Do
                End If
                .MoveNext
            Loop While Not .EOF
        End With
    
        rsCars.Open "SELECT ALL * FROM Cars " & _
                    "WHERE TagNumber = '" & txtTagNumber & "';", _
                    CodeProject.Connection, _
                    adOpenStatic, _
                    adLockOptimistic, _
                    adCmdText
           
        txtMake = rsCars("Make").Value
        txtModel = rsCars("Model").Value
        txtDoorsPassengers = rsCars("Doors").Value & " / " & rsCars("Passengers").Value
        
    End If
    
    rsCars.Close
    rsEmployees.Close
    rsCustomers.Close
    rsRentalOrders.Close
    
    Set rsCars = Nothing
    Set rsEmployees = Nothing
    Set rsCustomers = Nothing
    Set rsRentalOrders = Nothing
    
    Exit Sub

cmdFind_ClickError:
    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
  54. In the Object combo box, select txtMileageEnd
  55. In the Procedure combo box, select Lost Focus
  56. 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
  57. In the Object combo box, select cmdSubmit
  58. 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(Me.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("EmployeeNumber").Value = txtEmployeeNumber
        .Fields("DrvLicNumber").Value = txtDrvLicNumber
        .Fields("TagNumber").Value = txtTagNumber
        .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
        .Fields("Notes").Value = txtNotes
        .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
  59. Close Microsoft Visual Basic and return to Microsoft Access
  60. Close the UpdateRentalOrder form
  61. When asked whether you want to save, click Yes

Simulating the Application

A simulation allows us to test the functionality of the application. We will test the forms with possible values that an employee would use.

Practical LearningPractical Learning: Testing the Application
  1. In the Navigation Pane, double-click the Employees form
  2. Create the records using the following values:
     
    EmployeeNumber First Name Last Name Title
    48058 Daniel Keller General Manager
    96350 Margareth Gremore Accounts Associates
    28005 Patrick Starr Accounts Associates
    27479 William Schenke Accounts Associates
    62094 Laura Walters Accounts Manager
    94286 Sonia Wallers Accounts Associates
  3. Close the Employees form
  4. In the Navigation Pane, double-click NewCustomer
  5. Create the following records so that, after creating each row, click the Submit button:
     
    Driver's Lic. # First Name Last Name Address City State ZIP Code
    P244-957-279 William Post 808 Lennard Rd Takoma Park MD 20910
    182-37-4059 Myriam Angel 4206 L St NW Washington DC 20004
    C930-240-057 Justine Chisohlm 11366 Sansuit Drv Rockville MD 20853
    138-42-059 Ann Hill 8522 Glenton Ave Alexandria VA  
    K973-040-775 Larry Kean 1044 Hannarden Rd Silver Spring MD 20906
    257-49-0405 Maureen  Dorryan 6260 Odenton Rd NE Washington DC 20012 
  6. Close the NewCustomer form
  7. In the Navigation Pane, double-click the Categories form
  8. Create the records as follows:
     
    Category
    Economy
    Compact
    Standard
    Full Size
    Mini Van
    SUV
    Truck
    Grand Van
  9. Close the Categories form
  10. In the Navigation Pane, double-click the Cars form
  11. Create the records for cars as follows (set all conditions to Excellent and all available to Yes) (the pictures of cars are provided in lessons resources; to add the pictures, right-click the Pictures placeholder and click Manage Attachments, then click Add, locate the picture, select it, and click Open):
     
    Tag Number Category Make Model Doors Passengers
    4FF975 Standard Honda Accord 4 4
    6DL882 Economy Honda Civic 4 4
    6DR840 Truck Ford F-150 2 2
    3AH579 Compact Kia Rio 4 4
    8DL248 Full Size Mercedes-Benz C350 4 4
    7ZZ846 Compact Lamborghini Gallardo 2 2
    6DG224 SUV Honda CR-V 4 4
    9FB200 Mini Van Toyota Sienna 4 7
    9DT273 Compact Mazda Miata 2 2
    5FA208 Standard Ford Taurus 4 4
    2EG270 Mini Van Volvo XC70 4 5
    8EN294 Economy Honda Civic 4 4
    6DQ208 Compact Kia Rio 4 4
    8FG882 Standard Honda Accord 4 4
    2LR927 Truck Ford F-150 2 4
    9BE425 Grand Van Chevrolet Express 3 20
    1CH249 Mini Van Toyota Sienna 4 7
    5DB274 Compact Nissan Sentra 4 4
    6FH225 Full Size Buick Lacrosse 4 4
    4FP208 SUV Acura RDX 4 4
    8CT208 Truck Chevrolet Silverado 2 4

    Bethesda Car Rental - Cars
     
    Bethesda Car Rental - Cars
     
    Bethesda Car Rental - Cars
  12. Close the Cars form
  13. In the Navigation Pane, double-click the RentalRates formm
     
    Bethesda Car Rental - Rental Rates
  14. In the Navigation Pane, double-click NewRentalOrder
  15. Create a record with the following values, ignoring any information that is not given:
     
    Employee #: 28005
    Drv. Lic. #: C930-240-057
    Tag Number: 4FP208
    Condition: Excellentt
    Tank Level: Full
    Mileage Start: 104077
    Start Date: 01/16/2012
    Rate Applied: 56.95
    Order Status: Rented - Car On Roadd
    Notes: The customer plans to rent the car for the week.

  16. Click the Submit buttonn
     
    Bethesda Car Rental - New Rental Order
  17. Create another record with the following values and ignore any iniformation that is not given::
     
    Employee #: 62094
    Drv. Lic #: 182-37-4059
    Tag Number: 8CT208
    Condition: Driveable
    Tank Level: 1/4 Empty
    Mileage Start: 20268
    Start Date: 01/20/2012
    Rate Applied: 44.95
    Order Status: Rented - Car On Road
  18. Click the Submit buttonn
  19. Create another record with the following values and ignore any iniformation that is not given::
     
    Employee #: 94286
    Drv. Lic. #: C930-240-057
    Tag Number: 1CH249
    Condition: Excellent
    Tank Level: Half
    Mileage Start: 68422
    Start Date: 03/06/2012
    Rate Applied: 55.00
    Order Status: Rented - Car On Road
  20. Click the Submit buttonn
  21. Close the NewRentalOrder formm
  22. Close the RentalRates formm
  23. In the Navigation Pane, double-click UpdateRentalOrderr
  24. In the Receipt # text box, typee 100002
  25. Click Findd
  26. Change the following values, ignoring any information that is not given::
     
    Tank Level: Half
    Mileage End: 20416
    End Date: 01/22/2012
    Total Days: 2
    Order Status: Car Returned/Order Complete
  27. Click the Submit buttonn
     
    Bethesda Car Rental - Updating a Rental Order
  28. In the Receipt # text box, typee 100001
  29. Click Find
  30. Change the values as follows:
     
    Mileage End: 20982
    End Date: 01/21/2012
    Total Days: 5
    Order Status: Car Returned/Order Complete
  31. Click the Submit button
  32. In the Receipt # text box, typee 100003
  33. Click Find
  34. Create another record with the following values and ignore any iniformation that is not given:
     
    Tank Level: Full
    Mileage End: 68683
    End Date: 03/09/2012
    Total Days: 3
    Order Status: Car Returned/Order Complete
  35. Click the Submit button
  36. Close the UpdateRentalOrder form

Practical LearningPractical Learning: Ending the LessonPractical Learning: Ending the Lesson
  • Close Microsoft Access
 
 
   
 
Home Copyright © 2010-2016, FunctionX