I have had some thoughts on this as there's a 2 fold reason why I had some
thoughts on this. However, this will require some additional coding and one
additional table for all tables that will use such method.
Before you setup this table though, you should ask yourself the following
questions and answer them, which then once answered, you could then setup
your additional table and code to fit your needs.
Will the record number begin with a certain code on all records, such as
"REC" for Recieved?
Will the record number end with a certain code on all records?
What is the spacing between each record number (10, 20, 30, etc...)
Does the record number require a check digit? (highly recommended for a
higher chance of catching data entry error)
If a check digit is required, what is the calculation to come up with a
check digit based on the rest of the record number?
Are there multiple BE DBs involved?
The Check Digit is the first reason why I thought about it.
Another reason why I thought about it, I could just see a few of my tables
with as many transactions as it may through those minor few tables, it will
more than likely use up all of the positive numbers in a Long Integer Data
Type relatively quickly.
One thing that most developers would say to resolve both of these issues is
to use the Replica ID Data Type instead of the Long Integer Data Type to
resolve both of these numbers, but then that itself doesn't lend to itself
how many records were ever created, although I realize this may be just a
minor thing that wouldn't really have any real value when put to the mighty
dollar test.
Assuming you have multiple BE DBs, and you wanted to go ahead and go with
using something other than long integer data type, you can have a table
setup as follows:
fldID:AutoNumber(Long)
fldDBF:Text
fldTBL:Text
fldTID:Text
fldCKD:Yes/No
fldBEG:Text
fldEND:Text
fldINC:Long
Note: The field "fldTID" will contain the last used ID Number of the said
database and table, but it will not contain the beginning or ending text nor
will it contain the check digit.
Now for the code in general
Dim I as Long, NID as String, TID as String, PID as Long, LID as Long, X as
Long, RID as Long
DIM CD as String
TID = RST.fldTID.Value
LID = VBA.Strings.Len(TID)
If Int(LID/9) < LID/9 Then
X = Int(LID/9) + 1
Else
X = LID/9
End If
RID = RST.fldINC.Value
For I = 1 to X Step 1
PID = CLng(Right(TID,9))
LID = LID - VBA.Strings.Len(PID)
TID = Left(TID, LID)
PID = PID + RID
If PID > 999999999 Then
NID = CStr(PID mod 1000000000) & NID
RID = Int(PID/1000000000)
Else
NID = CStr(PID) & NID
I = X
End If
Next I
RST.fldTID.Value = NID
'Assuming you have a function by the name of "CheckDigit" is in place to
calculate the check
'digit based on only the right 9 digits of the new ID number.
If RST.fldCKD Then
CD = Right(CStr(CheckDigit(CLng(Right(NID,9)))),1)
Else
CD = ""
End If
NID = RST.fldBEG.Value & NID & CD & RST.fldEND.Value
NOTE: I didn't define the RST, which is the recordset, as that depends on
how you create your recordset as there's 4 general ways of creating a
recordset (Form bound to table, so cloning the recordset, use JetDirect Code
directly to create the recordset, DAO, and ADO).
Hope this is of help.
