Customised number increment in a table

daavis · May 18, 2011

Hi,
I am working on a db, where, in a particular table, an auto incrementing customised number is created in addition to autonumber (as it always starts from 1).
I would like to have my number starting with an Alphabet followed by a set of 4digit numbers that would increment in each new record.
Once it reaches the max of four digits ie. 9999, it increments the Alphabet and starts numeration from the beginning.
ex.
A0001 > A0002......>A9999 >B0001

Can this be achieved in a table?
if so, where the code should be written and what should be the format?

I tried working with DMax function explained in another thread in this section and tried placing it in "Default Value" section of the Field properties for the particular field.

ex. Field id [Issue data.is_issue no:]
Field properties > Default value: =DMax ("is_issue no:","Issue data") +1
When tried to save, it generated "Unknown function DMax in validation expression or default value on Issue data.is_issue no:"

I know I have made a mess of the available functions. I would appreciate if anyone could render their help in this regard.

Thanks in advance.
Regards
Daavis

JANR · May 18, 2011

Can this be achieved in a table?

If you use Access 2010 then yes, since that version supports DataMacros. That said why would you use such a limited setup? The number of unique values you can use is under 260000!!!

If this hasen't put you off then in order for this to work you can create a lookup table with 2 fields, one to hold the current alpha char and one to hold the current seed number. Then create a custom function in a Standard Module that you can call to find the next "stringnumber".

This function opens the table and increment the number, but you have to test the alpha to determent if you need to increment to the next letter in the alphabet.

ex:

Code:

Public Function fNewID() As String
    Dim IDs As String
    Dim IDi As Variant
    Dim rs As DAO.Recordset
    Dim x As Integer
    Dim y As String
    
    ' Open recordset to find current ID, IDStr = Alfa Character, IDInt = Number
    Set rs = CurrentDb.OpenRecordset("SELECT IDStr, IDInt FROM tblAuto")
    IDi = rs.Fields("IDInt") + 1
    IDs = rs.Fields("IDStr")
    x = Len(rs.Fields("IDInt"))
    y = Left("000", -x + 4)
    
    ' Validate NewRecordID
    If (IDs = "Z") And (IDi > 9999) Then
        MsgBox "No more RecordID's in table!!", vbCritical, "End of the rope!!"
        GoTo ExitPoint
        
        ElseIf (IDs <> "Z") And (IDi > 9999) Then
            IDs = Chr(Asc(IDs) + 1)
            IDi = 1
            fNewID = UCase(IDs) & "000" & IDi
    Else
        fNewID = UCase(IDs) & y & IDi
    End If
    
    'Update tblAuto with new RecordID
    With rs
        .Edit
        !IDStr = UCase(IDs)
        !IDInt = IDi
        .Update
        .Close
    End With

ExitPoint:
Set rs = Nothing
On Error GoTo 0
Exit Function

End Function

IMHO this is more of an academic exercise than something you really should use in a real database due to the limitation AND the fact that your "number" will be a string type and that will make life hard when you want to query your data.

JR

daavis · May 19, 2011

Thanks for your reply JANR.
Well 2.6L will not be the put off information, rather it will be the version, I use Access 2003 (primitive by your standard perhaps, but cant help)

Now the big question is how to do this in 2003?
From your explanation, I got an idea to concatenate values from two fields. One can hold Alphabets and the other numerals.
The reason for sticking with 4 digit numerals is a decision by choice as I find it confusing to read any numerals more than 4 digits (except when it relates with money

). The limitation of 2.6L will not be of much deterrent.
One simple query! The code that you wrote, where is it supposed to be placed? Is it a macro or can it be inserted into the expression builder in field properties?

I use Access2003!!!
Since you mentioned about problems while raising queries later on, if I carry all these exercise, I am also a bit confused. Will it affect in any way if calculation is not involved with this String (number) when raising a query?

I do not have much understanding of the macros and stuff like that. Hence it was difficult for me to interpret the various stages of the code.

Will it work if I just cut and paste it in my db? If no, then will you please help me?

My database name is Chemicalsdb
Table name: Issue data
Field Name: Issue Number

Thanks once again for all the effort taken.
Regards
Daavis

Galaxiom · May 19, 2011

If I was forced to do this I would adopt a completely different strategy.

The alphanumeric code could be considered as a derived value coming from an underlying numeric primary key code. It would not be stored anywhere but calculated in the RecordSource of control on forms and reports.

The alphanumeric code is derived from the key by:

Code:

 Chr(([NumericKey]\10000) + 65) & Format(([NumericKey] MOD 10000), "0000")

The backslash is the Integer Divide and returns the integer part of the division. MOD is the Modulo operator that returns the remainder of the division.

65 is the ASCII code for capital A and the capitals through to Z follow in sequence. Consequently A will be derived for values up to 9999, B for 10000 to 19999 and so on.

This does not quite match the specification because it includes B0000 etc but you can see the principle. I used that for simplicity. Skipping the 0000 values would use 9999 as the divisor and add one to the modulo.

Code:

 Chr(([NumericKey]\9999) + 65) & Format((([NumericKey] MOD 9999)) + 1, "0000")

Since the alphanumeric code is a user-visible value the use of autonumber on the key should be avoided. Use an incrementing system for the key value. (DMax or a stored NextKey system).

It would probably still fail at key value 260001 because this would be represented by:
"[0000"

JANR · May 19, 2011

The code that you wrote, where is it supposed to be placed?

In a Standard module so that it is exposed to your application.

To mimic autonumber beavior you can call the function in the default property of your formfield:

=fNewID()

Obviously you either lock or hide the control on the form so you don't interfere with it. See attached db open frm1 and start adding records to tblCust.

tblAuto holds the current Seed for alpha and number starting with A and 0.

In a single use enviroment it should work, but in a multiuser enviroment locking and deny read/write to tblAuto during the prosess would be recomended.

Edit: Using the Default property will like with autonumbers give gaps in the sequence if you disregard the record so if sequence is important you can get the "number" at the last possible moment prior to record gets saved to table, like in the Form_BeforeUpdate event.

JR

Customised number increment in a table

daavis

New member

JANR

Registered User.

daavis

New member

Galaxiom

Super Moderator

JANR

Registered User.

Attachments

Similar threads

Users who are viewing this thread