Practical
Learning: Creating Cleaning Orders
|
|
- On the Ribbon, click Create and, in the Queries section, click Query
Design
- On the Show Table dialog box, click Close
- Right-click the Query1 window and click SQL View
- Replace the code in the Query1 window with the following:
CREATE TABLE CleaningOrders
(
ReceiptNumber Counter(100001, 1),
Constraint PK_CleaningOrders Primary Key(ReceiptNumber)
);
- To execute the code, on the Ribbon, click the Run button

- Close the Query1 window
- When asked whether you want to save, click No
- In the Navigation Pane, right-click CleaningOrders and click Design View
- 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 |
|
|
|
- In the top section of the table, set the Data Type of the Item1 field to
Lookup Wizard...
- In the first page of the wizard, make sure the first radio button is
selected and click Next
- In the second page of the wizard, click Table: Items
- Click Next
- In the Available Fields list, double-click ItemName and DefaultPrice
- Click Next
- Click Next
- Click Next
- Accept the column label as Item1 and click Finish
- Click Yes to save the table
- Click the box on the left side of Item1
- Press and hold Shift
- Click the box on the left side of QuantityItem1
- Press Ctrl + C to copy
- Click the first empty box under QuantityItem1
- Press Ctrl + V to paste
- Change the field names to Item2, UnitPriceItem2, and QuantityItem2
respectively
- Click the first empty box under QuantityItem2
- Press Ctrl + V to paste
- Change the new field names to Item3, UnitPriceItem3, and QuantityItem3
respectively
- Click the first empty box under QuantityItem3
- Press Ctrl + V to paste
- Change the new field names to Item4, UnitPriceItem4, and QuantityItem4
respectively
- 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 |
|
|
|
|
- In the top section of the table, set the Data Type of the OrderStatus
field to Lookup Wizard...
- In the first page of the wizard, make sure the first radio button is
selected and click Next
- In the second page of the wizard, click Table: OrderStatus
- Click Next
- In the Available Fields list, double-click OrderStatus
- Click Next
- Click Next
- Click Next
- Accept the label as OrderStatus and click Finish
- Click Yes to save the table
- Close the table
- On the Ribbon, click Create
- In the Forms section, click Form Design
- Right-click the form and click Form Header/Footer
- Right-click the form's tab and click Save
- Set the name to CleaningOrders and press Enter
- Double-click the button at the intersection of rullers
- In the Property Sheet, click All and change the following
characteristics:
Record Source: CleaningOrders Caption: Georgetown Dry
Cleaning Services - Cleaning Orders Dividing Lines: Yes
- In the Tools section, click Add Existing Fields
- Design the form as follows:

|
Control |
Name |
Caption |
Control Sourcce |
Format |
Label |
 |
|
Customer Phone #: |
|
|
Text Box |
 |
CustomerPhone |
|
CustomerPhone |
|
Label |
 |
|
Customer Name: |
|
|
Text Box |
 |
CustomerName |
|
=IIf(IsNull([CustomerPhone]), "",
DLookUp("CustomerName", "Customers", "PhoneNumber = '" &
[CustomerPhone] & "'")) |
|
Label |
 |
|
Date Deposited: |
|
|
Text Box |
 |
DateDeposited |
|
DateDeposited |
|
Label |
 |
|
Time Deposited: |
|
|
Text Box |
 |
TimeDeposited |
|
TimeDeposited |
|
Label |
 |
|
Date Expected: |
|
|
Text Box |
 |
DateExpected |
|
DateExpected |
|
Label |
 |
|
Time Expected: |
|
|
Text Box |
 |
TimeExpected |
|
TimeExpected |
|
Line |
 |
|
|
|
|
Label |
 |
|
Item Type |
|
|
Label |
 |
|
Unit Price |
|
|
Label |
 |
|
Qty |
|
|
Label |
 |
|
Sub-Total |
|
|
Label |
 |
|
Cleaning Order Summary |
|
|
Label |
 |
|
Shirts |
|
|
Text Box |
 |
UnitPriceShirts |
|
UnitPriceShirts |
|
Text Box |
 |
QuantityShirts |
|
QuantityShirts |
|
Text Box |
 |
txtSubTotalShirts |
|
=IIf(IsNull([QuantityShirts]), "", Nz([UnitPriceShirts])
* Nz([QuantityShirts])) |
Fixed |
Label |
 |
|
Receipt #: |
|
|
Text Box |
 |
ReceiptNumber |
|
ReceiptNumber |
|
Label |
 |
|
Pants |
|
|
Text Box |
 |
UnitPricePants |
|
UnitPricePants |
|
Text Box |
 |
QuantityPants |
|
QuantityPants |
|
Text Box |
 |
txtSubTotalPants |
|
=IIf(IsNull([QuantityItem1]), "", Nz([UnitPricePants]) *
Nz([QuantityPants])) |
Fixed |
Combo Box |
 |
Item1 |
|
Item1 |
|
Text Box |
 |
UnitPriceItem1 |
|
UnitPriceItem1 |
|
Text Box |
 |
QuantityItem1 |
|
QuantityItem1 |
|
Text Box |
 |
txtSubTotalItem1 |
|
=IIf(IsNull([QuantityItem1]), "", Nz([UnitPriceItem1]) *
Nz([QuantityItem1])) |
Fixed |
Label |
 |
|
Cleaning Total: |
|
|
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 |
 |
Item2 |
|
Item2 |
|
Text Box |
 |
UnitPriceItem2 |
|
UnitPriceItem2 |
|
Text Box |
 |
QuantityItem2 |
|
QuantityItem2 |
|
Text Box |
 |
txtSubTotalItem2 |
|
=IIf(IsNull([QuantityItem2]), "", Nz([UnitPriceItem2]) *
Nz([QuantityItem2])) |
Fixed |
Label |
 |
|
Tax Rate: |
|
|
Text Box |
 |
TaxRate |
|
TaxRate |
|
Combo Box |
 |
Item3 |
|
Item3 |
|
Text Box |
 |
UnitPriceItem3 |
|
UnitPriceItem3 |
|
Text Box |
 |
QuantityItem3 |
|
QuantityItem3 |
|
Text Box |
 |
txtSubTotalItem3 |
|
=IIf(IsNull([QuantityItem3]), "",
Nz([txtUnitPriceItem3]) * Nz([QuantityItem3])) |
Fixed |
Label |
 |
|
Tax Amount: |
|
|
Text Box |
 |
txtTaxAmount |
|
=CLng(CDbl(Nz([txtCleaningTotal])) * CDbl(Nz([TaxRate]))
* 100) / 100 |
Fixed |
Combo Box |
 |
Item4 |
|
Item4 |
|
Text Box |
 |
UnitPriceItem4 |
|
UnitPriceItem4 |
|
Text Box |
 |
QuantityItem4 |
|
QuantityItem4 |
|
Text Box |
 |
txtSubTotalItem4 |
|
=IIf(IsNull([QuantityItem4]), "", Nz([UnitPriceItem4]) *
Nz([QuantityItem4])) |
Fixed |
Label |
 |
|
Net Price: |
|
|
Text Box |
 |
txtNetPrice |
|
=Nz([txtCleaningTotal]) + Nz([txtTaxAmount]) |
Fixed |
Line |
 |
|
|
|
|
Label |
 |
|
Order Status: |
|
|
Combo Box |
 |
OrderStatus |
|
OrderStatus |
|
Label |
 |
|
Date Picked: |
|
|
Text Box |
 |
DatePickedUp |
|
DatePickedUp |
|
Label |
 |
|
Time Picked: |
|
|
Text Box |
 |
TimePickedUp |
|
TimePickedUp |
|
Label |
 |
|
Notes: |
|
|
Text Box |
 |
txtNotes |
|
|
|
Button |
 |
cmdClose |
Close |
|
|
|
- Double-click the button at the intersection of the rulers.
In the
Properties window, click Event
- Double-click On Current
- Click the ellipsis button

- 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
- In the Object combo box, select txtDateDeposited
- In the Procedure combo box, select AfterUpdate
- 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
- In the Object combo box, select TimeDeposited
- In the Procedure combo box, select AfterUpdate
- Implement the event as follows:
Private Sub txtTimeDeposited_AfterUpdate()
txtDateDeposited_AfterUpdate
End Sub
- Close Microsoft Visual Basic and return to Microsoft Access
- Close the form
- Return to Microsoft Access
- Preview the form

- Save and close the form
- In the Navigation Pane, right-click the CleaningOrders form and click
Copy
- Right-click any area of the Navigation Pane and click Paste
- Set the name to NewCleaningOrder
- Click OK
- In the Navigation Pane, right-click NewCleaningOrder and click Design
View
- Using the Properties window, change the following properties:
Navigation Buttons: No Data Entry: Yes

- Save the form
A simulation allows us to test the tables and form with
possible or actual values.
Practical
Learning: Simulating the Application
|
|
- In the Navigation Pane, double-click the OrdersStatus table
- 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 |
- Close the table
- In the Navigation Pane, double-click the NewCleaningItem form
- 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 |
- Close the NewCleaningItem form
- In the Navigation Pane, double the NewCustomer form

- 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 |
- Close the NewCustomer form
- In the Navigation Pane, double-click the NewCleaningOrder form

- Close the form
- Display the first record

- Navigate to the third record

- Navigate to the fourth record

- Close the form
- When asked whether you want to save, click
Yes
Practical
Learning: Introducing Control's Text
|
|
- From the resources that accompany these
lessons, open the Georgetown Dry Cleaning Services2 database
- In the Navigation Pane, double-click the CleaningOrders form to
open it

- Navigate to the third record

- Navigate to the fourth record

- Right-click
the form's title bar and click Design View
|
|