1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Question

Discussion in 'Business Applications' started by jmo422, Oct 1, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. jmo422

    jmo422 Thread Starter

    Joined:
    Dec 30, 2007
    Messages:
    34
    I have a table with various information about work orders done in our shop.

    When I am adding a new job, I would like access to look at the last record or highest work order number and add 1 to the number for the next job, I thought you could possibly do a query and put +1 in the criteria, but what type of query would it be. Or maybe I am totally off.

    Thanks

    Jay Morlan
     
  2. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Just add the field data type in your table definition of AutoNumber, this automatically increments by one; call it something like OrderID.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As MRdNK says the easiest way is to use the built in Autonumber feature, it does have some drawbacks though.
    If you create and then cancel a new record, or delete the last record the Autonumber can't go back and use that number again, so you have blanks in your Work Order Numbers. If you are not allowed Blanks in the Work Order sequence you have to resort to VBA Code to increment the number instead.
     
  4. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Or use VBA to re-use old deleted record ID numbers.
     
  5. jmo422

    jmo422 Thread Starter

    Joined:
    Dec 30, 2007
    Messages:
    34
    I dont have any experience with VBA Code, but willing to try. How would I go about using VBA to get the numbers to move in increments of one.

    Thanks

    Jay Morlan
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jay, what form do you want the Work Order Number to take, is it a special Format, do you want to show preceding zeroes?
    i.e. 0001, 0002 to 9999
    or
    2008 - 0001 to 2008 - 9999
    changing to
    2009 - 0001 to 2009 - 9999
    next year.
     
  7. jmo422

    jmo422 Thread Starter

    Joined:
    Dec 30, 2007
    Messages:
    34
    I do not have to have the date in front of the work order, currently we use a four digit number such as 7468, which is the number that we are currently on as the weekend begins. I just would like the work order field in the table and form to automatically go to 7469 and so on.

    Thanks

    Jay Morlan

    Also while I am on here, I have yet another problem, in my Work Orders Table I have two hyperlinks that take me directly to the pictures of the unit that correspond with the work order and I have another hyperlink that takes me to the quote that has been saved for the work order. The picutures being located in the S:\Work Order Pictures\Work Order # Folder and the Quotes being saved under F:\2008 Quotes\OCT 2008\Work Order #. How can I get these two hyperlink fields to automatically fill in the first part of the file location and then read off the work order number and fill that part in as well.

    Thanks

    Jay Morlan \
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jay, I will be able to do something for you on the VBA for the automatic incrementing of the PO number, possibly by Monday.
    I do not use Hyperlinks, I have a Field on the Form that shows the photo or Document which when double clicked opens the actual Photo or Document.
    I use a VBA "Browser" to browse to the required file.

    Are you in the USA?
     
  9. jmo422

    jmo422 Thread Starter

    Joined:
    Dec 30, 2007
    Messages:
    34
    I am located in the US

    I appreciate you helping me with these questions.

    Thanks

    Jay Morlan
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jay, what is the actual Name of your PO Number Field in the table and on the Form?
    I also need the name of the Table as well.
    It would be easier still if you could take a Copy of your database, delete all the records and then Compact & Repair it, Zip it and post it on here as an Attachment.
     
  11. jmo422

    jmo422 Thread Starter

    Joined:
    Dec 30, 2007
    Messages:
    34
    Field Name- Work Order
    Table- Lead Time Dates
    Form - Lead Time Dates
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Jay, try this in the After Update Event of the First Field on the Form that is not the Work Order Field, the Work Order Field Property "Enabled" should be set to "No".

    Dim rs As Object, sql As String, code As String, last As Integer
    sql = "SELECT Lead_Time_Dates.* " & _
    "FROM Lead_Time_Dates "
    Set rs = CurrentDb.OpenRecordset(sql)
    rs.MoveLast
    last = rs![Work Order]
    rs.Close
    Set rs = Nothing
    Me.Work_Order = last + 1

    Note the Underscores "_" in the Table's Name, you will need to add those to your Table's name as well, VBA created SQL does not like Table names with spaces in them.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755255

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice