Forum Discussion

Moritz1003's avatar
Moritz1003
Copper Contributor
Sep 04, 2023
Solved

Auto number with prefix

Hello,   In my database I have a table (table1) with the primary key (PK) Internal Claim Number (ICN) as an auto number with the format "2023"0000 (2023 as the year). This PK is linked to another t...
2016
access
Auto number
  • Moritz1003's avatar
    Moritz1003
    Sep 11, 2023
    For anyone that wants to recreate this, the problem was the "strSQL" part in the DMax function. Here is the code that works for me:
    Private Sub Sequence_Click()

    Dim currentYear As Integer

    ' Aktuelles Jahr aus dem aktuellen Datum ermitteln
    currentYear = Year(Date)

    Dim strSQL As String
    strSQL = "Year([Claim_Year]) = " & currentYear

    Dim maxSeq As Variant
    maxSeq = DMax("[Sequence]", "_Auto_Number")

    If IsNull(maxSeq) Then
    Me.Sequence = 1 ' Wenn keine Datensätze für das aktuelle Jahr vorhanden sind, beginnen Sie mit 1.
    Else
    Me.Sequence = maxSeq + 1
    End If

    Me.SequenceDisplay = Format(Date, "yyyy") & "-" & Format(Me.Sequence, "0000")


    End Sub
Moritz1003's avatar
Moritz1003
Copper Contributor
Sep 07, 2023
That works, great, thank you so much! But now the Sequence number doesnt count up and stays at 1 :/. Does someone know how to fix that aswell?
Moritz1003's avatar
Moritz1003
Copper Contributor
Sep 11, 2023
For anyone that wants to recreate this, the problem was the "strSQL" part in the DMax function. Here is the code that works for me:
Private Sub Sequence_Click()

Dim currentYear As Integer

' Aktuelles Jahr aus dem aktuellen Datum ermitteln
currentYear = Year(Date)

Dim strSQL As String
strSQL = "Year([Claim_Year]) = " & currentYear

Dim maxSeq As Variant
maxSeq = DMax("[Sequence]", "_Auto_Number")

If IsNull(maxSeq) Then
Me.Sequence = 1 ' Wenn keine Datensätze für das aktuelle Jahr vorhanden sind, beginnen Sie mit 1.
Else
Me.Sequence = maxSeq + 1
End If

Me.SequenceDisplay = Format(Date, "yyyy") & "-" & Format(Me.Sequence, "0000")


End Sub

Resources