Access 2007-2016
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  
 

News      User Comments     History     Notify Me

4/23/2010 2:51:00 PM
Access Work Order Seminar
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

 
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

Permanent Link
Course Link: Access Work Order Seminar
Page Tag: whatsnew
Post Reply

Hours to hh mm Comment from Rene v @ 6/26/2017
how to get nummer Hours 21.25 to 21:15
in the Form LaborF


Reply from Alex Hedley:

What Format are you trying?
'm' is used for Months
'n' is used for Minutes.
You could try "hh:nn".
Show Just This Thread        Post Reply
Purchase Order Comment from Shallena A @ 5/5/2017
It wont create the query correctly, everything shows up double or triple


Reply from Alex Hedley:

Sounds like a Cartesian product to me.
Don't join all the tables in one query, build them up in multiple queries.
Show Just This Thread        Post Reply
Purchase Order Comment from Shallena A @ 5/1/2017
I have spent a week trying to figure this out. I am looking for any suggestions on the direction i should take to accomplish what i am trying to do.

I get a Purchase Order with a list of things to complete - some are tower and some are Civil(ground) and I have an "other" category.  I have to assign each of the items (there could be 20 or more items on the list).  So I enter what type of work each one is.  Then i go to my schedule table to schedule the worker.    
Job Form
JobID PO ID Service Code Service Description Unit Price UOM QTY Line Total  Work Type ScheduleID
111 57 AN03 GPS/BCCH Antenna Install 100 EA 1 100 Civil 10
112 57 AN12 Downtilt/Azimuth Change 200 EA 1 200 Tower 9
        
   I come to this Form and assign a time frame for the job and a subcontractor to the work type.        
Schedule Form
JobID ScheduledID Scheduled Date PO ID Job Number Subcontractor ID start date end date WorkTypeID
112 9 1-May 57 IL12345 Johny Subcontractor 15-May 20-May Tower
111 10 1-May 57 IL12345 In House 10-May 17-May Civil
The problem I am having is relating these tables together so that I can give each sub a work order only for their portion of the job and then a Purchase Order with their pricing. The JobID will only come over to the Schedule Table for the first JobID.   Right now i am opening the ScheduleF from my main PurchaseOrder page so it pulls all of the job items over and puts the POID on each item.  But I cant get the JobID onto the Schedule Form and/or the ScheduleID onto the Job Form.   And then....  i am not sure if i will be able to keep everyone separate for giving out work orders and for sending Purchase Orders and paying out each part of the job properly.          
Thank you so much in advance for any help, Shallena
Show Just This Thread        Post Reply
Purchase Order Comment from Shallena Ayers @ 5/1/2017
I submitted a question about my issue relating my work order table and schedule table.  I thought a visual might help.
Job Form  Job Table I get a Purchase Order with a list of things to complete - some are tower and some are Civil(ground) and I have an "other" category.  I have to assign each of the items (there could be 20 or more items on the list).  So I enter what type of work each one is.  Then i go to my schedule table to schedule the worker.      
JobID PO ID Service Code Service Description Unit Price UOM QTY Line Total  Work Type ScheduleID
111 57 AN03 GPS/BCCH Antenna Install 149.63 EA 1 149.63 Civil 10
112 57 AN12 Downtilt/Azimuth Change 731.5 EA 1 731.5 Tower 9
        
   I come to this Form and assign a time frame for the job and a subcontractor to the work type.        
Schedule Form        
JobID ScheduledID Scheduled Date PO ID Job Number Subcontractor ID start date end date WorkTypeID
112 9 1-May 57 IL12345 Johny Subcontractor 15-May 20-May Tower
111 10 1-May 57 IL12345 In House 10-May 17-May Civil
        
        
The problem I am having is relating these tables together so that I can give each sub a work order only for their portion of the job and then a Purchase Order with their pricing. The JobID will only come over to the Schedule Table for the first JobID.   Right now i am opening the ScheduleF from my main PurchaseOrder page so it pulls all of the job items over and puts the POID on each item.  But I cant get the JobID onto the Schedule Form and/or the ScheduleID onto the Job Form.   And then....  i am not sure if i will be able to keep everyone separate for giving out work orders and for sending Purchase Orders and paying out each part of the job properly.          



Reply from Alex Hedley:

You have the 'SubcontractorID' base your Reports on that, you could group them by JobID/ScheduledID etc
Show Just This Thread        Post Reply
Purchase Order Comment from Shallena Ayers @ 4/29/2017
Hi Richard,  In my database i am building with  you I receive a Purchase Order from my customer... The purchase order has a list of services they need done (lets say there are 4 services... 2 tower and 2 ground type services). I enter each service into my Jobsite Total form (where you made the materials form). so i dropdown box and add each requested service with the associated price and add it to my work order. I mark each service as either a Ground Type Service or a Tower type Service. Then... i hit my Schedule Subs button which takes me to the Schedule Form that you created... and I assign a subcontractor to do the tower work and a subcontractor to do the ground work. (the same sub may do both parts of the job).  I need to be able to issue a work order to 2 different subs for the same work order... one subcontractor will do the tower work and one will do the ground work. I then need to pay each of them for their part of the work.  I have been making a serious mess of this database trying to figure out the best way to do this. My flow right now is to enter the jobsite services that have been requested... schedule the subs for each part of the job... then click a button to print a Work Order to give to each of my subs.... that contains only their part of the work and the pricing they will be paid.  I have been going back and forth with sections where you create an invoice and schedule workers and use RecordSets... I am hoping you can give me some advice on the best/easiest way to accomplish what I want to do.  If there is a video that will show me how to do what i want I am happy to purchase it... i am at the point where everything i am doing is just messing up what i have already done.... i keep telling myself STOP TOUCHING IT!  But I dont listen.  If you could point me in the right direction i would be so happy and grateful!  Thank you in advance, Shallena


Reply from Alex Hedley:

Sounds like you have most of it already set up.
Create a Query that gets the assigned services based on the assigned user and base a report on that, you can then print it out.
Show Just This Thread        Post Reply
when i print more than 1 customers report the head Comment from Shallena Ayers @ 4/19/2017
Sorry that i didnt include this with my previous issue of the header only printing on the first customer report.  But I also noticed that my sub form information from the first report is shown on all customer reports.  If go to each work order and print just that work order... everything is fine.  I also notice on my Work Order Report that if I just open it from the reports section it shows me all of my customer work orders one after the other (which is what it should do) but only the first one has the header and all the rest of the reports have the service information from the first work order. Thanks for your help in figuring this out.  (Individually each report prints fine.  Its just when i try to print several work orders that the issue shows itself)
Show Just This Thread        Post Reply
when i print more than 1 customers report the head Comment from Shallena Ayers @ 4/19/2017
when i print more than 1 customers report the header stuff only shows up on the first report.  its like it thinks the other customer reports are part of the same report so no header info needed.  I dont see that you did anything with the header one way or the other.  What will fix this?  Thanks in advance,


Reply from Alex Hedley:

If you add a Page Header instead of a Report Header it will print on each.
Show Just This Thread        Post Reply
Search box section Comment from Shallena Ayers @ 4/1/2017
Hi again,  I am doing the search box section.  i would like to be able to have my search box be a dropdown box and use the choice for the search... (I have trouble remembering options).  I would like to have a search by job status.  instead of typing the status into the search box i would like to choose a status from the dropdown box and hit the GO button and have all the jobs with that status show up in the listbox.  I tried just substituting a dropbox for the textbox but it didnt work. Thanks in advance for your help.


Reply from Alex Hedley:

If you name the control the same as your original textbox and there is only one field i.e. the value you are searching that will be the input to your Query.
Show Just This Thread        Post Reply
Lessons 26 to 28 Comment from Shallena A @ 4/1/2017
Hi Alex,  I figured out the reason i could not delete something.  I had turned off the Record Selectors option so there was no way to choose a record to delete.  But as for the formula.  I have double checked and triple checked and the only thing that is different is that i am trying to SUM  a column of numbers that have formulas in them.  This is the formula in the field named SubAmount =([JobTotal]*[percentageperformed])*([subpercent])  (so maybe one sub crew did 50% of the work and another sub crew did 50% of the work and the subcrews gets 75% of the JobTotal) At the bottom of the form I have a field called TotalSubCost and the formula there is =Sum([SubAmount]).  Then when i closeout the sub crew form i have a formula on the purchase/work order to show the total sub cost and the formula there is =DSum("SubAmount","Subcontractorlaborf","purchaseorderid=" & [PurchaseOrderID]).  

I copy and pasted all of those formulas and names.  On the subcontractlaborf the totalsubcost shows $0.00 no matter what is entered.  And the PurchaseOrderF shows #Error in that box.


Reply from Alex Hedley:

Are any of the rows NULL or showing invalid values?
Show Just This Thread        Post Reply
Lessons 26 to 28 Comment from Shallena Ayers @ 3/31/2017
hello,  I an working on lessons 26 to 28.  I used the same principal to create a Jobsite form - we work on cell towers.  each project has  list of potential things that we could be asked to do.  I set up this form that works great... i can add whatever has been requested and it gives me the total of the workorder.  The trouble came when i used the same principal for my SubLabor form.  the subs typically make a percentage of the total workorder and they may do all of the labor or only part of the labor.  I was able to set up the form so i could choose the subcontractor... and it tells me the percentage that they get and i can enter in another field what percentage of the job they did.  Then using the Total from the Jobsite form i can calculate how much the sub is owed.  However... I am not able to total up that calculated field in the footer (maybe an inhouse crew did half the work and a sub did half the work).  I get $0.00 using =Sum([SubAmount]).   Then... when i try to bring the total amount back to my workorder page using =DSum("SubAmount","Subcontractorlaborf","purchaseorderid=" & [PurchaseOrderID])... I get a #error.

Question2 - i dont seem to be able to delete or change an option if i made an incorrect choice on these forms. (your forms are the materials forms).  Thanks for  your help... i am enjoying the seminar.


Reply from Alex Hedley:

Is the name of the field correct?

2. what happens when you try and delete, anything show in the status bar?
Show Just This Thread        Post Reply
Is the vb code case sensitive Comment from Brian Merrick @ 3/28/2017
Is the vb code case sensitive?


Reply from Alex Hedley:

What isn't working for you?
Have you declared a variable called "Fred" and when you use it as "fred" it isn't working?
Show Just This Thread        Post Reply
Next Appointment Comment from Brian Merrick @ 3/28/2017
For the next available appt times button, when i select the worker it shows the available appointments, but it does not populate the begin time and end time in the box. What am i doing wrong?


Reply from Alex Hedley:

Have you selected an available appt from the list?
Show Just This Thread        Post Reply
SQL Schedules Comment from Brian Merrick @ 3/27/2017
For the list of schedules for the worker, i used the same VB Code (PreviewList.RowSource = "SELECT ScheduleID, WorkerID, BeginDateTime, EndDateTime " & _
    "FROM ScheduleT " & _
    "WHERE WorkerID=" & WorkerCombo & " AND BeginDateTime >= #" & Now() & "# " & _
    "ORDER BY BeginDateTime; ") but when i select a worker it goes blank.  What went wrong?



Reply from Alex Hedley:

Dim strSQL As String
Instead of PreviewList.RowSource have strSQL = ...
Then PreviewList.RowSource = strSQL
Then can you add a Debug.Print strSQL and see what it shows.
Show Just This Thread        Post Reply
VBA and SQL Code Comment from Brian Merrick @ 3/26/2017
Richard I would just like to say that I'm amazed how you know all the VB Code and Sql code off the bat.  Kudos to you, Man!


Reply from Alex Hedley:

It's years of practice!

He might have it on one of his 10 other screens too :p like the wizard of oz, the man behind the curtain!
Show Just This Thread        Post Reply
Decimal Option Comment from Brian Merrick @ 3/24/2017
I am using Microsoft Access 2016 when it comes to the billable hours i don't have the option to select decimal under the number option.



Reply from Alex Hedley:

Number then Decimal Points?
Show Just This Thread        Post Reply
Default Value Comment from Brian Merrick @ 3/21/2017
Never mind, i figured it out. Thanks so much.


Reply from Alex Hedley:

What fixed it?
Show Just This Thread        Post Reply
Default Value Comment from Brian Merrick @ 3/21/2017
I am having problem when I click on the add new record in the customer form and it opens the new location form, it does not fill in the customer name.  I have in the default value under Customer Combo
Forms!CustomerF!CustomerID.  What went wrong?


Reply from Alex Hedley:

Does the Customer Combo have the first field as the ID?
Show Just This Thread        Post Reply
Just for others with date time formatting issues Comment from Jeffrey Ervin @ 2/24/2017
Just for others with date time formatting issues as I live in Australia. After some messing around, This code worked for me

If Not IsNull(beginDateTimeFilter) Then
        If W <> "" Then W = W & " AND "
        W = W & " BeginDatetime >= #" & Format(Me.beginDateTimeFilter, "dd/mmm/yyyy") & "# "
    End If
    
    If Not IsNull(EndDateTimeFilter) Then
        If W <> "" Then W = W & " AND "
        W = W & " EndDatetime < #" & Format(Me.EndDateTimeFilter + 1, "dd/mmm/yyyy") & "# "
    End If


Reply from Alex Hedley:

I'm UK based an have had this pane for years! DLOOKUPS kill me all the time with this.
Show Just This Thread        Post Reply
4 47 would a locationID be helpful to track the em Comment from Matt @ 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.
Show Just This Thread        Post Reply
Can you make overlapping windows a default for new Comment from Matthew Pattison @ 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.
Show Just This Thread        Post Reply
sound fades in and out 40 and other spots Comment from Matthew Pattison @ 6/20/2016
sound fades in and oout :40 annd other spots


Reply from Alex Hedley:

Does it happen if you replay the video?
Show Just This Thread        Post Reply
Owner History Comment from Scott A @ 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!
Show Just This Thread        Post Reply
Owner History Comment from Scott Axton @ 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.
Show Just This Thread        Post Reply
European Time Filtering Comment from Wayne Rudge @ 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?
Show Just This Thread        Post Reply
Category SubForm Comment from Lynda C @ 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?
Show Just This Thread        Post Reply
Category SubForm Comment from Lynda Chase @ 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?
Show Just This Thread        Post Reply
StatusT Ordering Comment from Alexander L @ 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.
Show Just This Thread        Post Reply
StatusT Ordering Comment from Alexander LaValle @ 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?
Show Just This Thread        Post Reply
Filter Work Order List Comment from Holly W @ 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
Show Just This Thread        Post Reply
Filter Work Order List Comment from Holly Winter @ 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.
Show Just This Thread        Post Reply
New Category Comment from Lynda C @ 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.
Show Just This Thread        Post Reply
European Time Filtering Comment from Lynda C @ 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.
Show Just This Thread        Post Reply
European Time Filtering Comment from Anonymous @ 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.
Show Just This Thread        Post Reply
European Time Filtering Comment from Lynda Chase @ 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.
Show Just This Thread        Post Reply
New Category Comment from Lynda Chase @ 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
Show Just This Thread        Post Reply
RecordsetClone and Bookmark Comment from Anne Cowden @ 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.
Show Just This Thread        Post Reply
RecordsetClone and Bookmark Comment from Anne Cowden @ 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.
Show Just This Thread        Post Reply
RecordsetClone and Bookmark Comment from Anne Cowden @ 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?
Show Just This Thread        Post Reply
RecordsetClone and Bookmark Comment from Anne Cowden @ 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?
Show Just This Thread        Post Reply
RecordsetClone and Bookmark Comment from Anne Cowden @ 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
Show Just This Thread        Post Reply
Billable Hours Comment from Clay FULGHAM @ 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
Show Just This Thread        Post Reply
The enabeling and disableing of Location and Unit Comment from CHARLES FULGHAM @ 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.
Show Just This Thread        Post Reply
Triple State Checkbox Comment from Anne C @ 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.
Show Just This Thread        Post Reply
Triple State Checkbox Comment from Anne C @ 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.
Show Just This Thread        Post Reply
Triple State Checkbox Comment from Anne Cowden @ 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?
Show Just This Thread        Post Reply
Format Time Comment from Steve HEnderson @ 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"
Show Just This Thread        Post Reply
On Current Event Comment from Karena Pollard @ 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
Show Just This Thread        Post Reply
I added my Location list box to my customer form Comment from Megan Prosser @ 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.
Show Just This Thread        Post Reply
Search Results List Box Comment from David Martinez @ 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.
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Lynda C @ 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
Show Just This Thread        Post Reply
Comment from Lynda @ 9/4/2014
Please ignore my last question....you answer it later in the video :o)
Show Just This Thread        Post Reply
Comment from Lynda @ 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
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Lynda C @ 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
Show Just This Thread        Post Reply
date format Comment from Lynda Chase @ 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
Show Just This Thread        Post Reply
date format Comment from Lynda Chase @ 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.
Show Just This Thread        Post Reply
Dates and Quotes and Such Comment from Lynda Chase @ 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 "
Show Just This Thread        Post Reply
So I am getting an error when I close the UnitF fo Comment from Darleen Perez-lavin @ 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.
Show Just This Thread        Post Reply
Comment from Darleen Perez-lavin @ 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.
Show Just This Thread        Post Reply
combo box in customer field Comment from john Edwards @ 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.
Show Just This Thread        Post Reply
Comment from Christine White @ 2/10/2014
Hi,
Just curious...I don't see the actual Requery command in your Requery sub routine.
Show Just This Thread        Post Reply
Comment from Christine White @ 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
Show Just This Thread        Post Reply
Comment from BJ North @ 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,
Show Just This Thread        Post Reply
Comment from Joni Moore @ 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?
Show Just This Thread        Post Reply
Change Location Comment from Van Jones @ 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.
Show Just This Thread        Post Reply
cant get the recordset to work Comment from Mary @ 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.
Show Just This Thread        Post Reply
cant get the recordset to work Comment from Mary Franklin @ 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.
Show Just This Thread        Post Reply
User defined type Comment from Mary Franklin @ 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?
Show Just This Thread        Post Reply
Option Group Comment from T-Bone @ 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.
Show Just This Thread        Post Reply
Option Group Comment from T-Bone @ 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?
Show Just This Thread        Post Reply
Option Group Comment from T-Bone @ 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.

Show Just This Thread        Post Reply
Error if form closed Comment from Van Jones @ 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.
Show Just This Thread        Post Reply
RunTime Error 3008 Comment from Matthew Wolfe @ 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.
Show Just This Thread        Post Reply
cannot get the List box to work correctly Comment from Russell A @ 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...
Show Just This Thread        Post Reply
cannot get the List box to work correctly Comment from Russell A @ 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.
Show Just This Thread        Post Reply
Comment from William Weaver @ 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.
Show Just This Thread        Post Reply
Access Questions Comment from Arthur Moore @ 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.
Show Just This Thread        Post Reply
Comment from Karima Wooten @ 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
Show Just This Thread        Post Reply
add more companies Comment from Karima Wooten @ 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.
Show Just This Thread        Post Reply
effective date Comment from Matthew Wolfe @ 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.
Show Just This Thread        Post Reply
effective date Comment from Matthew Wolfe @ 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?"
Show Just This Thread        Post Reply
Buy Just the Database Comment from Rick R @ 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.
Show Just This Thread        Post Reply
Comment from khaja nizamuddin @ 5/1/2012
Will --> Deleting Location is @ video 5 , time 07:21.
Show Just This Thread        Post Reply
Comment from Usman @ 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.
Show Just This Thread        Post Reply
Comment from Usman @ 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.
Show Just This Thread        Post Reply
Comment from  Usman @ 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?
Show Just This Thread        Post Reply
Comment from Bert Tripp @ 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.
Show Just This Thread        Post Reply
ADD Location Form2 at 1 31 Comment from RP @ 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,
Show Just This Thread        Post Reply
Flash Player Comment from RP @ 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.
Show Just This Thread        Post Reply
Comment from RP @ 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.
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Hajialik @ 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
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Judy Standifer @ 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?
Show Just This Thread        Post Reply
Comment from WILL POWERS @ 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
Show Just This Thread        Post Reply
FROM Clause Comment from WILL POWERS @ 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.
Show Just This Thread        Post Reply
Comment from WILL POWERS @ 8/22/2011
Hi,

at 2:43min,
what is the advantage of putting the CustomerID  in the units table?  It says its optional.
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Comment from Daniel @ 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!
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Alex Hedley @ 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?
Show Just This Thread        Post Reply
Comment from David Spens @ 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
Show Just This Thread        Post Reply
Comment from Tony @ 6/13/2011
Disregard, I misspelled TargetDate....forgot the second "t". Ha Ha, I kill myself sometimes.
Show Just This Thread        Post Reply
Comment from Anthony Santiago @ 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?
Show Just This Thread        Post Reply
Comment from Anthony Santiago @ 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!
Show Just This Thread        Post Reply
Access Work Order Seminar Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply
Comment from Anthony Santiago @ 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?
Show Just This Thread        Post Reply
Comment from  Sandy @ 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?  
Show Just This Thread        Post Reply
Comment from  Sandy @ 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.
Show Just This Thread        Post Reply
Comment from Dan @ 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.
Show Just This Thread        Post Reply
Comment from  Access Noob @ 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.
Show Just This Thread        Post Reply
WHERE Condition Not Working Comment from  Access Noob @ 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.
Show Just This Thread        Post Reply
Thank You Comment from Kim @ 11/19/2010
I didn't notice that there was two database and I had just downloaded the sample one...
Show Just This Thread        Post Reply
Comment from chris smith @ 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.
Show Just This Thread        Post Reply
Comment from  Mary @ 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.
Show Just This Thread        Post Reply
Comment from  Kim @ 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.
Show Just This Thread        Post Reply
multiple Owners Comment from Kim @ 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.
Show Just This Thread        Post Reply
Comment from  Kim @ 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.
Show Just This Thread        Post Reply
Comment from  Dmitriy @ 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.
Show Just This Thread        Post Reply
Comment from  David @ 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?
Show Just This Thread        Post Reply
Comment from  David @ 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?
Show Just This Thread        Post Reply
Comment from Andrea Martin @ 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.
Show Just This Thread        Post Reply
Comment from Andrea Martin @ 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
Show Just This Thread        Post Reply
Comment from Joy Cunningham @ 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.
Show Just This Thread        Post Reply
Comment from  Brian @ 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.
Show Just This Thread        Post Reply
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:

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.
Show Just This Thread        Post Reply
Comment from Mubeezi Micah @ 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.
Show Just This Thread        Post Reply
Comment from Mary Franklin @ 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.
Show Just This Thread        Post Reply
Create New Customer Button Comment from Mary @ 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
Show Just This Thread        Post Reply
I have more than one computer Comment from Dan Wong @ 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.
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 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
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 5+3:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

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

7/19/2017Access Developer 2 and 3 Released
7/19/2017Microsoft Access Developer 3
7/19/2017Microsoft Access Developer 2
6/30/2017Access Developer 2 is Finished
6/7/2017Microsoft Access Developer 1
6/6/2017Access Developer 1 is Finished
5/18/2017Microsoft Access Advanced 6
5/17/2017Access Advanced 6 is Online
4/9/2017Microsoft Access Advanced 5
4/9/2017Microsoft Access Advanced 4
 
  Dismiss
 

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