Creating a custom PK with autonumber (1 Viewer)

[biofaku]

Hi, I was trying to code an autonumber for the PK of a Table that only ID field and a text field.
I came with this:

Option Compare Database
Option Explicit

Public Function Nextus() As String


Dim pref As String
Dim lastCase As String
Dim csNum As Integer
Dim nxtCase As Integer
Dim anno As String

anno = Format(DatePart("yyyy", Date), "0000")

             'create prefix
  pref = "VAL-NA-" & anno & "-"


If IsNull(lastCase = DMax("[ID]", "Table1", Left("ID", (Len(pref))))) Then
lastCase = 0
Else
lastCase = Right(DMax("[ID]", "Table1"), 1)

End If



csNum = Left(lastCase, Len(pref) + 2)

nxtCase = csNum + 1
    
Nextus = pref & nxtCase

End Function

It works mostly fine except that when it counts to ten, it stops counting, and I don't get it why.

Could someone give me some advice how to fix that?

Here is an image of how it looks.

Thanks

 

[Gasman]

Why not just use the one Access generates? You can make up your identifiable 'key', but always use the Access autonumber for related records.

However I'd guess this is not correct?

astCase = Right(DMax("[ID]", "Table1"), 1)

As it is text the max will be the -9 and then you just are adding to it.

I would walk through the code line by line with F8 and inspect where it is going wrong, but I'd bet on that.
If you used actual numbers, you woul just add 1.

Experts here recoomend not to mash data together like that, so you would have VAL-NA- as a constant, year as a field and last digits as another field, but numeric.?

HTH

 

[biofaku]

Why not just use the one Access generates? You can make up your identifiable 'key', but always use the Access autonumber for related records.

Hi, thanks for your reply. I thought about using the one that access generates, but I need it that way for regulatory reasons.

 

[theDBguy]

It works mostly fine except that when it counts to ten, it stops counting, and I don't get it why.

I think the reason for that is because your field is a String/Text. In other words, DMax() will *always* return "VAL-NA-2020-9" as the highest value, because "2020-9" is greater than "2020-10." In other words, "3" is greater than "22."

 

[Gasman]

Hi, thanks for your reply. I thought about using the one that access generates, but I need it that way for regulatory reasons.

You can have whatever you want to show, but use the Autonumber for relations. User should never get to see it. I used to like to show/see it so I can debug easier.

 

[Isaac]

Agree with Gasman. Always use a PK with no embedded business meaning (etc) behind the scenes.
Create an additional business-facing column with whatever they like to see (regulatory etc)

 

[biofaku]

I think the reason for that is because your field is a String/Text. In other words, DMax() will *always* return "VAL-NA-2020-9" as the highest value, because "2020-9" is greater than "2020-10." In other words, "3" is greater than "22."

I supposed it could be that... Gonna look how to get a workaround it. Thanks DBguy!

 

[theDBguy]

I supposed it could be that... Gonna look how to get a workaround it. Thanks DBguy!

One workaround is to use a Number field or convert the Text field into a numeric value. Good luck!

 

[The_Doc_Man]

There is nothing wrong with having a complex primary key. HOWEVER, the moment you try to use "autonumber" you have just shot yourself in the foot for the complex key. In Access, an autonumber has one and only one purpose - to provide a unique key for the purpose of identifying individual records via key-based relationships. An autonumber makes a lousy display key.

You can build any kind of identifying key for the record for business rules and can even search using it. But the autonumber value cannot be modified in format or content to suit some regulatory requirement. It is always and only an internal-purposes key.

 

[namliam]

Concure with other contributers, use a autonumber inner database key for inner database needs.

Compound user keys like this are best of storing in seperate fields, thus in your database use atleast two fields or even three or four fields to store the seperate character part(s),
Field 0: Autonumber field for the database
Field 1: Stores VAL
Field 2: Stores NA
Field 3: Stores your year in an integer or stores the exact date and you extract the year for display purposes
Field 4: is the counter, which can be created quite easily with a DCount or DMax function based on the previous three fields.

Trying to mix and match different functions is just making this hard on you.

 

[jdraw]

I agree with the others in that use the built in autonumber for Unique values. You can concoct a code of your own choosing including/excluding an autonumber as part of your code.

However, if you are looking for sequential numbering -with no gaps in sequence - then look to Dmax(yourNumber) +1.
Autonumbers are assigned when record is first being created. If you change your mind, or some edit/error occurs, and the record gets cancelled/considered incomplete --then that autonumber is lost and cannot be reused. And your sequence has a gap(s) simply because of the way Access is designed and works.
Good luck with your decision.

 

[biofaku]

I agree with the others in that use the built in autonumber for Unique values. You can concoct a code of your own choosing including/excluding an autonumber as part of your code.

However, if you are looking for sequential numbering -with no gaps in sequence - then look to Dmax(yourNumber) +1.
Autonumbers are assigned when record is first being created. If you change your mind, or some edit/error occurs, and the record gets cancelled/considered incomplete --then that autonumber is lost and cannot be reused. And your sequence has a gap(s) simply because of the way Access is designed and works.
Good luck with your decision.

Hi jdraw, thanks for your input!
At first I used that option, but then I wondered if I could format the entire thing from vba, and it mostly worked but well, then it doesn't work past the number ten. I think im going to give up the full code option and just use the Dmax option.

 

[arnelgp]

change your function to:

Public Function Nextus() As String
    Dim pref As String
    Dim anno As String
    Dim strNext As String
    anno = format(DatePart("yyyy", Date), "0000")

    'create prefix
    pref = "VAL-NA-" & anno & "-"

    strNext = DMax("Val(Mid(ID,13))", "Table1", "Left(ID,12) = '" & pref & "'") & ""
    If Len(strNext) = 0 Then
        strNext = pref & "1"
    Else
        strNext = pref & Val(strNext) + 1
    End If
    Nextus = strNext
End Function

 

[biofaku]

Hi Arnel! That worked perfectly as I wanted to!
Thanks Man!

 

[arnelgp]

You're welcome!

 

[biofaku]

Just one more thing, that would be cool to fix if its possible. When I arrange the results on that column on ascending it messes the order when reachs the 100 records, any idea how to make sure that goes subsequently? I was starting to consider having that autonumber column for that purpose.

Thanks a lot again

 

[arnelgp]

Create a query and sort it there on expression:

year + format(seqn, "000000")

you need to parse the year part and the seqn part.

 

[arnelgp]

Autonumber will not be in Sequence always. If you add record then cancel it, the autonumber has already advanced by 1.

Say on empty table, you try adding new record then cancel it, next time you add new record, the autonum will start with 2 not 1.

 

[namliam]

sorry but no no no no no... mixing characters and numbers and expecting proper results.... no no no.

Split your columns as per my earlier post, make your own life easier by so many ways ... 4 seperate columns for four seperate needs.
KISS
Keep characters characters
Keep numbers numbers
Keep dates dates (year is part of a date)

VAL will change to VALL or some other change
NA will change to N or NOW or some other change
2020 will change to 20 without the century or change to 202001 to include the month.
Dont say it WONT change, believe me it will ! Eventually any business rule will change!

Store your values in seperate columns as per my earlier post and you wont have any problems with any of these changes nor will you have any trouble sorting your data in any way shape or form you want.

 

[biofaku]

Just came back to tell you that everything is working fine! Thanks a lot for your help