Home

Example Application: Georgetown Dry Cleaning Services
     

Introduction

Georgetown Dry Cleaning Services is a fictitious business that takes care of cleaning all types of items for customers. Customers bring their items to the store. Depending on the time they leave them, they can get them back the same day or the day after.

We will create a (very) simple application to give us an idea of how to manage such a business.

Practical Learning: Introducing GDCS
  1. Start Microsoft Access
  2. To create a new database, click File Name and type GeorgetownDryCleaningServices
  3. Click Create
  4. On the Ribbon, click File -> Options
  5. In the left list, click Current Database
  6. Click Ovelapping Windows, Compact On Close, and Remove Personal Information...
  7. Click OK
  8. Close Microsoft Access
  9. Re-start Microsoft Access and open the GeorgetownDryCleaningServices database

The Customers

A cleaning order starts with a customer bringing some clothes for cleaning. To prepare and deliver cleaning orders, we will create a table for customers.

Practical Learning: Creating Customers
  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Click the empty box under Field Name and create the following fields:
     
    Field Name Data Type Field Size Caption
    PhoneNumber Text 20 Phone #
    FirstName Text 25 First Name
    LastName Text 25 Last Name
  3. Right-click PhoneNumber and click Primary Key
  4. Right-click the Table1 tab and click Save
  5. Set the Table Name to Customers
  6. Click OK
  7. Click the first empty box under Field Name and type CustomerName
  8. Set its Data Type to Calculated
  9. In the Expression Builder, type LastName + ', ' + FirstName
  10. Click OK
  11. Click the first empty box under Field Name and type Notes
  12. Set its Data Type to Memo
     
    Field Name Data Type Field Size Caption
    PhoneNumber Text 20 Phone #
    FirstName Text 25 First Name
    LastName Text 25 Last Name
    CustomerName Text   Customer Name
    Notes Memo    
  13. Save and close the table
  14. On the Ribbon, click Create and, in the Forms section, click Form Design
  15. Using the Properties window, set its Record Source to Customers
  16. Save the form as Customers
  17. Complete the design as follows:
     
    Georgetown Dry Cleaning Services: Customers
  18. Save and close the Customers form
  19. In the Navigation Pane, right-click the Customers form and click Copy
  20. Right-click any area of the Navigation Pane and click Paste
  21. Set the name to NewCustomer
  22. Click OK
  23. In the Navigation Pane, right-click NewCustomer and click Design View
  24. Using the Properties window, change the following characteristics:
    Navigation Buttons: No
    Data Entry: Yes
     
    Georgetown Dry Cleaning Services: New Customer
  25. Save and close the NewCustomer form

The Current Status of an Order

After a customer has droppped some clothes, employees start processing them. At one time, the items will have been cleaned and wait for the customer. At another time, the customer will come to pick the clothes up. This means that, throughout this process, the status of a cleaning order changes. To keep track of these, we will create a small table.

Practical Learning: Creating Order Status
  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Click the empty box under Field Name and create the following fields:
     
    Field Name Data Type Field Size Caption
    OrderStatus Text 40 Order Status
    Notes Memo    
  3. Right-click OrderStatus and click Primary Key
  4. Right-click Table1 and click Datasheet View
  5. When asked to save the table, click Yes
  6. Set the Name to OrdersStatus
  7. Click OK

The Cleaning Items

Our company cleans various types of items. We could provide empty text boxes in a form and let the clerk type the name of an item that is being cleaned but this solution would require too much typing. Another option is to provide a fixed list of items. The problem with this solution is that the list of items may increase. The most logical option is to create a table of items so that, when necessary, a new item can be added to the list.

Practical Learning: Creating Cleaning Items
  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Click the empty box under Field Name and create the following fields:
     
    Field Name Data Type Field Size Format Caption
    ItemID AutoNumber     Item ID
    ItemName Text 40   Item Name
    DefaultPrice Number Double Fixed Default Price
  3. Right-click ItemID and click Primary Key
  4. Right-click Table1 and click Datasheet View
  5. When asked whether you want to save the table, click Yes
  6. Set the Name to Items
  7. 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 its Record Source to Items
  11. Save the form as CleaningItems
  12. Complete the design as follows:
     
    Georgetown Dry Cleaning Services: Cleaning Items
  13. Save and close the CleaningItems form
  14. In the Navigation Pane, right-click the CleaningItems form and click Copy
  15. Right-click any area of the Navigation Pane and click Paste
  16. Set the name to NewCleaningItem
  17. Click OK
  18. In the Navigation Pane, right-click NewCleaningItem and click Design View
  19. Using the Properties window, change the following properties:
    Navigation Buttons: No
    Data Entry: Yes
     
    Georgetown Dry Cleaning Services: New Cleaning Item
  20. Save and close the NewCleaningItem form

Cleaning Orders

A cleaning order is the central activity of our database. It will keep its information as

  • A (unique) receipt number
  • A way to identity the customer whose cleaning will be processed or has been done
  • Date and time information about the order such as when the customer brought the items to be cleaned, the date/time the items are/were expected
  • The item or list of items to clean

We also need to keep a status of each cleaning order to specify if the items have been dropped, are being cleaning, or have been picked up.
 
 
 

Practical Learning: Creating Cleaning Orders
  1. On the Ribbon, click Create and, in the Queries section, click Query Design
  2. On the Show Table dialog box, click Close
  3. Right-click the Query1 window and click SQL View
  4. Replace the code in the Query1 window with the following: 
    CREATE TABLE CleaningOrders
(
    ReceiptNumber Counter(100001, 1),
    Constraint PK_CleaningOrders Primary Key(ReceiptNumber)
);
  5. To execute the code, on the Ribbon, click the Run button Run
  6. Close the Query1 window
  7. When asked whether you want to save, click No
  8. In the Navigation Pane, right-click CleaningOrders and click Design View
  9. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption Default Value
    ReceiptNumber AutoNumber     Receipt #  
    CustomerPhone   20   Customer Phone  
    DateDeposited Date/Time   Long Date Date Deposited =Date()
    TimeDeposited Date/Time   Medium Time Time Deposited =Time()
    DateExpected Date/Time   Long Date Date Expected  
    TimeExpected Date/Time   Medium Time Time Expected  
    UnitPriceShirts Number Double Fixed Unit Price Shirts 1.25
    QuantityShirts Number Integer   Quantity Shirts  
    UnitPricePants Number Double Fixed Unit Price Pants 1.95
    QuantityPants Number Integer   Quantity Pants  
    Item1          
    UnitPriceItem1 Number Double Fixed    
    QuantityItem1 Number Integer      
  10. In the top section of the table, set the Data Type of the Item1 field to Lookup Wizard...
  11. In the first page of the wizard, make sure the first radio button is selected and click Next
  12. In the second page of the wizard, click Table: Items
  13. Click Next
  14. In the Available Fields list, double-click ItemName and DefaultPrice
  15. Click Next
  16. Click Next
  17. Click Next
  18. Accept the column label as Item1 and click Finish
  19. Click Yes to save the table
  20. Click the box on the left side of Item1
  21. Press and hold Shift
  22. Click the box on the left side of QuantityItem1
  23. Press Ctrl + C to copy
  24. Click the first empty box under QuantityItem1
  25. Press Ctrl + V to paste
  26. Change the field names to Item2, UnitPriceItem2, and QuantityItem2 respectively
  27. Click the first empty box under QuantityItem2
  28. Press Ctrl + V to paste
  29. Change the new field names to Item3, UnitPriceItem3, and QuantityItem3 respectively
  30. Click the first empty box under QuantityItem3
  31. Press Ctrl + V to paste
  32. Change the new field names to Item4, UnitPriceItem4, and QuantityItem4 respectively
  33. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption Default Value
    ReceiptNumber AutoNumber     Receipt #  
    CustomerPhone          
    CustomerName          
    DateDeposited          
    TimeDeposited          
    DateExpected          
    TimeExpected          
    UnitPriceShirts          
    QuantityShirts          
    UnitPricePants          
    QuantityPants          
    Item1          
    UnitPriceItem1          
    QuantityItem1          
    Item2          
    UnitPriceItem2          
    QuantityItem2          
    Item3          
    UnitPriceItem3          
    QuantityItem3          
    Item4          
    UnitPriceItem4          
    QuantityItem4          
    OrderStatus          
    TaxRate Number Double Percent Tax Rate 0.0750
    DatePickedUp Date/Time   Long Date Date Picked Up  
    TimePickedUp Date/Time   Medium Time Time Picked Up  
    Notes Memo        
  34. In the top section of the table, set the Data Type of the OrderStatus field to Lookup Wizard...
  35. In the first page of the wizard, make sure the first radio button is selected and click Next
  36. In the second page of the wizard, click Table: OrderStatus
  37. Click Next
  38. In the Available Fields list, double-click OrderStatus
  39. Click Next
  40. Click Next
  41. Click Next
  42. Accept the label as OrderStatus and click Finish
  43. Click Yes to save the table
  44. Close the table
  45. On the Ribbon, click Create
  46. In the Forms section, click Form Design
  47. Right-click the form and click Form Header/Footer
  48. Right-click the form's tab and click Save
  49. Set the name to CleaningOrders and press Enter
  50. Double-click the button at the intersection of rullers
  51. In the Property Sheet, click All and change the following characteristics:
    Record Source: CleaningOrders
    Caption: Georgetown Dry Cleaning Services - Cleaning Orders
    Dividing Lines: Yes
  52. In the Tools section, click Add Existing Fields
  53. Design the form as follows:
     
    Georgetown Dry Cleaning Services
    Control Name Caption Control Sourcce Format
    Label Label   Customer Phone #:    
    Text Box Text Box CustomerPhone   CustomerPhone  
    Label Label   Customer Name:    
    Text Box Text Box CustomerName   =IIf(IsNull([CustomerPhone]), "", DLookUp("CustomerName", "Customers", "PhoneNumber = '" & [CustomerPhone] & "'"))  
    Label Label   Date Deposited:    
    Text Box Text Box DateDeposited   DateDeposited  
    Label Label   Time Deposited:    
    Text Box Text Box TimeDeposited   TimeDeposited  
    Label Label   Date Expected:    
    Text Box Text Box DateExpected   DateExpected  
    Label Label   Time Expected:    
    Text Box Text Box TimeExpected   TimeExpected  
    Line Line        
    Label Label   Item Type    
    Label Label   Unit Price    
    Label Label   Qty    
    Label Label   Sub-Total    
    Label Label   Cleaning Order Summary    
    Label Label   Shirts    
    Text Box Text Box UnitPriceShirts   UnitPriceShirts  
    Text Box Text Box QuantityShirts   QuantityShirts  
    Text Box Text Box txtSubTotalShirts   =IIf(IsNull([QuantityShirts]), "", Nz([UnitPriceShirts]) * Nz([QuantityShirts])) Fixed
    Label Label   Receipt #:    
    Text Box Text Box ReceiptNumber   ReceiptNumber  
    Label Label   Pants    
    Text Box Text Box UnitPricePants   UnitPricePants  
    Text Box Text Box QuantityPants   QuantityPants  
    Text Box Text Box txtSubTotalPants   =IIf(IsNull([QuantityItem1]), "", Nz([UnitPricePants]) * Nz([QuantityPants])) Fixed
    Combo Box Combo Box Item1   Item1  
    Text Box Text Box UnitPriceItem1   UnitPriceItem1  
    Text Box Text Box QuantityItem1   QuantityItem1  
    Text Box Text Box txtSubTotalItem1   =IIf(IsNull([QuantityItem1]), "", Nz([UnitPriceItem1]) * Nz([QuantityItem1])) Fixed
    Label Label   Cleaning Total:    
    Text Box Text Box txtCleaningTotal   =IIf(IsNull([QuantityShirts]), 0, Nz([txtSubTotalShirts])) + IIf(IsNull([QuantityPants]), 0, Nz([txtSubTotalPants]))+IIf(IsNull([QuantityItem1]), 0, Nz([txtSubTotalItem1])) + IIf(IsNull([QuantityItem2]), 0, Nz([txtSubTotalItem2]))+IIf(IsNull([QuantityItem3]), 0, Nz([txtSubTotalItem3])) + IIf(IsNull([QuantityItem4]), 0, Nz([txtSubTotalItem4])) Fixed
    Combo Box Combo Box Item2   Item2  
    Text Box Text Box UnitPriceItem2   UnitPriceItem2  
    Text Box Text Box QuantityItem2   QuantityItem2  
    Text Box Text Box txtSubTotalItem2   =IIf(IsNull([QuantityItem2]), "", Nz([UnitPriceItem2]) * Nz([QuantityItem2])) Fixed
    Label Label   Tax Rate:    
    Text Box Text Box TaxRate   TaxRate  
    Combo Box Combo Box Item3   Item3  
    Text Box Text Box UnitPriceItem3   UnitPriceItem3  
    Text Box Text Box QuantityItem3   QuantityItem3  
    Text Box Text Box txtSubTotalItem3   =IIf(IsNull([QuantityItem3]), "", Nz([txtUnitPriceItem3]) * Nz([QuantityItem3])) Fixed
    Label Label   Tax Amount:    
    Text Box Text Box txtTaxAmount   =CLng(CDbl(Nz([txtCleaningTotal])) * CDbl(Nz([TaxRate])) * 100) / 100 Fixed
    Combo Box Combo Box Item4   Item4  
    Text Box Text Box UnitPriceItem4   UnitPriceItem4  
    Text Box Text Box QuantityItem4   QuantityItem4  
    Text Box Text Box txtSubTotalItem4   =IIf(IsNull([QuantityItem4]), "", Nz([UnitPriceItem4]) * Nz([QuantityItem4])) Fixed
    Label Label   Net Price:    
    Text Box Text Box txtNetPrice   =Nz([txtCleaningTotal]) + Nz([txtTaxAmount]) Fixed
    Line Line        
    Label Label   Order Status:    
    Combo Box Text Box OrderStatus   OrderStatus  
    Label Label   Date Picked:    
    Text Box Text Box DatePickedUp   DatePickedUp  
    Label Label   Time Picked:    
    Text Box Text Box TimePickedUp   TimePickedUp  
    Label Label   Notes:    
    Text Box Text Box txtNotes      
    Button Button cmdClose Close    
  54. Double-click the button at the intersection of the rulers.
    In the Properties window, click Event
  55. Double-click On Current
  56. Click the ellipsis button Ellipsis
  57. Implement the event as follows: 
    Private Sub Form_Current()
    If OrderStatus = "Processing Cleaning Order" Then
        OrderStatus.BackColor = RGB(128, 0, 0)
        OrderStatus.ForeColor = RGB(255, 255, 0)
    ElseIf OrderStatus = "Ready For Pick Up" Then
        OrderStatus.BackColor = RGB(50, 150, 100)
        OrderStatus.ForeColor = RGB(250, 250, 10)
    Else
        Rem If OrderStatus = "Finalized (Picked Up)" Then
        OrderStatus.BackColor = vbWhite
        OrderStatus.ForeColor = vbBlack
    End If
End Sub
  58. In the Object combo box, select txtDateDeposited
  59. In the Procedure combo box, select AfterUpdate
  60. Implement the event as follows: 
    Private Sub txtDateDeposited_AfterUpdate()
On Error GoTo txtDateDeposited_AfterUpdateError

    Dim DateLeft As Date
    Dim TimeLeft As Date
    Dim Time9AM As Date

    DateLeft = CDate(txtDateDeposited)
    TimeLeft = CDate(txtTimeDeposited)
    Time9AM = TimeSerial(9, 0, 0)
    
    ' If the customer leaves clothes before 9AM...
    If Hour(TimeLeft) <= 9 Then
        ' ... then, regardless of the day,
        ' the clothes should be ready the same day after 5PM
        MsgBox "The clothes will be ready today after 5PM.", _
               vbInformation Or vbOKOnly, _
               "Georgetown Dry Cleaning Services"
        txtDateExpected = DateLeft
        txtTimeExpected = TimeSerial(17, 0, 0)
        ' DateSerial(Year(DateLeft), Month(DateLeft), Day(DateLeft))
    Else
        ' If the clothes are left after 9AM, ...
        ' if today is Saturday (the store is closed on Sunday),
        ' then the clothes will be ready on Monday after 8AM
        If Weekday(DateLeft) = vbSaturday Then
            MsgBox "The clothes will be ready on Monday after 5PM.", _
                   vbInformation Or vbOKOnly, _
                   "Georgetown Dry Cleaning Services"
            txtDateExpected = DateAdd("d", 2, DateLeft)
            txtTimeExpected = DateSerial(17, 0, 0)
        Else
            ' If today is a week (business) day, then the clothes will be ready tomorrow 8AM,
            MsgBox "The clothes will be ready tommorrow after 5M.", _
                   vbInformation Or vbOKOnly, _
                   "Georgetown Dry Cleaning Services"
            txtDateExpected = DateSerial(Year(DateLeft), Month(DateLeft), Day(DateLeft) + 1)
            txtTimeExpected = TimeSerial(17, 0, 0)
        End If
    End If

    Resume txtDateDeposited_AfterUpdateExit
    
txtDateDeposited_AfterUpdateExit:
    Exit Sub
    
txtDateDeposited_AfterUpdateError:
    If Err.Number = 94 Then
        MsgBox "Error #94 - Description: " & Err.Description & vbCrLf & _
               "Make sure you enter the date deposited and the time deposited."
    Else
        MsgBox "Error #" & Err.Number & ": " & " - Description: " & Err.Description
    End If
    
    Resume Next
End Sub
  61. In the Object combo box, select TimeDeposited
  62. In the Procedure combo box, select AfterUpdate
  63. Implement the event as follows: 
    Private Sub txtTimeDeposited_AfterUpdate()
    txtDateDeposited_AfterUpdate
End Sub
  64. Close Microsoft Visual Basic and return to Microsoft Access
  65. Close the form
  66. Return to Microsoft Access
  67. Preview the form
     
    Georgetown Dry Cleaning Services - Cleaning Orders
  68. Save and close the form
  69. In the Navigation Pane, right-click the CleaningOrders form and click Copy
  70. Right-click any area of the Navigation Pane and click Paste
  71. Set the name to NewCleaningOrder
  72. Click OK
  73. In the Navigation Pane, right-click NewCleaningOrder and click Design View
  74. Using the Properties window, change the following properties:
    Navigation Buttons: No
    Data Entry: Yes
     
    Georgetown Dry Cleaning Services - New Cleaning Order
  75. Save the form

Application Simulation

A simulation allows us to test the tables and form with possible or actual values.

Practical Learning: Simulating the Application
  1. In the Navigation Pane, double-click the OrdersStatus table
  2. Create the following records:
     
    Order Status Notes
    Processing Cleaning Order The order is being processed and the items are not yet ready
    Ready For Pick Up The items have been cleaned and the customer can pick them up
    Processing (Problem With Order) This can signal any problem, such as a customer complaining about something
    Finalized (Picked Up) The items have been cleaned and the customer has picked them up
  3. Close the table
  4. In the Navigation Pane, double-click the NewCleaningItem form
  5. Create the following items (most dry cleaning store use the same price for shirts and another price for other types of items; they also have a third different type for comforters; just for fun, we are using different orices in our application):
     
    Item Name Unit Price
    Tie 1.25
    Coat 3.25
    Dress 2.45
    Jacket 3.15
    Swede 6.5
    Sweater 2.3
    Silk Shirt 2.25
    Comforter 19.95
    Women Suit 8.95
    Regular Skirt 2.35
    Men's Suit 2Pc 8.5
    Men's Suit 3Pc 9.95
    Skirt With Hook 2.4
  6. Close the NewCleaningItem form
  7. In the Navigation Pane, double the NewCustomer form
     
    Georgetown Dry Cleaning Services: New Customer
  8. Create the following records:
     
    Phone Number First Name Last Name
    240-210-2844 James Newfield
    202-286-1117 Ahmed Adama
    301-840-0025 Catherine Lopez
    202-290-3740 Elliott Wuah
    301-927-9475 Annette Belsam
    202-692-4079 Edith Nwabugo
  9. Close the NewCustomer form
  10. In the Navigation Pane, double-click the NewCleaningOrder form
     
    Georgetown Dry Cleaning Services - New Cleaning Order
  11. Close the form
  12. Display the first record 
    Georgetown Dry Cleaning Services
  13. Navigate to the third record 
    Georgetown Dry Cleaning Services
  14. Navigate to the fourth record
    Georgetown Dry Cleaning Services
  15. Close the form
  16. When asked whether you want to save, click Yes

Practical LearningPractical Learning: Introducing Control's Text
  1. From the resources that accompany these lessons, open the Georgetown Dry Cleaning Services2 database
  2. In the Navigation Pane, double-click the CleaningOrders form to open it 
    Georgetown Dry Cleaning Services
  3. Navigate to the third record 
    Georgetown Dry Cleaning Services
  4. Navigate to the fourth record 
    Georgetown Dry Cleaning Services
  5. Right-click the form's title bar and click Design View
 
 
   
 
Home Copyright © 2010-2016, FunctionX