Results 1 to 9 of 9
  1. #1
    KG2209 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    3

    How do I automatically generate a unique ID that uses year and then a sequential number sequence.

    Hi!



    I am very new to access so first of all, I would like to apologize if I talk utter nonsense!

    I would like to create a field that appears on both a form and the main table my form is linked to. I need this unique ID to automatically generate for every new record. The ID needs to be the last 2 numbers of the current year followed by a 4 digit sequential number (YY-0000) so 17-0001, 17-0002 then 18-0001. I need this number to reset at the start of each year.

    I have read what seems like hundreds of forum threads in the general area but I have not found anything that has worked for my particular needs.

    I would greatly appreciate any help! I would prefer if possible an alternative to VBA as like access I am brand new to it but I am open to options!

    Thank you in Advance!
    Kerry

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Folks keep wanting to do this and there is no need.
    autonum creates the unique ID. Putting the year in a field gives:

    id, year
    123, 2017
    124, 2017
    etc...

    sorting gives you the order for year 2017.
    otherwise you must create lots of code in order to create the same thing: 17-0001, 17-0002

    no need. But a report can produce the 1,2,3 for you w/o programming, if you must have it.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the BeforeInsert event on the form where you are adding a new record, use this code, change the names to match yours (it isn't a bad thing to learn VBA!):

    Code:
        Dim strY As String, strN As String
        On Error Resume Next
        
        strY = Format(Date, "yy")
        strN = DLookup("Max(nbrfield)", "table1", "yearfield='" & Format(Date, "yy") & "'")
        If IsNull(strN) Or strN = "" Then strN = "0"
        strN = Format(CInt(strN) + 1, "0000")
        Me!bothfields=stry & "-" & strn

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    KG2209,
    Do you want a unique number? Or do you want to create some concocted code?
    Relational databases work extremely well with atomic fields --that is store 1 fact in 1 field. As soon as you try to codify -year-number.... to store a value, you are not using 1 fact 1 field. Now if you want to display your concocted code that may have some meaning to the user, the you can concatenate field values from storage to do so.

    This sort of thing:
    MyCode= Year(some stored Date) & "-" & Some Number + 1

  5. #5
    KG2209 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    3
    Hi All,

    Thank you very much for your replies!

    Just to clarify a little. This is a work project. I work in a laboratory. Every certificate we issue has a unique ID number which is the year (yy) followed by a sequential number (17-0001, 17-0002). It is definitely not how I would choose to number jobs with access in mind but that is the format the company uses and I cannot change it.

    It does not have to replace the primary key ID but I need that date/number to auto-generate in a field on my form/table.

    My table name is "Calibration Details Table" and my form name is "Calibration Details Form".

    aytee111- Thank you very much for your help. I agree I think it would definitely benefit me to learn VBA. I can't help but think I am trying to run before I can walk with this one! With that being said I tried to input the code you gave me and I cannot get it to work (I am sure this is down to me doing something wrong!). I changed the "Table1" name and the "yearfield" to match mine. Is that correct?


    Again thank you, everyone, for your help.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There will be two fields on your table, as Orange points out.
    Yearfield - a text 2-digits for the year ("17")
    Nbrfield - a number integer field holding the sequential numbering

    On your form, in the BeforeInsert event:
    Code:
    Me!Yearfield=Year(Date())
    On Error Resume Next
    Me!NbrField=DLookup("Max(Nbrfield)", "tablename", "Yearfield='" & Year(Date()) & "') + 1
    If IsNull(Me!NbrField) Or Me!Nbrfield="" then Me!NbrField=1
    Me!BothFields=Me!Yearfield & "-" & Format(Me!Nbrfield,"0000")
    If this doesn't work, please provide specific details.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Common topic, here is another discussion https://www.accessforums.net/showthread.php?t=67421
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    KG2209 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    3
    Hi Guys,

    Thank you so much for your help that seems to be working great! Is there a way to get the numbers to reset back to 0001 when it turns 2018?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That is demonstrated in another thread referenced in the referenced link. But here is the link https://www.accessforums.net/showthread.php?t=23329
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-09-2016, 12:33 PM
  2. Replies: 12
    Last Post: 11-23-2016, 02:55 PM
  3. Auto generate sequential number
    By krai in forum Access
    Replies: 13
    Last Post: 08-17-2015, 06:05 AM
  4. Replies: 3
    Last Post: 12-07-2013, 01:12 AM
  5. Replies: 1
    Last Post: 05-07-2012, 08:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums