As you would have it, one of my clients asked me if I could produce a sequential number field in a database of 400,000 records. For example, the code should look like "A2234", a letter and four digits. However I can't use the letters "I, L OR O" and can't use numbers "1, 7 or 0".
There are supposed to be some 800,000 combinations and each one has to be unique. I thought of using 4 different tables with the needed letters and numbers and somehow using those, but I am at a loss.
Believe it or not, I had about the same demand some time ago.
I looked up the code for it and adapted to fit your needs.
You need one table called "tblList with 2 fields : "Character" and "CharNr". It must contain in the first field the characters that are allowed (=alphabet minus "I, L OR O" ) and in the second field a consecutive numbering (Autonumber will do fine)
You then need to create a module with the following code :
Public Function NextSeqNr(strMaxNr As String) As String
Dim strFirstChar As String, intNr As Integer
'split the given maximum seqnumber
strFirstChar = Left(strMaxNr, 1)
intNr = Mid(strMaxNr, 2)
intNr = intNr + 1
If intNr = 10000 Then
intNr = 0
strFirstChar = RaiseChar(strFirstChar)
Public Function RaiseChar(strChar As String) As String
Dim i As Integer
i = DLookup("CharNr", "tblList", "Character='" & strChar & "'")
RaiseChar = DLookup("Character", "tblList", "CharNr=" & i + 1)
When all is well, you can use the function :
as entry for the default value in the field "YourFieldName" (This is the field in the table 'YourTable' where you need the sequential number
Like this, the next available sequential number will be generated autoimatically for each new record you need to add. Just make sure there is at least one value entered manually to get the routine started (otherwise, the DMax function would give a NULL value!)