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...
Yes, it's possible via the Dynamic Azure AD Groups functionality: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-groups-dynamic-membership-azure-portal
The membership rule you need is: (user.userType -eq "Guest")
I can't recommend one over the other. In this, as in many similar situations, I believe the course of action that leads to the greatest combination of successful outcomes AND new knowledge is the old-fashioned, perhaps boring, "Try each of the approaches mentioned and see which one suits you best."
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")- 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?
- 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
