Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011

    Question Unanswered: Creating a Unique Autonumber in Access 2010

    I am trying to create a unique autonumber in Microsoft Access 2010.

    When an entry is created in a form, I would like the form to automatically create a "job number" for the entry. The format should be in:


    Where YY stands for the last two digits of the year, MM stands for the 2 digit month, DD stands for the two digit day, and XX stands for the two digit job of the day. For example if it was the first job created then XX would equal 01 and if it was the second job of the day then XX would equal 02.

    Here is a code that I found on the forums that is supposed to do what I want, but I cannot figure out how and where to implement it.

    Private Sub Work_Request_Click()
    ' Code made by Vestigo, 051203
    ' Add this code to a button on your form.
    ' Create the table 't-Work' with one autonumber field (called Auto, not important)
    ' and one text field (length 8) called 'Work_Request_Number'
    ' Put in some figures to 'Work_Request_Number' in your table, like:
    ' 04120301
    ' 05120301
    ' 05120302
    ' Manipulate the 'Work_Request_Number' in order to test the code

    Dim Today As String, Last As String, Next_Request As String, Next_Count As Integer
    Today = Format(Now(), "ddmmyy")
    Last = Left(DLast("Work_Request_Number", "t-Work"), 6)

    If Today = Last Then
    Next_Count = Int(Right(DLast("Work_Request_Number", "t-Work"), 2)) + 1
    Next_Request = Format(Now(), "ddmmyy") & Right("0" & Next_Count, 2)
    Next_Request = Format(Now(), "ddmmyy") & "01"
    End If

    MsgBox "Next_Request (to be inserted to your table): " & Next_Request
    End Sub

    Thank you for the help.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so what steps have you taken to understand what the code is doing
    what steps have you taken to see if it does match your requirement

    where does the code fail
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2003
    For a start, if you want the format to be YYMMDD, you are going to need to make some obivous amendments to the code, otherwise it will produce DDMMYY...

    Today = Format(Now(), "ddmmyy") ...
    Next_Request = Format(Now(), "ddmmyy") ...
    Next_Request = Format(Now(), "ddmmyy") ...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts