SOLVED

Auto number with prefix

Copper Contributor

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 table (table2) where all values are displayed to my liking as for example 20230009. Everything worked quite well to the point of creating a query where I wanted to link the foreign key back to the primary key, it did not work. It only works when I manually type in 9 instead of 20230009 for example.

Does anybody have an idea how to fix this problem?

 

Best regards

Moritz

9 Replies

@Moritz1003 

Please do not try to store a concatenated value like this in one field.

And even more importantly, please do not use the AutoNumber datatype for this purpose. It is guaranteed only to provide unique values. It can not be counted on to provide sequential values with no gaps between them. If you use this approach, you will inevitably have such gaps and people will see them. People will wonder why there are missing values. It's possible, but not very wise. There are better ways to achieve the goal.

 

Instead, create a field for the "prefix", which is obviously the current year. It can default to Year(Date()).

 

In the second field, create the sequential values you want to display to users. This is an excellent extended discussion of how to accomplish that, with links to examples.  

It includes a reference to restarting the sequence each year, which I suspect is also in your sights for this feature.

Okay, thank you for your reply, do I understand correctly that what you are proposing is creating a second field in table1 for the prefix? What do you mean by "In the second field, create the sequential values you want to display to users? I am pretty new to Access and VBA, I think I need a little more explaining on what to actually do :\ What kind of field will the second field be and where do I insert the code?
In the link included in my reply you'll find an extended discussion of how to create and use the sequential numbers you need. And yes, the prefix should be in its own field, called something like "ClaimYear". Set its default value to Year(Date()). That will automatically put the current year in that field for all new records.
Okay I got the part with the prefix now, my database will be used by not more than 10 people. In the link you sent there are 3 different approaches as far as I am concerned, is the one by Edward Leno sufficient? And if so could you maybe help me understand it better as these instructions are not enough for me to understand?
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?
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?
best response confirmed by Moritz1003 (Copper Contributor)
Solution
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
1 best response

Accepted Solutions
best response confirmed by Moritz1003 (Copper Contributor)
Solution
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

View solution in original post