Determining next value in a sequence

[Johnny Drama]

Here's a good one for you access gurus:

I have a primary key field that consists of values such as ENT01, ENT02, COMP01, COMP02, etc.

The user will need to increment the value to the next value in the sequence when they create a new record and I would like some way for them to be able to see what the last value in the sequence was, possible through a popup that would simply say the next value in the sequence.

Any thoughts?

Thanks in advance

 

[Banana]

The common answer is to use DMax() to get the highest value from the appropriate value, add by one. This of course has issues with concurrent multi-user setting.

 

[Johnny Drama]

Fortunately the number of users will be minimal so there shouldn't be much of a concurrent user issue. I'm not a code guy by any means...do you ahve an exampled of the dmax() plus one you mentioned?

 

[DCrake]

What is the likelyhood of having more than 99 for each suffix? It may be wize to increase the mask to cover 999 possible entries. Better to be safe than sorry.

Create a query that has two columns

Col 1
Left(PK,3)

Col 2
Right(PK,3)

Sort Descending on Col 2 (highest first)

Then when finding the next sequential number

NextId = StrPrefix & [COLOR="royalblue"]Format[/COLOR]([COLOR="YellowGreen"]Nz[/COLOR][COLOR="Red"](DLookup[/COLOR]("Col2","Query","Col1='" & StrPrefix & "'"),0) +1,"000")

SrtPrefix is the non numeric prefix of the PK

DLookup(Value,Domain,Condition) gets the matching record

Nz() handles no matches
Format converts 1 to 001

NextId = ENT003

You may note that I have split the PK into 3 alpha 3 numeric for ease of coding. If you want variable length non numeric prefixes then you will need to adjust the code accordingly.

David

 

[Johnny Drama]

The likelihood of more than 99 numbers of the suffix is is extremely low. Also, while you have split out the primary key into an alpha and a numeric, I won't be able to do that.

I'll try to figure out how to adjust the example you gave me without destroying it too badly.