Forum Discussion
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...
- 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
I tried the solution that was added in the Edit 1 of the question (https://scottgem.wordpress.com/?s=dmax) where I adjusted the code of the 2nd version. Again I am not that familiar and tried using Chat Gpt to help me and this is the code I came up with:
Option Compare Database
Private Sub Sequence_GotFocus()
Dim currentYear As Integer
' Aktuelles Jahr aus dem aktuellen Datum ermitteln
currentYear = Year(Date)
Debug.Print "Aktuelles Jahr: " & currentYear
Dim strSQL As String
strSQL = "Year([Claim_Year]) = " & currentYear
Debug.Print "SQL: " & strSQL
Dim maxSeq As Variant
maxSeq = DMax("[Sequence]", "_Auto_Number", strSQL)
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(currentYear, "yyyy") & "-" & Format(Me.Sequence, "0000")
End Sub
I have the fields: Sequence (that "stores the code") , Claim_Year (that always gives out the current year and SequenceDisplay.
Right now when I click in the field Sequence, in the field SequenceDisplay 1905-0001 appears, what am I doing wrong?
Option Compare Database
Private Sub Sequence_GotFocus()
Dim currentYear As Integer
' Aktuelles Jahr aus dem aktuellen Datum ermitteln
currentYear = Year(Date)
Debug.Print "Aktuelles Jahr: " & currentYear
Dim strSQL As String
strSQL = "Year([Claim_Year]) = " & currentYear
Debug.Print "SQL: " & strSQL
Dim maxSeq As Variant
maxSeq = DMax("[Sequence]", "_Auto_Number", strSQL)
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(currentYear, "yyyy") & "-" & Format(Me.Sequence, "0000")
End Sub
I have the fields: Sequence (that "stores the code") , Claim_Year (that always gives out the current year and SequenceDisplay.
Right now when I click in the field Sequence, in the field SequenceDisplay 1905-0001 appears, what am I doing wrong?
That should be:
Me.SequenceDisplay = Format(Date, "yyyy") & "-" & Format(Me.Sequence, "0000")
Me.SequenceDisplay = Format(Date, "yyyy") & "-" & Format(Me.Sequence, "0000")
- 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 - 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?