Custom Numbering (1 Viewer)

Jpowell

New member
Local time
Today, 17:42
Joined
Jul 11, 2018
Messages
4
I am trying to find away to Create an auto number for our project numbering system. It is formated as PR01A2018. Pr Stays the same, The number changes as new projects the letter is the month (A = January, B = February, Ect) and the year as the date entered. Is this possible or am I wasting my time?
 

plog

Banishment Pending
Local time
Today, 16:42
Joined
May 11, 2011
Messages
11,611
Is this possible or am I wasting my time?

Yes, any algorithm you can logically define is possible.

Yes, you are wasting your time.

What do you hope to accomplish with this custom numbering? What benefit does it provide over an autonumber?
 

Jpowell

New member
Local time
Today, 17:42
Joined
Jul 11, 2018
Messages
4
We have a current format now on our numbering system and I am in charge of now putting these into a database as they come in and would like to make it auto number with the current format we use.
 

Jpowell

New member
Local time
Today, 17:42
Joined
Jul 11, 2018
Messages
4
Do you use a Month as a letter format in your table? Thanks, I browsed some of these before I posted. I just wanted to know if my format is possible.
 

June7

AWF VIP
Local time
Today, 13:42
Joined
Mar 9, 2014
Messages
5,423
Certainly your ID format is possible.

No, I do not use a month identifier as part of the ID.

Whether or not you use this custom ID as the linking primary/foreign key is a different question. Many here would suggest you should use autonumber as the (hidden) link and use the custom ID for users to reference.
 

Jpowell

New member
Local time
Today, 17:42
Joined
Jul 11, 2018
Messages
4
This is what I would like to do but I'm not sure how to go about this with the user reference key I'm trying to create. Unless it's manually typed.
 

June7

AWF VIP
Local time
Today, 13:42
Joined
Mar 9, 2014
Messages
5,423
I provided example code for a function that creates unique identifier. Modify as you see fit.

The real trick is figuring out what event to call the function from. There is risk that multiple users could generate the same number in which case will get error for whoever is not first to save record if you have the field set to not allow duplicates, as should be.

Other code calls the function to create the identifier and immediately commits record - because users have to see the SampleID right away - then opens that record for them to complete other data. I have never had the same number generated by multiple users. My code also allows for user to abort record creation and since the SampleID must be accounted for, code 'blanks' the record except for the SampleID field and the saved record is pulled up for next user to enter a new sample.

Alternatively, you can have users do data input and create the new SampleID after all input and commit record at that time.

The more 'user friendly' the more code.
 
Last edited:

Mark_

Longboard on the internet
Local time
Today, 14:42
Joined
Sep 12, 2017
Messages
2,111
@ OP,

To be fairly clear regarding the advice you are being given...

To link records or to track a specific record you will want to use an auto number primary key that cannot be edited. This avoids many issues related to users doing strange things.

If you want a short hand "Number" that end users can reference you will want to save that as a different field that is NOT used to link records or to uniquely identify a given record.

What you are looking or is "Sequential numbers in a given month". This is normally accomplished by having two fields in your record, AddDate (The date you create the record, you probably have one anyhow) and "Sequence". You then use DMax() to find the highest sequence number for a given month, often by having a query that lists all records by yyyymm and sequence.

You then put together the pieces you need to display your reference. In your case it becomes "PR" & FORMAT(YourSequenceNumber,"00") & SomeFunctionYouWriteToReturnLetterBasedOnMonthNumber(Month(AddDate)) & FORMAT(YEAR(AddDate),"yyyy")
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:42
Joined
Sep 12, 2006
Messages
15,614
Personally, I would have both in some cases.

Have an autonumber, and use that to relate this table to other tables.

Than have your own specially designed unique reference value, and store this in your table, without it being used as a foreign key anywhere. That way your table links remain simple (numeric), and you can change your internal numbering system anytime you want. It might be easier to make your internal numbering multi-field, rather than single field - eg, year, contract, serial number. This way you don't have the pain (and it is a pain) of constructing multi field links between tables.


as Mark said above.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:42
Joined
Jan 20, 2009
Messages
12,849
. . . have your own specially designed unique reference value, and store this in your table, without it being used as a foreign key anywhere.That way your table links remain simple (numeric), and you can change your internal numbering system anytime you want.

The convenience of this cannot be overestimated :)confused:eek:r do I mean underestimated ?) especially while attempting to merge data from disparate databases.

It might be easier to make your internal numbering multi-field, rather than single field - eg, year, contract, serial number. This way you don't have the pain (and it is a pain) of constructing multi field links between tables.

Yes, remembering that anything that purports to be a key in in the eyes of the user aught to be treated as a candidate key. For example, we shouldn't have two formula derived "invoice numbers" identical even if we can tell the records apart in the database. Hence logical models and indexes need to be arranged to ensure only a deterministic unique value is presented for each record regardless of how it is derived.

But of course you wouldn't want something that complex and vulnerable as your primary key to link other tables.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:42
Joined
May 21, 2018
Messages
8,463
a simple version without the concurrent user checks.
Code:
Public Function NewProjID(theDate As Date) As String
   Dim strProjNum As String
   Dim Counter As Integer
   Dim MonthID As String
   Dim strYear As String
   MonthID = GetMonth(theDate)
   strYear = CStr(Year(theDate))
   If strProjNum = "" Then
        Counter = DCount("*", "tblProjects", "[Project_ID] like '*" & MonthID & strYear & "'")
   End If
   NewProjID = "PR" & Format(Counter + 1, "00") & MonthID & strYear
End Function

Public Function GetMonth(theDate) As String
    GetMonth = Chr(64 + Month(theDate))
End Function
 

Users who are viewing this thread

Top Bottom