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.

Sequential numbering in access based on another field

Discussion in 'Business Applications' started by Welshcharles, May 7, 2009.

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

    Welshcharles Thread Starter

    Joined:
    May 7, 2009
    Messages:
    5
    I am trying to create a database, to keep track for stores orders for the ship i am on.
    I want to auto generate the stores order number (Order Number) in the format
    0730/TAK/yy/$$$
    The 0730 is fixed as is the TAK. The yy is 2 digit year and the $$$ is the sequential number.
    The yy field i want to take the year from the "Order Date" field in the same form.

    IS this possible?
    I have Access 2007 but the final database may have to run on 2003.
    The database is still in early stages so is still small can be forwarded if it helps

    Many Thanks
    Charles
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Charles, yes :)

    Seriously, it requires some vba code and a simple query which will list either all of the previous Order Number values or just the last value for that field. You definitely want it based on the Form's Order date field and not the Current Year?
     
  3. Welshcharles

    Welshcharles Thread Starter

    Joined:
    May 7, 2009
    Messages:
    5
    Hi OBP
    Thanks for the quick response. Yes I would need it based on the Order Date field as some orders may be enter after being placed, ie the order could be placed via phone call and not entered until later. I know this is a unlikely possibility but i would like to get the code right at the start tan trying to correct things at a later date.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    OK, if you create the query and let me have it's name and I will create you some code.
    The code will have to go in the Order Date field's After Update event procedure, because it must be entered before you can create the Invoice Number.
     
  5. Welshcharles

    Welshcharles Thread Starter

    Joined:
    May 7, 2009
    Messages:
    5
    Thanks for the quick response again.
    I have created a query for all order numbers called qryOrderNos

    Thanks again
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    I forgot to say what order are they in?
    Is the last Invoice Number the last record or the first record?
     
  7. Welshcharles

    Welshcharles Thread Starter

    Joined:
    May 7, 2009
    Messages:
    5
    The last number is the last record
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Ok I am not certain that this will work without some tweeking, so if it errors out make a note of the error and get back to me.
    Here is the VBA code, it opens the query, looks at the last record to see if the year is the same year as the Order Date, if it isn't it will start the number at 001.
    If it is the same then it will increment the number.

    On Error GoTo Eventerror
    If Me.NewRecord Then
    Dim rs As Object, zeroes As String, temps As String, tempv As Integer
    zeroes = "000"
    Set rs = CurrentDb.OpenRecordset("qryOrderNos")
    rs.movelast
    If rs.RecordCount <> 0 And mid(rs![InvoiceNumber],9,2) = Format(me.[Order Date], "YY") Then
    tempv = Val(Right(rs![InvoiceNumber], 3)) + 1
    temps = Str(tempv)
    temps = Right(temps, Len(temps) - 1)
    If Len(temps) < Len(zeroes) Then temps = Left(zeroes, (Len(zeroes) - Len(temps))) & temps
    Me.InvoiceNumber = Left(rs![InvoiceNumber], 9) & Format(me.[Order Date], "YY") & temps
    Else
    Me.InvoiceNumber = "0730/TAK/" & Format(me.[Order Date], "YY") & " - 001"
    End If
    rs.Close
    Set rs = Nothing
    End If
    Exit Sub
    Eventerror:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
     
  9. Welshcharles

    Welshcharles Thread Starter

    Joined:
    May 7, 2009
    Messages:
    5
    I have tried the code and it is not running right. I have made a couple of mods but still no joy.
    Could i email you the DB?
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Yes of course, it is what I expected, it is difficult working in VBA without the "objects". :)
     
  11. 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...
Similar Threads - Sequential numbering access
  1. Jack1000
    Replies:
    4
    Views:
    285
Thread Status:
Not open for further replies.

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

  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