Access Work Order Seminar
0. Introduction (10:49)
1. Plan Database (11:33)
Tables
Relationships
Reports
2. Customer Tables (11:58)
CustomerT
LocationT
UnitT
3. Customer Form (11:20)
Make Customer Form
Locations List Box
SQL RowSource
Custom WHERE Condition
Forms!CustomerF!CustomerID
OnCurrent Event
LocationList.Requery
4. Location Form 1 (12:27)
Build Location Form
Customer Combo Box
Open Location From CustomerF
DoCmd.OpenForm Code
Where Condition
OnDblClick Event
5. Location Form 2 (9:03)
Add New Location
Open Form to Add Records
Form Field Default Value
Tab Stop Property
Requery Across Forms
On Error Resume Next
6. Unit Form 1 (12:47)
Build Unit Form
7. Unit Form 2 (9:19)
Limit List of Locations
Change RowSource in VBA
Notes
8. Using Subforms (10:23)
Creating Location SubForm
Creating Unit SubForm
Continuous Forms
Form Header/Footer
9. Customer List (11:00)
Build Customer List Form
Open Customer Button
New Customer Button
10. Work Order Form 1 (7:33)
Build Work Order Table
Design Work Order Form
11. Work Order Form 2 (11:14)
Dynamic Combo Boxes
Locations by Customer
Units by Location
Enabled Property
DropDown Method
SetFocus Method
12. Work Order Form 3 (11:58)
Blanking Combo Boxes
OnCurrent Event
Enable/Disable if Needed
Requery Combo Boxes Again
13. Work Order Form 4 (9:22)
DLOOKUP Name & Address Info
Dim Variable
14. Work Order Form 5 (12:17)
NZ Function
Dealing with NULL problems
Double-click to Open Forms
DoCmd.OpenForm
15. Status, Priority (12:52)
Status Table
Sorted Status Query
Priority Table
Sorted Priority Query
Status, Priority Combo Boxes
Default Combo Values
Default Requested Date
16. Create Work Order (12:57)
Button on Customer Form
Button on Location Form
Button on Unit Form
17. Work Order Query (6:35)
Master Work Order Query
Relationships
Outer Joins
18. Work Order List 1 (18:57)
List of Work Orders
Listbox on Customer Form
Listbox on Location Form
Listbox on Unit Form
Limit to Current Record
Double-click Open Event
OnCurrent Event Updated
19. Work Order List 2 (10:26)
Requery Work Order List
On Error Resume Next
Triple State Check Box
Show Closed Work Orders
Show Open Work Orders
Show Both Closed and Open
20. Work Order List Form (19:48)
Master Work Order List
Show Closed Work Orders
Change Sort by Column Headers
Main Menu Form
21. Labor Form 1 (9:01)
Form to Track Labor Items
Continuous Forms
22. Labor Form 2 (8:53)
Calculate Billable Hours
Rounding Time Values
SUM Total
Page Footer v. Form Footer
DateDiff Function
23. Labor Form 3 (9:41)
Recalculate Hours
Combo Box for Worker
24. Labor Form 4 (14:06)
Modal, Popup Form
Button to Open Labor Form
Calculate Hours on Work Order
25. Labor Form 5 (14:29)
Combo Box Default Worker
Minimum Billable Hours
End Time Earlier than Start Time
26. Materials Form 1 (9:39)
Table, Form for Materials
Calculate Line Totals
27. Materials Form 2 (9:04)
Product Combo Box
Add Product to Materials Form
Column() Values
DLOOKUP
28. Materials Form 3 (8:36)
Calculated Footer Values
Values on Work Order Form
Grey Out Calculated Values
29. Categories (11:42)
CategoryT
Category Junction Table
Many to Many Relationship
Category Subform on Work Order
30. Scheduling 1 (14:37)
Delete Scheduled Date Field
Change to IsScheduled
Schedule Table and Form
31. Scheduling 2 (12:27)
Filter Boxes
Dynamic Form Requery
SQL Statements for Recordsource
32. Scheduling 3 (11:00)
Filter Boxes for Dates
33. Scheduling 4 (16:05)
Schedule Button for Work Order
Mark as Scheduled
MsgBox vbYesNoCancel
Appt Conflict Resolution, Part 1
34. Scheduling 5 (16:37)
Appt Conflict Resolution, Part 2
Upcoming Appointment Preview List
35. Scheduling 6 (19:44)
Next Available Appt Time
Loop to Get Next Appt Time/Date
36. Fix Work Order List (12:32)
DateScheduled Now Missing
Add Show Scheduled Box
Link to Show Open, Unscheduled
37. Inactive Units (9:36)
IsActive Field
Show Active / Inactive Units
38. Search Form 1 (16:04)
Company Name Search
Location Name Search
Person Name Search Part 1
Union Query
39. Search Form 2 (11:51)
Search for Name Anywhere in DB
Custom VBA and SQL Code for Listbox
40. Search Form 3 (9:15)
Open Corresponding Form
Location, Customer, or Unit
41. Search Form 4 (7:06)
Add Phone Number Field to Search
42. Billing 1 (16:15)
MyCompanyT
Default Hourly Rate for All
Default Hourly Rate Per Customer
Add Billable, Invoiced to WO List
Show Completed, Not Invoiced Yet
43. Billing 2 (10:47)
Invoice Table
Invoice Detail Table
Billing Decisions
44. Billing 3 (13:49)
Make Invoice Button
Check to see if Billable
Check for labor or materials
Create InvoiceT record
Recordsets
Create a Recordset
AddNew
Set Field Values
45. Billing 4 (14:29)
Add Line Items to Invoice
Recordset to Loop through Materials
Add Materials to Invoice
46. Billing 5 (8:46)
Add One Line Item for Labor
47. Billing 6 (12:12)
Invoice Detail Form
Invoice Form
Show Invoice Button
Change Caption Dynamically
OnCurrent Event
48. Sales Tax 1 (13:30)
DefaultTaxRate
Adding TaxRate to Invoice
Add IsTaxable to Materials
Taxable Labor
49. Sales Tax 2 (7:27)
Add SalesTax to VBA Invoicing Code
50. Sales Tax 3 (14:57)
TaxableTotal
NonTaxableTotal
IIF Function
Form Footer Calculations
Order Total
51. Order List Form (18:46)
Order List Query
Aggregate Query
Show Totals
Group By
Sum Function
52. Update Status (12:16)
Update the Status Combo
On Error Resume Next
On Error Goto 0
53. Work Order Report 1 (16:53)
Work Order Report Query
Outer v Inner Joins
Work Order Report
Work Order Category Subreport
54. Work Order Report 2 (15:51)
Labor Subreport
55. Work Order Report 3 (25:26)
Materials Subreport
Button to Print 1 Work Order
Limit Work Order List by Dates
Print Batch of Work Orders
56. Printable Invoice (21:49)
Master Invoice Query
Report Grouping
Force New Page
Sum Totals
Printing Invoices
57. Printable Schedules (20:17)
Schedule for All Workers
Schedule for One Worker
Schedules Between Two Dates
58. Review (3:25)
|