Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Advanced Level 2    dismiss
 
 

Access Work Order Seminar
Build a Database to Run Your Service Business

 

 

Although the title of this course says "Work Order Seminar," it's much more than that. You will learn everything you need to build a database to run a service-oriented business using Microsoft Access.

This seminar came about because one of my customers had a need to track work orders for his business, managing maintenance for apartment complexes. All of my Access database lessons to date have been designed for more of a retail-type establishment (basic point of sale and inventory). So, I decided to build a database seminar tailored for service businesses. While many of the concepts are similar, the construction of the database is quite different.

Click on the videos below for more information on exactly what's covered in this seminar. The first video is a preview covering the general outline of the seminar. The second video is an in-depth lesson summary showing each lesson's topics.

Seminar Preview Lesson Summary
AccessLearningZone.com AccessLearningZone.com
10 minutes 15 minutes

   

After you watch the preview videos above,
click here to download the database we build in this
seminar so you can see everything that is included.

 


 

 
Seminars - Access Work Orders
Description: Learn how to build a database to run a service business.
Versions: I will use Access 2007, however most of the lessons are valid for all versions of Access back to 2000. I will show any differences between 2007 and 2003.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 329 very helpful
Running Time: 12 hours, 23 minutes
Cost: $219 - Order multiple courses to receive a discount up to 50% off
Includes a sample, customizable, working database, as built in class.

 

This seminar has several main goals. You will:

1. Track customers with multiple locations
2. Generate work orders with unlimited categories
3. Schedule employees and subcontractors, avoiding conflicts
4. Track job costs, including materials and labor
5. Invoice your customers, and calculate sales tax
6. Design a comprehenive Search Form
to find records
7. Create printable reports for your business
 

We will begin by planning our database, and determining all of the different tables, queries, forms, and reports that we'll need. Each of our customers can have multiple locations, and each location can have multiple units. Think of a company that manages different apartment complexes. The company is the client. Each apartment complex is a location. Each unit in that apartment can be tracked separately - with a complete history.

 

Then we'll create the Work Order Form so that we can track work orders for each customer, location, or even unit separately. The work orders can have an unlimited number of categories (plumbing, electrical, painting, etc.) plus a status, priority, separate contact information, and so on. We'll track whether each work order is billable, has been scheduled, and has been invoiced.

 

We'll generate a printable copy of the work order:

 

We will track Labor for each work order. Your workers can enter a start time and an end time, and the database will automatically calculate the number of hours (which you can edit if you want). You can enter a description, notes, and whether or not each bit of labor is taxable.

 

You can also enter Materials for each work order. You can type them in manually, or select from a table containing your commonly used products which will store unit price, cost, and other information. And, of course, you can track which items are taxable.

 

 

We develop a comprehensive Work Order List showing all of the work orders, their statuses, completion dates, and other information. You can filter this list based on multiple criteria including dates and invoicing status. We'll also create links to perform commonly-used filters, such as "show me all of the work orders that are completed but have not been invoiced yet."

 

We will learn how to schedule appointments for all of our workers, whether they're contractors or employees. You can select a worker, then the form will show you all of his upcoming appointments - so you don't schedule a conflict. You can click on the "Next Available Appointment" button to automatically select his next free time slot. Of course, if you do double-book a worker, the database will yell at you. And of course, we'll make printable schedules you can hand out to your workers.

 

We will create a comprehensive Search Form for our database, so you can search for records based on company name, location, first name, last name, or phone number. Using the techniques I will show you in class, you can search on ANY fields that you want to. Just add them to the form and update the code.

 

You will learn how to generate invoices with the click of one button. Once the data has all been entered into the work order form, just click the "Make Invoice" button and all of the information will be transferred to the invoice form.

 

Again, just click one button, and you can print the invoice. Now you're ready to fold it, put it in an envelope, and mail it on its way.

 

Now, this seminar does stand alone. You don't need any other resources to build the database that I build in this class. However, there is a good deal of VBA (Visual Basic for Applications) programming in this seminar. I will explain everything that I cover enough so that you can follow along, however it will help you tremendously to have a solid background in developing Access databases before taking this course. I would recommend taking at least my Basic and Intermediate Access courses before this one. See the pre-requisites listed in the box above.

 

 

Again, this seminar is perfect for anyone who wants to learn how to build a Microsoft Access database to run a service-oriented business. You will be able to track customers, work orders, scheduling, and more, when you're finished with this seminar. If you are interested in building a database for a retail business (customers, contacts, invoicing, products, inventory control, etc.) then you should take my normal Access classes.

This seminar is very long - over twelve (12) hours - but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish. 

All of the sample database files for this seminar are available on my Web site. They are available in Access 2007 and 2000 formats (Access XP and 2003 users can download the 2000 version which is compatible). You can download the sample databases here.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first two lessons absolutely free.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Work Order Seminar Outline

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)

 


 

 
 

Student Interaction: Access Work Order Seminar

Richard on 4/23/2010:  Microsoft Access Work Order Seminar Build a Database to Run Your Service Business 12.5 Hours Although the title of this course says "Work Order Seminar," it's much more than that. You will learn everything you need to build a database to run a service-oriented business using Microsoft Access. This seminar has several main goals. You will: 1. Track customers with multiple locations 2. Generate work orders with unlimited categories 3. Schedule employees and subcontractors, avoiding conflicts 4. Track job costs, including materials and labor 5. Invoice your customers, and calculate sales tax 6. Design a comprehensive Search Form to find records 7. Create printable reports for your business Click here for more information
Alex Hedley on 4/23/2010: For the ListBox invisible trick you could colour it the same light grey as the column line and make the form background the same colour but then it's the grey you try and stay away from with forms
Dan Wong on 5/6/2010: I have two computer at home, older computer has Window xp pro with access 2003. The other has vista ultima with access 2007. I have a laptop running xp pro with access 2007.
I am the only user,( other don'tspeak English.)
I just purchased "Access work order".
question 1) Is it ok to install it on more than one computer? Since I am the only user (one user)?
Q2? Which version is best for me, the 2003 or the 2007, or does it matter?
Q3) I don't remember much from the earlier access course. Is there a way you can set this up so this can be a group (collaboration) course? I am convinced there are other students who would be interest in a group effort - project. I think it will be a much more power service software.

Reply from Richard Rost:

Hi Dan. Let me answer your questions:

1. As long as you're the only user, you can install my courses on as many computers as you need to. My lessons are sold on a PER-USER basis, so if you need to install them at home, work, and on your laptop, that's just fine with me. Just don't share them with others.

2. If you have a need to build a database to use on both of your computers, you can still use Access 2007, just make sure you save the database file in the old Access 2003 MDB format, not in the new Access 2007 ACCDB format. That should work on both of your machines.

3. This is what the Student Forums are for. Post any questions you have in the forum window that appears next to each lesson and either one of the other students or I will generally answer it for you.

And remember, you can always log back on and download ANY courses you've purchased in the past. I see from your account that you took a whole bunch of Access classes back in 2005. They're still available for you to download and refresh your memory with.

Mary on 5/23/2010: I am using Access 2000 to create Access Work Order Seminar Question:Open customer button in create customerlist works and refesh on close, but when I put in the create new customer button it will not refresh. In my build event they are both the same, but will not refresh customerlist when I close. But if I close the customerlist form and reopen they are there. Any suggestions on what to check
Mary Franklin on 6/5/2010: I'm on Lesson 33 Time 7:06. When I click the Schedule button on the Work Order form it comes up Run-time error '438' Object doesn't support this property or method. I have check everthing, but must be overlooking something.
Mubeezi Micah on 6/12/2010: Dear Richard,

In the work order seminar you encourage us leave some space after an SQL statement "just in case" or "just to make sure". I have heard you mention this in the Search form video 2 at 8:54 and in this (scheduling) video at 9:03.

Many times i have seen you demonstrate what could happen if a step is missed. For a learner like me, it alerts me stronger not to make such a mistake. In this instance, what could happen if no spaces are left after the SQL statement? I have tried this on my own with no visible problems. But i am sure there must be an experience you have gone through which would be good for us learners. Please share it if you can.

Thank you,

MICAH

Reply from Richard Rost:

Well, if you forget don't have a space, you could end up with something like this:

SELECT * FROM CustomerTWHERE CustomerID=3

This of course is an invalid SQL statement and will cause errors. That's why I always try to make sure I have spaces around everything because it's better to have TOO MANY spaces than to be missing a space.

Brian on 7/3/2010: Lesson 3: Time 6:18 to 7:42

SQL Statement:
SELECT LocationID, LocationName FROM LocationT WHERE CustomerID=Forms!CustomerF!CustomerID ORDER BY LocationName;

Access Version 2002.

The WHERE statement is not limiting selection to the CustomerID. It operates the same way as if it is not recognizing the WHERE Statement.

Thank you, Brian



Reply from Richard Rost:

That should work just fine in Access 2002. Check to make sure you spelled everything correctly - including the names of your fields, forms, and the list box. That's usually what causes this problem.

 Brian on 7/5/2010: Hello,

I've been trying to download the full version of the student sample database files used in these lessons without any luck.

A message pops up that says these files come from the internet and aren't trusted. I've tried changing my internet security to allow this site as a "trusted site zone" but no luck.

Using Access 2002.
OS: MS Vista Business

Thank you,

Brian

Reply from Richard Rost:

If you right-click, SAVE AS the database files to your desktop or documents folder, you should be able to open them without a problem.

Joy Cunningham on 7/7/2010: Using Access 2010: Lesson 9
When I put the code in to create a new Customer, (docmd.openform "CustomerF" ,,,, acFormAdd) and save it, the button opens the Customer Form but not at a blank one. What am I doing wrong?

Reply from Richard Rost:

That should work. Your code is correct. I'd need to see your database to tell you for sure what the problem is.

Andrea Martin on 8/4/2010: Hi. I am creating my labout form (Lesson 21, 4min 30 sec) but I need to minus break times from my billable hours.

How do I do this in the code part?

Thanks for your help! Andrea

Andrea Martin on 8/4/2010: Lesson 23: 5min 30sec. In my worker combo box I want to be able to show the person's first and last names as well as the company (if they have one) as in my business it is important to track who is doing the work. Have tried to do this without success, as I can only get the combobox to show one field at a time :-(

Andrea

Reply from Richard Rost:

You can make a query and concatenate the fields together into one, such as:

FullName: FirstName & " " & LastName

Then use this query to feed your combo box.

 David on 8/24/2010: Having a problem with the work order form. Forms opens, Choose the customerId ABC Properties, set focus to LocationID choose the UX South Campus and afterupdate the field goes blank and set focus is on the Unit ID. I've looked at the code and missing something. What am I missing?

 David on 8/24/2010: Richard,

I believe I got it. I created another LocationCombo box on the Work Order Form, named it LocationCombo2 revised the code to reflect LocationCombo2 and it work. I then deleted LocationCombo, then renamed LocationCombo2 to LocationCombo revised the code, and it was messed up again. Renamed back to Locationcombo2 and code an all seems to be working. Any reason why it doesn't like the name LocationCombo?

 Dmitriy on 9/7/2010:
Video 04
Time index 04:08

You say "aaand just to see how it works" then you do something and the Location Name updates with the CustomerID combo box.
In my case the LocationName stays static as "UX SOUTH CAMPUS

Would you explain what you did - it wasn't in the video.

Thanks.

 Kim on 11/10/2010: I have a question on the Access Work Order, I have Residental and commerical customer I would like to be able to sort by each so that they are in ABC Order.. Like John Abney and Abney Construction... how do I do that? and I need a way to list phone number one customer might have 5 different phone numbers Wife might have a cell & work number same for the husband how do you suggest fixing a table for that so that you can determine who/what phone # it is for. Thanks

Reply from Richard Rost:

As far as mixing residential and commercial customers, there are many ways to do this. I personally prefer to make a query that selects the company name IF one exists, otherwise it selects LastName, FirstName. Use something like this in your query:

MyName: IIF(IsNull(CompanyName), LastName & ", " & FirstName, CompanyName)

Now you can use that MyName field in your combo boxes and such where you have to choose a customer.

As far as multiple phone numbers go, you COULD just have 3 or more phone number fields in your customer table. However, nowadays you've got families with 6 or 7 different numbers. Everyone's got a cell phone, some have landlines, fax numbers, etc.

So, you IDEALLY should create a second table to store phone number information:

PhoneID (AutoNumber)
Phone (Text)
Type (ID linked to another table: mobile, home, fax, etc.)

Then you can either make this a subform on your customer form OR a listbox to just display them all.

Have fun.

Kim on 11/10/2010: Thanks for the input, I have one more question and not sure how to address it. I have customers that live together that might or might not have the same last name but both are on the Monitoring contract how do you suggest setting that up, so that a search engine will be able to find the customer no matter who calls in. Thanks

Reply from Richard Rost:

Multiple people could be handled the same way as multiple phone numbers - either additional fields OR a separate related table. Your search then could just incorporate these multiple fields, search on the different table, or bring everything together in one big query to search on it.

 Kim on 11/19/2010: Why is it that we can't see your vba codes in the sample workbook I would like to be able to see if I have it correct of not?

Thanks
Kim

Reply from Richard Rost:

Most of the VBA code should be shown in the handbook. If there is something missing, (a) let me know so I can properly flog my handbook authors, and (b) you can download the sample database to get the full code listings.

 Mary on 11/19/2010: Can I put Create Work Order Button on CustomerListF?

Reply from Richard Rost:

I don't see why not. You'd have to get the value of the Customer List to use on the Work Order.

chris smith on 11/19/2010: is there any way to import customers from microsoft outlook

Reply from Richard Rost:

Yes, it is possible. You can attach to Outlook objects and treat them like a table in Access. I will cover this in a future seminar.

Kim on 11/19/2010: I didn't notice that there was two database and I had just downloaded the sample one...
 Access Noob on 1/17/2011: I have the same problem as below. The SQL statement does not filter the items in the list box.

Comment from Brian @ 7/3/2010
Lesson 3: Time 6:18 to 7:42

SQL Statement:
SELECT LocationID, LocationName FROM LocationT WHERE CustomerID=Forms!CustomerF!CustomerID ORDER BY LocationName;

Access Version 2002.

The WHERE statement is not limiting selection to the CustomerID. It operates the same way as if it is not recognizing the WHERE Statement.

Thank you, Brian




Reply from Richard Rost:

Older versions of Access sometimes choke on this. You might need to say (notice the brackets):

SELECT LocationID, LocationName FROM LocationT WHERE CustomerID=[Forms!CustomerF!CustomerID] ORDER BY LocationName;

or even:

SELECT LocationID, LocationName FROM LocationT WHERE CustomerID=[Forms]![CustomerF]![CustomerID] ORDER BY LocationName;

Try both of those and let me know if it works.

 Access Noob on 1/19/2011: I got it to work by bringing in the LocationID, CustomerID, and LocationName on the list box through the wizard and this SQL:

SELECT LocationID, LocationName FROM LocationT WHERE CustomerID=Forms!CustomerF!CustomerID ORDER BY LocationName;

At 4:10, the vid shows only LocationID and LocationName on the list box through the wizard. For me, it won't without bringing in the CustomerID with LocationID and LocationName.

Thanks! (Access 2010, btw).



Reply from Richard Rost:

That's my mistake. I should have realized that the SQL field list didn't include the CustomerID. You MUST have a field in the field listing if you want to use it in the WHERE condition. Sorry. Good catch.

Dan on 3/10/2011: Hey Richard, I'm using Accses 2010 and have run into a problem at 8:22 in the customer Form video (3). It is regarding the "on current" Event (LocationList.Requery).
the addition of this requery is bringing up the debugger and not letting me cycle through my customers to show the related elements. any advice

Reply from Richard Rost:

I don't believe anyone else has reported a problem like this. I built my database in Access 2007 which is almost exactly the same as 2010. Walk me through exactly what you're doing, and exactly what happens.

 Sandy on 4/2/2011: I am using Access 2010, no matter what size I make my forms they display huge, taking up the whole work area in Access and beyond. It would not be possible to display multiple forms. It is not possible to resize with navigation or scroll bars because none are visible. I have used the actual width dimensions you have in the sample database, verified all format items are the same, tried your resize method that you have posted in tips, which does not work. However your forms in the sample database display smaller.
Any suggestions?

Reply from Richard Rost:

In Access 2007 and 2010, the default setting for forms is Tabbed Documents (which I personally can't stand). They take up the whole Access window. To get smaller forms, you have to switch to Overlapping Windows.

Go to File > Access Options > Current Database > Document Window Options. Select Overlapping Windows.

 Sandy on 4/10/2011: When selecting the add location button on the CustomerF form, it does not display the customer selected on the CustomerF form so the user must select the customer from the Combo List on the location form. Was this the intent? We have a large customer and location base so a combo box needs to limit the list or give the option to do so. Should I use a select statement for this or is it possible to allow entry for the first few letters of the customer name?
Anthony Santiago on 6/8/2011: I messed something up.... after deleting the DateScheduled from the table and form, it asks me for a date scheduled everytime i open the form. what should i do to stop the pop up?
Alex Hedley on 6/8/2011: It'll be looking for a field in a query called DateScheduled so either add the field back in or deleted the field from the query and it should fix the problem.
Might be used in multiple places, I'd have to check the sample db or my notes from when I did it

Parameter Query tip

Al

Anthony Santiago on 6/10/2011: Ok, I found the problem about the DateScheduled popup after deleting it. The Row Source from the WorkOrderT was messed up due to the deletion of the item. I just had to reselect the WorkOrderT. LOL, I spent a few days searching through all of the codes, queries and rewatching the videos to make sure i didn't miss anything.... i swear it's always the little things that get us!

Reply from Richard Rost:

How true that is. I once spent a week trying to find a problem. Turns out it was a misspelled variable. Ugh!

Anthony Santiago on 6/11/2011: The code for the "Next Available Appt" button is not working if I change the Worker, also, everytime I hit the button after I already schedule a worker Access freezes up. I went back through the video to make sure everything matched up, but, I am clueless right now. Anyone know what is up?
Tony on 6/13/2011: Disregard, I misspelled TargetDate....forgot the second "t". Ha Ha, I kill myself sometimes.
David Spens on 6/14/2011: Dear Rick,
I have followed the Work Order Seminar to Lesson 19 with no problems.
However, the Triple State Check Box in Lesson 19 does not work on Access 2010 running on Windows 7.
The check box works in dual state, and if I rem out the two SQL statements in dual state, I get the desired result - (Closed Works Orders) and (All Works Orders) - so the code is clearly correct, but if I retain the triple state I only see the Open Works Orders and cannot change the box of the text box to a tick or check!
However if I run exactly the same program on my Windows 7 laptop running Access 2007 all the code works as intended.
It seems there is a bug in the Access 2010 program.
Can you confirm that?
Regards
David

Alex Hedley on 6/15/2011: Hi David,
Have you tried running the sample db to see if that works?
I tried it on Vista and it's working.
Although I'm sure something came up recently about triple-state cbs, I'll see if I can find it.
Alex

Reply from Richard Rost:

I just tried it with Access 2010 in Windows 7 and it worked OK for me. Try MsgBox'ing the value in an OnClick event and see what it's giving you. Is it NULL, Empty "", or what?

Daniel on 7/7/2011: Hi Richard,

In my database, CustomerT has a field for [PhoneCell], [PhoneWork], [PhoneHome]. I'm having difficulties making my search inclusive of these different phone fields. What I'd like to have is the ability to search a phone number throughout all 3 fields with one click of the search button.

Thanks for your time!

Alex Hedley on 7/9/2011: Hi Daniel,

When I made a search form in a database I wanted to search for a person so I created a text box then based a query using the value entered and searched for either a first name or a last name.

The key here is to use an 'OR' with 'LIKE' and the '*' wildcard
I have wrapped the search value in '*'s so that it will find a value that contains that character(s)


SELECT StudentsT.ID, StudentsT.Form, StudentsT.Forename, StudentsT.Surname
FROM StudentsT
WHERE (((StudentsT.Forename) Like "*" & [Forms]![StudentsF]![DescriptionFilter] & "*"))
OR (((StudentsT.Surname) Like "*" & [Forms]![StudentsF]![DescriptionFilter] & "*"));


(Should really strip the StudentsT. as it isn't necessary as I only have one Table.)

Query Builder OR

So you are referencing the textbox on the form with [Forms]![FORMNAME]![CONTROLNAME] and searching in the (TABLENAME.FIELDNAME)

All you need to do for your phone number search is add another OR and amend the table/fields to use yours.

There is a Seminar planned for Searching, approx release - Jul 25 [Production Schedule]

There is a Multi Field Search Form tip on this site that might also be useful.

There is a version on the Waiting List that you can vote on too.

Alex

WILL POWERS on 8/22/2011: Hi,

at 2:43min,
what is the advantage of putting the CustomerID in the units table? It says its optional.

WILL POWERS on 8/24/2011: Richard, how can you use a where statement with the customerID=forms.... if you dont include the customerID before the "FROM". what am i missing?

Reply from Richard Rost:

Can you elaborate? I'm not sure I understand the question.

WILL POWERS on 8/30/2011: Richard,

in Lesson 5 Location form 2 you show us how to add a new location, what
if you wanted to delete a location with a button, how would you do this?
- Will

Judy Standifer on 10/11/2011: On the Labor form when 11:00 PM is entered for start time and 12:01 AM is entered for end time, it changes the 12:01 AM to 12:01 PM and the amount of time spent is not calculated right. How do I fix this?
Hajialik on 11/7/2011: sir, i purchased one your seminar workorder seminar,
genaral question sir,
how can i sort alpha order in combo box means, i type in "A" on combo box, so the combo box only wants to show list of first letter "A" Customers
how can this possible, plz sir

RP on 1/14/2012: I cannot see an image on this video nor the 1,2.

Reply from Richard Rost:

Try switching to the Flash player.

RP on 1/15/2012: It worked, but I still cannot download the videos. Thanks

Reply from Richard Rost:

The Flash Player is currently ONLINE only. You can't download them. I'm working on adding this feature in the near future. This indicates to me, however, that there's an incompatibility in Windows Media Player somewhere. A handful of people have complained about this over the last year or so, and I am unable to nail down the problem exactly because I can't reproduce it.

RP on 1/15/2012: I am trying to ADD a New Record, but it is not working. I checked the code and repeated the steps several times and did not work. Please advice me on what to do. Thanks,
Bert Tripp on 2/16/2012: Richard, some of the lessons in Work Order and Access 2010 don't run visual, audio only. I pick up most from the dialog so it's not a major. I have tried reloading without success.. Is there another way I can start the video? Cheers Bert

Reply from Richard Rost:

Are you using Windows 7, 64-bit? If so, the Windows Media versions of some of my videos are having problems playing. Try using the Flash player in the Theater. I'm still trying to find a solution for this.

 Usman on 4/7/2012: Is there any way by which we can select a Companyname first & than select the location from the location list on the right & than hit the create work order button that opens the work order filled wtith company info & location info? Please assist.

Reply from Richard Rost:

You mean with a set of cascading combo boxes?

Usman on 4/10/2012: Never mind Sir, I fixed that thing & now, on the customer form, I am able to chose the company (customer) & than a location from the list conataining locations for that particular customer & hit "Create Work Order" button & it opens the work order form with customer & location info automatically filled out. Thank you Sir for taking time to look at my question.
Usman on 5/1/2012: hi,

Is there any way to add blank rows in the DETAILs section? Thanks.

Reply from Richard Rost:

You could add blank records to your table to simulate extra space. I do this all the time with labels - to make up for one or two labels already printed from a full sheet that I'm reusing.

khaja nizamuddin on 5/1/2012: Will --> Deleting Location is @ video 5 , time 07:21.
Rick R on 5/4/2012: Recently I've been asked by a couple of different people if you can buy JUST a copy of the database (without the video tutorials or handbook). The answer is YES. The cost for the database alone is $89. However, if you buy JUST the database, I offer ZERO support with any questions you might have.
Matthew Wolfe on 12/14/2012: for the location list box, is it possible to have the box return an effective date in addition to the location? Time Index: 5:25

Reply from Richard Rost:

What do you mean by "effective date?"

Matthew Wolfe on 12/17/2012: Thanks for the reply Richard! I was able to get my query updated to include the date I was referring to. I also figured out how to make it appear in the list box. Thanks for your help!

Reply from Richard Rost:

You're very welcome.

Karima Wooten on 1/20/2013: Hello Rost,

Access Work Order Seminar:

The first table you created was CustomerT. My husband business consist of residental and commerial companies. I notied that you only enter one company but when you need to add more companies, how would I do this?



Reply from Richard Rost:

If you're saying that you need to be able to store multiple company names for one "customer" then you can either add additional fields to the same table, or use a 2nd table. The first option is fine if you only have 2 or 3 company names you need to track. If you need 4 or more, I'd use the related table. It's like tracking multiple parents for a child. If you only need mother's name and father's name, then 2 fields in 1 table gets you by. However, if you need to also track other parents, guardians, grandparents, etc. then you might want a 2nd table. Basic one-to-many relationship.

Karima Wooten on 1/21/2013: Rost,

I am in the mist of making a vendor's form. Each of our vendors has multiples property location and units numbers inside a dorm or office building. When I make a List Box on my Vendor form, how will be able to enter different locations or units number without going back into the table and to add them as they come in. So, this means I don't have all the location or units # until they come in for repairs.

Please Note: Some vendors add new properties locations all the time in total different areas.

I have created 3 tables: Wooten Appliance Vendors Location, Wooten Appliance Vendors Units Properties Table and Wooten Appliance Vendors table of all our Venders. How should I set up the Row Source in the Property Sheet?

This is what I have in the Row Source in the Property Sheet: SELECT LocationID, LocationName FROM Vendors Location WHERE CustomerID=Forms!Vendors Form!CustomerID ORDER BY LocationName;

This is what I have but it telling me "Syntax error(missing operatior) in query expression 'CustomerID=Forms!VenderForm!CustomerID'.

Thanks for your help in advance!

Karima

Arthur Moore on 2/4/2013: Richard I went through the first course and want to make sure I use my time well. I know access fairly well. What are the limits on Access in database size? Is this good for small businesses where I can change the database used, based on Yearly input? Should I look to your training in Visual Basic?

Reply from Richard Rost:

The limit for an Access database is 2 GB, but you can link tables together for an effectively unlimited size. I almost never go above the 2 GB limit, however. Access is the PERFECT solution for small businesses, and a great solution for departments in a mid-sized or large business. You can always UP-SCALE your database later to SQL Server or something more powerful on the back-end.

William Weaver on 2/8/2013: How do I create a form that can be accessed by someone else to submit a work order to me? For instance, in your database, how would I create a form for the occupants of those units to create a work order that I would then receive? And how could I make it so that they can view the progress of that work order? For me, the work order begins with the occupants of the units I am responsible for. It gets submitted to me, and then I determine how and who fixes it, but I want them to be able to see what the status of that work order is.
Russell A on 2/20/2013: In lesson 3 I cannot get the List box to work correctly. Everything is spelled correctly and is exactly like you show in the seminar however it does not change.

Reply from Richard Rost:

Russell, I would have to see your database to tell you what's wrong. There's not enough information here for me to diagnose your problem. This is kind of like saying "my car won't start" when the mechanic is 3 states away. :)

I can tell you that so far, to date, a few HUNDRED other students have completed this seminar without problems... so you probably missed something. But that's OK... that's what learning is all about - making mistakes. :)

Try it again from scratch in a new database... or give me some more information so I can try to help you.

Russell A on 2/21/2013: Apparently I am not the only one.
This is on your site:
Brian on 7/3/2010: Lesson 3: Time 6:18 to 7:42 SQL Statement: SELECT LocationID, LocationName FROM LocationT WHERE CustomerID=Forms!CustomerF!CustomerID ORDER BY LocationName; Access Version 2002. The WHERE statement is not limiting selection to the CustomerID. It operates the same way as if it is not recognizing the WHERE Statement. Thank you, Br [more...]

Reply from Richard Rost:

I'll have to look into it...

Matthew Wolfe on 3/21/2013: Richard,

When following the updated code builder steps to create the Private Sub, it does work, but only the first time I try to access the form. For instance, after setting the OpenLocation subroutine (11:30) and updating the other two subs, I save the code and exit code builder. Close the customer form, and then reopen. Double Click or Button Click to open store works 1 time. If I close the Location Form and attempt again, I get a Run-Time Error 3008. It indicates that the Table Location_T is already opened exclusively by another user or it is already open through the user interface and cannot be manipulated programmatically. Upon clicking Debug, I am brought to the code builder and the "DoCMD.OpenForm ..." is highlighted in yellow. My code reads as follows:
DoCmd.OpenForm "Store_F", , , "SKEY_STORE_CURR=" & Reporting_List

How can I get around this issue so that end users can close the location form and still be able to use the code to access new stores from various customer profiles?


Reply from Richard Rost:

I've never encountered this before, and no other students have reported this problem. What version of Access are you working with, and do you have all of the latest service packs installed? Bug like this were notorious in Access 2007 (especially before SP1). Can you recreate the problem with a different form? Have you tried rebuilding the form in a new, blank database? This is one of those things that I really have to SEE to fix.

Van Jones on 8/24/2013: Richard, I get a run time error if I select a unit via the search form. This error occurs when the unit form is closed due to the location form not being open and VB is trying to requery the unitlist in the location form. Is there a fix for this?

Reply from Richard Rost:

Don't let the user close the other form. You could make your foreground form POPUP and MODAL so they can't close the forms behind it.

T-Bone on 8/27/2013: Is there a way to do a frame, or option group like a triple state box? In other words give the values of 1,0,-1 and have the code execute the data that way? I hope i'm asking the question properly.

Reply from Richard Rost:

I don't believe so. An option group is a "pick one of these multiple values" control. Check boxes can be in 3 states. Option buttons can only be ON or OFF, one of which in a group.


T-Bone on 8/29/2013: What if we where to use 3 options, say 0=Active, -1=Inactive & 1=All. Then how would we view customers who are in a list and selecting 1 of these options will show the different customers accordingly on the list?

Reply from Richard Rost:

I don't understand the question. Can you give me some context, please? What are you trying to do?

T-Bone on 8/30/2013: In the header of the form I have a 'Find Customer' drop down box. Under that I have an option frame with 'All, Active & Inactive' as selections. I want the option of selecting the 'All' or 'Inactive' and have the list populate based on the option selected. The default is 'Active' when the form opens and it should show the 'Active' customers only until another option is selected and have it refresh the list accordingly.
Mary Franklin on 9/12/2013: Lesson 44
Getting Compile error: User-defined typ not defined. This is after I entered the recordset. I'm using access 2010

Reply from Richard Rost:

I need more info in order to help you. Let's see the code. Is the debugger highlighting a specific line?

Mary Franklin on 9/15/2013: Richard I can't get the recordset to work. I first had trouble getting 2010 to take the first two lines of code. Fix that in the references. Now it will not get pass:
Set rs = db.OpenRecordset("InvoiceT", dbOpenDynaset)
Do you thing this is a reference problem too?
I copied my code below for you to see.

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("InvoiceT", dbOpenDynaset)
rs.AddNew
rs!WorkOrderID = WorkOrderID
rs!InvoiceDate = Date
rs!IsPaid = False
rs!TaxRate = DLookup("DefaultTaxRate", "CustomerT", "CustomerID=" & CustomerCombo)
InvoiceID = rs!InvoiceID
rs.Update
rs.Close
Thank you for your help!

Reply from Richard Rost:

I don't see the problem. Your code looks fine. If you want one of my techs to look at it, send your database to us here: TECHHELP.

Mary on 9/22/2013: Richard it was in the access references. I added the DAO and just kept changing the order till it finally worked.
Mary

Reply from Richard Rost:

Gotcha. Yes, DAO has to be a higher priority than ADO. I should have suggested this first, but I know that I mention it in the seminar like 3 times. :) Ha ha.

Van Jones on 9/24/2013: If I select a location for a customer and then select another customer via the customer combo on the location form, the location will be moved to the customer now selected.

Reply from Richard Rost:

Yes, that is correct. They are able to change which customer that location belongs to. If you don't want that to happen, simply set the LOCKED property of the customer combo on the location form to YES.

Joni Moore on 11/6/2013: Time stamp 4:00 -
Since the Decription field is a text field anyway, why couldn't you just use
1.New, unassigned
2.Work Scheduled
3.Work Completed
etc. instead of creating an order field?
If you wanted to change the order later, you would just change the names right?

BJ North on 2/6/2014: Hi Richard,

I would like to be able to change the data in the listbox on the fly. How do you do that>

Thanks,

Christine White on 2/10/2014: I have seen an example of a cascading combo box where you enter the Select information directly into the combo box Row Source. Just wondering if it makes any difference
Christine White on 2/10/2014: Hi,
Just curious...I don't see the actual Requery command in your Requery sub routine.

john Edwards on 4/11/2014: Hi, Is there any reason why you don't put a combo box into the customer field. 4.31 Will it make any difference to the ongoing example if one was put in? When I have finished studying and I start the process of building mine I will need it as I have too many customers to scroll through

Reply from Richard Rost:

You can put combo boxes wherever you want. Remember, the goal of this seminar is to focus on the main concept: building the work order portion of the database. You can add whatever embellishments you want to the rest of it.

Darleen Perez-lavin on 6/12/2014: I have the same problem as Dan 3/10/11 but it would not let see your response. Please advise what is the solution.

Thank you.

Darleen Perez-lavin on 6/12/2014: So I am getting an error when I close the UnitF form that it says it can't find the 'LocationF' form but it does exist and have the same name. I am confused.

Reply from Richard Rost:

I can't tell what the problem is just from your description here.

Lynda Chase on 9/2/2014: Hi Richard,

I'm Stuck on this one at 5.15 time stamp - this is the code I've put in, but my filter is not working:

If Not IsNull(BeginDateTimeFilter) Then
If W <> "" Then W = W & " AND "
W = W & " BeginDateTime >= #" & BeginDateTimeFilter & "# "
End If

If Not IsNull(EndDateTimeFilter) Then
If W <> "" Then W = W & " AND "
W = W & " EndDateTime < #" & EndDateTimeFilter + 1 & "# "
End If

Can you advise where I'm going wrong

Thanks Lynda

Reply from Richard Rost:

W = W & " EndDateTime < #" & EndDateTimeFilter + 1 & "# "

should be

W = W & " EndDateTime < #" & EndDateTimeFilter & "#+1 "

Lynda Chase on 9/2/2014: Hi again Richard,

I live in New Zealand, so our date format is dd/mm/yy - going through your expert classes, I had a similar issue which unfortunately was not resolved. Basically, the filter does not return results.

Lynda Chase on 9/2/2014: Me Again :o)

I've just discovered, if i put the date in american format in the begindatetimefilter box - mm/dd/yy the filter works fine

Lynda C on 9/2/2014: Hi Richard

I'm unsure if the comments I posted actually reached you from the online theatre screen - I am having a problem with the way access is processing my dates. I live in New Zealand, our date format here is dd/mm/yy - so when building the scheduleF, I've used that format, however, when I come to the lesson on filtering dates, my filters return no results. I had a similar issue when doing the expert series...Sam Finlay replied to my query then saying that he had had a similar problem using non US regional settings. He said I need to enclose dates with a US Format. Is this the right way to address the issue in vba? and if so, can I put a 'global' line of code that will apply the format code anytime I'm using dates?

This is the filter I've created, which is returning no results:

Private Sub BeginDateTime_AfterUpdate()

If Hour(BeginDateTime) = 0 And Minute(BeginDateTime) = 0 Then
BeginDateTime = BeginDateTime + (9 / 24)
End If

' check for conflicting appointments
Dim ID As Long
On Error Resume Next
ID = 0
ID = Nz(DLookup("ScheduleID", "ScheduleT", "WorkerID=" & WorkerCombo & " AND " & _
"BeginDateTime < #" & BeginDateTime & "# AND " & _
"EndDate Time > #" & EndDateTime & "#"), 0)
If ID <> 0 Then
'we have a conflict!
MsgBox "CONFLICT!"
End If


If IsNull(EndDateTime) Then
EndDateTime = BeginDateTime + (1 / 24)
End If

Same issue with EndDateTime

Thanks
Lynda

Lynda on 9/4/2014: Hi Richard, my amt column in the InvoiceListF is not displaying as currency. Can you please advise how I change this to currency format in the listbox.

Thanks

Lynda on 9/4/2014: Please ignore my last question....you answer it later in the video :o)
Lynda C on 9/16/2014: Hi Richard,
I am still having problems with the schedulingF returning errors. I believe this is to do with my location settings and the date format.

I have downloaded your student database, and it is returning the same error.

When I type in the begindatetime field, I get the following error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'WorkerID= AND ScheduleID <> 25 AND BeginDateTime < #17/9/2014 7:00:00 a.m.# AND EndDateTime > #17/09/2014 7:00:00 a.m.#'.

If I then click debug, this is where the error is...

Private Sub BeginDateTime_AfterUpdate()

If Hour(BeginDateTime) = 0 And Minute(BeginDateTime) = 0 Then
BeginDateTime = BeginDateTime + (9 / 24)
End If

' check for conflicting appointments
Dim ID As Long
Dim MyReply
'On Error Resume Next
ID = 0
ERROR IS HERE ID = Nz(DLookup("ScheduleID", "ScheduleT", "WorkerID=" & WorkerCombo & " AND " & _
"ScheduleID <> " & ScheduleID & " AND " & _
"BeginDateTime < #" & BeginDateTime & "# AND " & _
"EndDateTime > #" & BeginDateTime & "#"), 0) ERROR ENDS HERE
If ID <> 0 Then
'we have a conflict!
MyReply = MsgBox("Scheduling CONFLICT found. Keep appointment anyhow?", vbYesNoCancel)
If MyReply = vbNo Then
BeginDateTime = Null
EndDateTime = Null
Exit Sub
End If
End If

If IsNull(EndDateTime) Then
EndDateTime = BeginDateTime + (1 / 24)
End If

I am convinced my problems with dates in access, all stem from the fact that my computers location and regional settings are for New Zealand.

I've tried everything, googling, looking at Allen Brownes international date post etc, but I really don't know how to fix this. And as I said, even downloading your student database, then reports errors.

Please help me.

Thanks
Lynda

David Martinez on 10/21/2014: TIME INDEX: 7:10
Could you briefly explain the code needed to populate the search results in a list box below the search field rather than opening the customer form separately? I would like to populate the results below in a list box and then double click one to open up it's customer form.

Reply from Alexander Hedley:

Hi David,
You can set a listbox to have a Row Source of a Table/Query so you could just base this on a Query that is returned for the Search Results.

Megan Prosser on 11/5/2014: I added my Location list box to my customer form. Now, when I go to open my customer form, I get a ""enter parameter value" box. It wants the LocationID and LocationName. I am assuming it is because my form is missing values somewhere or a relationship is not set up right, but there are no instructions regarding setting up a relationship specifically for this box. Can you help me with what I have done wrong?

Reply from Alex Hedley:

Hi Megan,
The Source/Query used for the Listbox will be looking for values on the Form you borrowed it from which aren't there so you will have to change where it is getting the data from.
You could create a new query and use that instead.

Karena Pollard on 3/12/2015: Hello,

O am using Access 2013 and working on the list boxes for the customer form. When I am in the property sheet and go to events, I do not have a line called Current.
These are the ones I have: 'On Click', 'On Dbl Click', 'On Mouse Down', On Mouse up', 'On Mouse Move', 'On Paint'. I am wanting the Location list to update as I move from record to record. In which of these lines would I enter the command?

Reply from Alex Hedley:

The On Current Event runs for the Form not a Control.
Click on the Detail section of the Form or where the two Rulers meet top left hand corner and add it there

Steve HEnderson on 4/17/2015: In the Work Orders seminar you showed how to format the begin/end date/time fields to show only the date. How do you set the time format to not show the seconds in the Upcoming Appts area? Thanks.

Reply from Alex Hedley:

You can use a Format of "hh:nn"

Anne Cowden on 4/29/2015: Hello Richard,

Re Time Index: 08:00

I am having a problem getting the check box to show the third state. I can see the active and inactive but not both together. Can you suggest where I should look.

Kind regards
Anne



Reply from Alex Hedley:

Did you set the Triple State Property?

Anne C on 4/29/2015: Hi Alex,

Thanks for you speedy reply. Yes, triple state is set. I have watched the lesson several times and have deleted the checkbox and redone. But I must be missing something.

Reply from Alex Hedley:

Is it unbound i.e. Not bound to any field on your Form.

Anne C on 4/30/2015: It is unbound. I copied the database to another computer and it works perfectly. Could there be a setting within the Access options that could effect this?

Reply from Alex Hedley:

Not that I'm aware of, I'll have to do some digging.

CHARLES FULGHAM on 5/11/2015: The enabeling and disableing of Location and Unit Combo boxes originally wasn't working on my version like in this lesson, but I figured out that the Customer, Location, and Unit were being given default values of zero (not null) in the WorkOrderT. I adjusted to code to look for null's OR zero's and it works perfect. Thought I'd share. THANKS, Loving this lesson so far!

Reply from Alex Hedley:

Thanks for sharing.

Clay FULGHAM on 5/14/2015: I'm using Access 2015, for some reason the Billable Hours calculation at 3min 19 seconds in this lesson (CalculateBillableHours) was giving me an entirely different result than in the video, I used the DateDiff function "n" /60, and now it works like a champ. Thought I'd share.

Reply from Alex Hedley:

Thanks for sharing

Anne Cowden on 5/14/2015: Hi Richard/Alex

I have set up a field within my WorkOrderF called OrderNumber which is indexed and set to no duplicates.

I want to prevent users from entering a duplicate number but if they do I want them to be taken to the original record.

I have the following code in the before update event:

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.OrderNumber.Value
stLinkCriteria = "OrderNumber =" & "'" & SID & "'"

'Check WorkOrderT for duplicate OrderNumber
If DCount("OrderNumber", "WorkOrderT", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Order Number " _
& SID & " Already Exists." _
& vbCr & vbCr & "You will now be taken to that record.", _
vbInformation, "Warning: Duplicate Information"
'Go to record of original Order Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

It works fine until it reaches Me.Bookmark = rsc.Bookmark

then it breaks. Can you show me what I'm doing wrong. I am using Access 2010.

Thanks
Anne

Reply from Alex Hedley:

I'm not a fan of RecordsetClone and Bookmarks

Why not set it to:
Set rsc = CurrentDb.OpenRecordset("TABLE/QUERY", dbOpenDynaset)

Or you could say
Set rsc = Me.Recordset.Clone

Also it might not be the Bookmark that is the issue, sometimes Access highlights the next line.
Is OrderNumber a String or Integer
stLinkCriteria = "OrderNumber =" & "'" & SID & "'"
If Integer change to:
stLinkCriteria = "OrderNumber=" & SID

Anne Cowden on 5/16/2015: Thanks Alex,

I tried both of your suggestions but still got the same error.

What I did find was that if I enter a duplicate order number straight into the WorkOrder Form it opens up the warning and when I click on ok, takes me to the original record, which is exactly what I wanted, so the code is working fine.

However, if I try this via the CreateWorkOrderButton on any other form, which is what the end user will do, the code breaks at the same place.

The OrderNumber is a text field in the WorkOrder Table, is indexed and set to no duplicates.

Thanks
Anne

Reply from Alex Hedley:

Doesn't the CreateWorkOrderButton not open WorkOrderF, so that would be a new Record?

Anne Cowden on 5/18/2015: Yes, the CreateWorkOrderButton does open TheWorkOrderF but that is when the code breaks.

If I attempt to enter a duplicate OrderNumber it shows the messagebox box warning then if I say ok to take me to the original record with that OrderNumber the code breaks. This does not happen if the WorkOrderF is open on its own. The code works perfectly and takes me to the original record.

Reply from Alex Hedley:

If you click Debug, which line does it go to?

Anne Cowden on 5/22/2015: Error 3021 "No current record"
On clicking debug goes to:
Me.Bookmark = rsc.Bookmark

Reply from Alex Hedley:

You could move it from the "before update" event to your own event. Remove the Close Button on the Form then only allow the user to add a Record via your button. You could then run this check only when they click save.

Anne Cowden on 5/27/2015: Thank you Alex. I appreciate your suggestions but I would rather not have my users waiting until they have entered all data before finding out that they have a duplicate order number.

I would really like to find out why the code works when the workorderf is opened on its own but not when other forms are open. Could it be because there is a me.undo on duplicate entry? As you know the code is in the beforeupdate event on the ordernumber field and is a private sub. Should it also be placed somewhere else?

Also, after creating an order through the unitf, which is the last form in the process, is there a way to close all preceding forms without clicking close on each individual form?

After having now completed the whole seminar, this is the only issue I have not been able to resolve.

I have thoroughly enjoyed the whole learning experience and would highly recommend it to anyone who wishes to learn more about Microsoft Access. I now need to be able to split my database, can you suggest which course would be best?

Thanks again for all your help.

Kind regards
Anne

Reply from Alex Hedley:

I'll have to test it when I'm back on a PC.

You could have a list of commands closing each form:
DoCmd.Close acForm, "FORMNAME", acSaveYes

There is either the Split DB Seminar or the Security Seminar.

Lynda Chase on 7/12/2015: Hi Richard, Quick question, I would like to be able to hit a 'add new category' button on the WorkOrderF , which brings me to a form, where I can add a new category (I've got this working fine) My issue is, upon closing the CategoryF, my subform in the WorkOrderF, doesn't update with the new category. I realise it's probably a simple bit of code. But I just cannot figure it out. Please Help

Reply from Alex Hedley:

Add a CONTROL.Requery where CONTROL is the name of the SubForm.

This Tip might help.

=Forms!FormName!FieldName
Or...
=Forms!ParentForm!SubForm.Form!FieldName

Lynda Chase on 7/12/2015: Hi Richard
Can you please tell me what I need to put in my code, to enable European time filtering, as the EndDateTimeFilter and BeginDateTimeFilter only work if I enter US date format in them, even though my format is for dd/mm/yy hh:nn

Reply from Alex Hedley:

I've had this problem myself and a couple of others have mentioned it in the Forum,
You could try a Format Function on the Date before you pass it to the SQL statement so it is the way it expects it.

Anonymous on 7/12/2015: Hi Again, I'm finding the whole scheduling section of this course to be nigh on impossible for me as I am in New Zealand, and Access 2013 uses US Date formatting in SQL (I think) So none of my filters work nor does the 'conflict' of appointments scheduled. Please help.
Lynda C on 7/15/2015: Thanks Alex - how do I go about doing that?

Reply from Alex Hedley:

Are you using a Parameter Query?
Just use the Format Function in your Parameter, wrap the Date you're pulling from the Form.

Lynda C on 7/15/2015: This is what I have in the 'on close' event of the CategoryF
Private Sub Form_Close()

On Error Resume Next
Forms!CategorySubFormF!CategoryCombo.Requery
Forms!WorkOrderF.Requery
Forms!WorkOrderF!CategorySubFormF.Requery

End Sub

Please tell me where I'm going wrong

Reply from Alex Hedley:

Don't put it in the Close Event.
Put it in a Button Click with a Me.Close at the end or you can't access the Forms in that code block.

Holly Winter on 7/28/2015: I would like to filter the work order list using buttons based on the various statuses (e.g. 1. New Unassigned, 2. Scheduled etc etc). How can I do this?

Reply from Alex Hedley:

You could add a Filter button to the Form using a Macro.

Holly W on 8/4/2015: Anyone able to point me in the right direction with some example code? Help would be much appreciated! Thanks

Reply from Alex Hedley:

You could do it in code:
Me.Filter = "FIELD= """ & Me.CONTROL & """"
Me.FilterOn = True



Have you tried the Button Wizard?
Form Operations | Apply Form Filter

Alexander LaValle on 9/13/2015: My StatusT has over 9 choices. When the form is run the StatusCombo reurns the order of 1,10,11,12,2,3,4,5,6,7,8,9. How do I fix this sort?

Reply from Alex Hedley:

Is this number an AutoNumber.
Do you have a Sort or ORDER BY on your Query?

Alexander L on 9/17/2015: I have this set up exactly as the seminar shows, lesson 15, 3:30. I just have more status choices than the video, 12 status choices.

Reply from Alex Hedley:

Can you show me the SQL used in your Query.
Open the Query in Design Mode, change the view to SQL view and paste the code here please.

Lynda Chase on 10/3/2015: Hi Richard,

OK, I've expanded on the categorysubformF, where i can pick the category from the categorycombo box, and then a second combo box runs next to this for suppliercombo - so i can choose a supplier, however when i move to the next line and pick a second category for the same work order, the supplier combo box deletes all previous suppliers listed, and only puts in the possible suppliers for the category selected. How can I get previous categorys and suppliers 'to stick' when i move to put in another category.
I know I haven't written this question so it is clear, am hoping you're as good at deciphering as you are access 2013 lol

Reply from Alex Hedley:

Are you creating a cascading combo?

Lynda C on 10/6/2015: Hi Alex
I don't even know what a cascading combo is, but basically, all my suppliers have 'categories' eg plumbing, electrical. When I select the category, I've got the supplier combo only showing those in that category, which is great. However....as soon as i move to the next line to enter a second category for my work order, the supplier combo above clears out and the new reduced supplier list appears according to the category chosen.

Reply from Alex Hedley:

Say you have list of States and you want to see the Cities in that State.
Cascading Combo Tip

They are probably bound to the same data.
Did you just copy paste them to double them up?

Wayne Rudge on 4/7/2016: Hi Richard, I am also having the same issue as Lynda I cannot figure out how to get the date reports to appear in dd/mm/yyyy format, if I enter into my begin date time filter 04/07/2016 then it will show results for 7th April. Many Thanks Wayne

Reply from Alex Hedley:

Did you try the Format?

Scott Axton on 5/24/2016: Hello - Just finished watching the last lesson in the seminar. What a course!! SO much info here. I appreciate the approach for a service industry. Really helpful for what I'm attempting to do.

I do have a design question if you are still around. This topic is quite old.

I'm having trouble wrapping my mind around handling disconnecting a property (location) from one customer (owner) and assigning it to a new customer/owner. I want to make it pretty straight forward and easy to do but difficult enough (or protected enough) so it cant be done by accident.

We are a property maintenance company in a military town and properties change hand quite often. So I want to maintain the history of the property even if it changes hands.
Additionally, probably 90-95% of our clients are single family residences. The rest are "commercial" where there might be multiple locations for the same customer. We don't have any that are multi-unit so that should help simplify a bit.

Any suggestions or comments on changing the design a bit to handle the above situations would be greatly appreciated!

Thanks - Scott


Reply from Alex Hedley:

You will need another Table Ownership History.
When you assign an Owner add a record to this Table, only needs CustomerID and LocationID, DateStart, DateEnd.
When you assign again, update the End of the last record and Start of New.
Now you can query this to see who had it.

Scott A on 5/27/2016: That makes perfect sense! Don't know why I didn't come up with it. Oh well - Learn with each experience right?

Thanks for the quick response!
Scott

Reply from Alex Hedley:

Indeed you do, and the fact I've lived in an AssetManagement system for the past 6 months, you get to know the inner workings quite well!

Matthew Pattison on 6/20/2016: sound fades in and oout :40 annd other spots

Reply from Alex Hedley:

Does it happen if you replay the video?

Matthew Pattison on 10/14/2016: Can you make overlapping windows a default for new databases?

Reply from Alex Hedley:

Don't think so, unless it's a Reg key I don't know about.

Matt on 11/28/2016: 4:47 would a locationID be helpful to track the employee Location

Reply from Alex Hedley:

Sounds like a good idea, if you've got a lot of locations and want to store other information against it.

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP