Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2017

    Unanswered: Auto-number Issue

    I am converting a database built in Access 2003 to Access 2013 and the following auto-number function is not working as it did in the older version.

    Private Sub EnteredBy_AfterUpdate()
    If Me.NewRecord Then
    Dim strWhere As String
    Dim varResult As Variant

    strWhere = "FileNo Like """ & Format(Date, "yy") & "*"""
    varResult = DMax("FileNo", "tblDND728Register", strWhere)

    If IsNull(varResult) Then
    Me.FileNo = Format(Date, "yy") & "-0001"
    Me.FileNo = Left(varResult, 3) & _
    Format(Val(Right(varResult, 4)) + 1, "0000")
    End If
    End If
    End Sub

    Even though I know it's not the best solution I need this format for regulatory reasons.
    My records must show the last two characters of the current year and then a 4 digit serial number.

    When I use the above code, the YY portion is not translating to the new year - it is just continuing from the previous entry in the table which was 16-0001.
    What I get now is 16-0002, 16-0003 etc.

    Any assistance would be greatly appreciated.

  2. #2
    Join Date
    Apr 2014
    Provided Answers: 32
    Did you put a stop point on:
    Me.FileNo = Format(Date, "yy") & "-0001"

    To see if the code actually hits it?
    This WILL produce year 17, but if the logic skips it, it may not.
    It could be the IF condition.

  3. #3
    Join Date
    Mar 2017
    I'm sorry but I don't know what a stop point is
    How do you accomplish this?

Posting Permissions

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