Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

Another post on Sequential Numbering…

The most often asked question is about customizing Autonumber.

The answer is, don’t!  If you want to use a sequential numbering system Autonumber is not the Data Type to use.  Even if you want to include Autonumber with another Field to make a composite… don’t, it’s not always sequential.  (More on that later)  Leave Autonumber alone and use it for its intended purpose, a unique Primary Key for your databases internal logic that no one, besides you, sees.  And here’s why…

1. It skips

  • This is a biggie. Once you delete a record or *spend* it (create and then undo) that number is no longer available, it’s gone for good.

MissingNumbers

2. It’s not always sequential

Sometimes Access decides to assign a negative number. Autonumber is only guaranteed to be unique not sequential even if you select Increment under New Values at the Table level.

3. While you can set the initial value (The Access Web – Have Autonumber field start from a value different from 1 or Allen Browne’s Site Set AutoNumbers to start from) you cannot otherwise modify the number. The Autonumber Data Type is not editable and there is no way around that via Access. The only exception to this rule is if you upsized your Table to SQL Server in which case you can modify the number.

4. And, it does not always indicate the order in which records are entered, see Item 2.

OrderByID

So, now what?  Instead, create a separate field for your sequential/custom number/string, herein referred to as the Sequential ID.  A few tips before we get started…

  • Using the Nz() function ensures that the first record is handled correctly, i.e. first record of the year or first record in the table or first record in the group.
  • You will want to Save your Record immediately after the Sequential ID has been assigned. This becomes especially important in multi-user databases, so that the Sequential ID does not get *snatched* away from the User and/or your Users may receive the Error 3022, shown below, if two Users try to save at the same time.

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

You can implement your own Error Handling or Roger Carlson has some simple code to trap the error, see Domain Function Example: Simulate AutoNumber with DMax()

  • Have a back-up plan. When you *commit* your Sequential ID to the Table the Record is saved. However, if the User decides they really did not want to create a new Record you’ll want a way to back out that Sequential ID. You will also want a way to handle *spent* Sequential ID’s, i.e. the Record deleted was not the last record so now there is a gap in the sequence. To find those unused numbers you can use…
SELECT T.lLiftID+1 AS Missing
FROM tblLifts AS T LEFT JOIN tblLifts AS T1 ON T1.lLiftID=T.lLiftID+1
WHERE (((T1.lLiftID) Is Null));

You will need to decide for yourself what you want to do with this *orphaned* numbers, let them go or allow your Users to use one of them when creating a new Record.

So, let’s create some Sequential ID’s…

A. Sequential ID number, pseudo Autonumber
B. Use a Command Button to assign a Sequential ID
C. Customize your Sequential ID

a. Sequential ID with Year prefixing starting fresh each New Year
b. Sequential ID with YYMMDD with an incremental number suffix
c. Sequential ID strings alpha-numeric or numeric alpha

D. Variations of Sequential ID’s

Side note:  While some of the methods here allow you to use Sequential ID as a Primary Key, however, it would not be my first choice.  That said the method described in A. is at the top of the list for preferred methods should you decide not to use the Autonumber Data Type but implement your own.  Remember, just because you can do so does not mean you should do so.  Evaluate your schema and decide what works best for you (and the Database) your Users are only going to see what you let them see.

Special note:  The sample code provide here does not provide any Error Handling which means you should implement your own.

A. Sequential ID number, pseudo Autonumber

With this example you can use the Sequential ID as a Primary Key by putting it in the Before_Insert of the Form, since the Before_Insert event fires when a User moves to a new Record you can increment and then Save locking your number in place. (Again, just because you can do thing does not mean you should do a thing… so, again, use this one as the one the User sees and maintain Autonumber with another field as the Primary Key Access uses.)

Private Sub Form_BeforeInsert(Cancel As Integer)

     If Me.NewRecord Then
        Nz(DMax("YourIDField","YourTable"), 0) + 1
        DoCmd.RunCommand acCmdSaveRecord
     End If

End Sub
Note: You can also use the Before_Update1 event of the Form, and while, the Before_Insert event fires before the Before_Update event the Before_Update event is the only event that *writes to disk* upon firing.

Private Sub Form_BeforeUpdate(Cancel As Integer)

     If Me.NewRecord Then
        Nz(DMax("YourIDField","YourTable"), 0) + 1
        DoCmd.RunCommand acCmdSaveRecord
      End If

End Sub
B. Use a Command Button to assign a Sequential Increment

When using this method you’ll want to *push* the User to a new Record and then create your Sequential ID. After all, we don’t them overwriting an existing Sequential ID. You can either do it automatically…

Private Sub cmdAddNew_Click()

     If Dirty Then Exit Sub
       DoCmd.GoToRecord , , acNewRec   'Force User to a New Record
       Me.txtCoilID = Nz(DMax("crCoilID", "tblCoilReceiving"), 0) + 1
       Me.cboCoilID = Me.txtCoilID

End Sub
…or send them a message to go to a New Record if they are not already on one…
Private Sub cmdAddNew_Click()

     If Not IsNull(Me.txtCoilID) Then
       'Tell User to go to a New Record first
       MsgBox "You MUST go to NEW record first!", vbCritical, "Add New"
     Exit Sub
     End If

     Me.txtCoilID = Nz(DMax("crCoilID", "tblCoilReceiving"), 0) + 1
     Me.cboCoilID = Me.txtCoilID

End Sub

C. Customize your Sequential ID

a. Sequential ID with Year prefixing starting fresh each New Year

GetMyID

This example will only increment up to 99999, see GetMyID!  If you plan to use this method you must be careful you will not have more than 99,999 records per year or, if you want to go higher, you can expand on the spaces (placeholders), however, you still need to take note of *how many* you allot when using this example or this might happen…

1400000
1400001
etc…
1499999

…go to add a new Receiving ID and you get…

1400000

…and an Error Message because at the end it will *start again*!  So, let’s avoid code that locks you into a maximum record count unless that is what you mean to and have an end game, like starting a new unique sequence.

Step 1

Copy/paste the below in a Module then Debug > Compile.  Be sure to change the name of the Fields and Table below to match your own.

Function GetMyID() As Long
   GetMyID = Format(Date, "yy") & Format(Nz(DMax("rSequenceID", "tblReceiving", "Year([rReceiveDate]) = " & Year(Date)), 0) + 1, "00000")
End Function
 Step 2

Create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  There are two procedures listed below, one with validation and one without.  You only need to copy/paste the one you want to use not both.  Again, be sure to change the name of the Fields and Table below to match your own.

With validation…

Private Sub cmdReceive_Click()

'Inform User they are not on a New Record
     If Not IsNull(Me.txtReceiveID) Then
        MsgBox "You MUST go to NEW record first!", vbCritical, "Receive"
        Exit Sub
     End If
     
     'Validate that all other required Controls are filled in prior to creating Sequential ID.
     If sfrValidateData Then
        Me.rSequenceID = Nz(DMax("rSequenceID", "tblReceiving", "Year([rReceiveDate]) = " & Year(Date)), 0) + 1
        DoEvents
        Me.txtReceiveID = <strong>GetMyID()
        DoCmd.RunCommand acCmdSaveRecord
        'Requery the Combo Box to exclude the newly received item from the Combo Box which shows items not yet received
        Me.cboResults.Requery
     Else
        MsgBox "Oops, no Receive ID created, please retry!"
     End If

End Sub

OR if no validation is needed…

Without validation…

Private Sub cmdReceive_Click()

'Inform User they are not on a New Record
     If Not IsNull(Me.txtReceiveID) Then
        MsgBox "You MUST go to NEW record first!", vbCritical, "Receive"
        Exit Sub
     End If
     
        Me.rSequenceID = Nz(DMax("rSequenceID", "tblReceiving", "Year([rReceiveDate]) = " & Year(Date)), 0) + 1
        DoEvents
        Me.txtReceiveID = GetMyID()
        DoCmd.RunCommand acCmdSaveRecord
      
        'Requery the Combo Box to exclude the newly received item
        Me.cboResults.Requery

 End Sub

 

b. Sequential ID with YYMMDD with an incremental number suffix SequentialYYMMDD

In this example you can easily tell how many Quotes you got on any particular day and still maintain a unique ID.  This type of Sequential ID can be useful for tracking sales or sales per Associate/Employee.  You can query the table to check how your business or Sales Staff are doing.  One caveat of doing it this way is you will to determine what part is which because you have not formatted two digits for Months and Days.  The second example does this and makes it much easier to determine when the Quote was actually created no matter what create date is entered.

Example One
Step 1

Create a query to count records each day to use in the Function, i.e.

qryCountOfQuotes

SELECT Left([qQuoteID],InStr([qQuoteID],"-")-1) AS Quotes, Count(Left([qQuoteID],InStr([qQuoteID],"-")-1)) AS CountOfQuotes FROM tblQuote GROUP BY Left([qQuoteID],InStr([qQuoteID],"-")-1);

Be sure to change the name of the Fields and Table below to match your own.

Step 2

Create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  There are two procedures listed below, one no space holders for double digit Months and Days, Example One and; one with spaces holders for Months and Days, Example Two.  The Year is double digit for both examples.  You only need to copy/paste the one you want to use not both.  Again, be sure to change the name of the Fields and Table below to match your own.

Private Sub cmdAddNew_Click()

     'Push User to a New Record
     DoCmd.GoToRecord , , acNewRec
     Me.txtQuoteID = Format(Date, "yy") & Format(Date, "m") & Format(Date, "d") & "-" & Nz(DLookup("CountOfQuotes", "qryCountOfQuotes", "Quotes = " & Format(Date, "yy") & Format(Date, "m") & Format(Date, "d")), 0) + 1
     Me.cboQuoteID = Me.txtQuoteID

 End Sub

OR

Example TwoqryCountOfQuotes

 Private Sub cmdAddNew_Click()

     Me.txtQuoteID = Format(Date, "yymmdd") & "-" & Nz(DLookup("CountOfQuotes", "qryCountOfQuotes", "Quotes = " & Format(Date, "yymmdd"), 0) + 1

End Sub

c. Sequential ID strings alpha-numeric or numeric alpha

Here are two examples using incremental letters; the first will be using an YYDDMM format implementing the letter at the end. The second example I’m using Property Cards which, in this case, only have a letter if there is more than one card. The Property already has a Parcel Number, so no incrementing of the Sequential ID needed.

Example one140725A
 Step 1

Create a query which will be used to create the Sequential ID

Me.txtQuoteID = Format(Date,"yymmdd") & Chr(Nz(DMax("Asc(Right(qQuoteID,1)),","tblQuote","Left(qQuoteID,6) = """ & Format(Date,"yymmdd") & """"),64)+1)

 

Step 2

Create your Command Button and the copy/paste the above line between Private Sub… and End Sub in the Event Procedure of your Command Button.  You only need to copy/paste the one you want to use.  Again, be sure to change the name of the Fields and Table below to match your own.

OR

Example two

123456789B

Step 1

Create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  You only need to copy/paste the one you want to use.  Again, be sure to change the name of the Fields and Table below to match your own.

Because this is a Property Card and 90% of the information will be duplicated, I am taking what I need from the first card and putting it on the next card.  If this is the first time it’s happening, the only card, I first need to add the *A* and then create the new card.

Public Sub cmdDuplicate_Click()

     Dim intRecCount As Integer
     Dim db As DAO.Database
     
     Set db = DBEngine(0)(0)
     'We need to know how many Property Cards there are to determine if we are starting a *A* or some other letter
     intRecCount = DCount("ppParcelNumber", "tblPropertyProfile", "[ppParcelNumber] = '" & Me![txtParcelNumber] & "'") - 1

     If Me.Dirty Then
        Me.Dirty = False
     End If

     If Me.NewRecord Then
        MsgBox "Select an Property Card to duplicate!", vbInformation, "Duplicate"
     Else
        'If there is only one Property Card assign it an *A*
        If intRecCount = 0 Then
           Me.cboFormID = "A"
        End If
     
     'Duplicate the main record
     With Me.RecordsetClone
        .AddNew
        !ppOwner = Me.txtOwner
        !ppAddress = Me.txtAddress
        !ppLegal = Me.txtLegal
        !ppDTEID = Me.cboDTEID
        !ppPropertyClassificationID = Me.cboPropertyClassificationID
      'Start the lettering from *B* on.
        !ppFormID = Chr(Asc("B") + intRecCount)
        !ppParcelNumber = Me.txtParcelNumber
        !ppCountyFIPSID = Me.cboCountyFIPSID
        .Update
        .Bookmark = .LastModified
      Me.Bookmark = .LastModified
      Me.cboParcelNumber = Me.txtPropertyID
      Me.cboParcelNumber.Requery
     End With
End If
Set db = Nothing

End Sub

If you just want to increment letters you can use the below.  Note, when using the alphabet you can only go up to *Z* unless you use a custom VBA procedure.  See the link below for one such custom procedure.

To Increment Letters…

    If IsNull(Me.txtRevisionID) Then
       Me.txtRevisionID = "A"
    Else
       Me.txtRevisionID = Chr(Asc(Me.txtRevisionID) + 1)
    End If

To increment beyond the single letter click here.

D. Variations

I.
T
his method grabs the first three letters of the Company Name. The Company name used in this example is Access Diva. No worries though, if you have two Companies that have the same first three letters it will increment the numeric side to keep the ID unique. This only becomes a problem if you have more than 9,999 Companies that have the identical first three letters.

ACC0001

Dim lngCountIDs As Long
     'Find how many Companies start with the same three letters and count plus one
     lngCountIDs = Nz(DCount("Right(cCompanyID,3),", "tblCompanies", "Left(cCompanyID,3) = '" & Left(Me.txtCompanyName, 3) & "'"), 0) + 1

     Me.txtCompanyID = StrConv(Trim(Left(Me.txtCompanyName, 3)), vbUpperCase) & Format(lngCountIDs, "0000")
     DoCmd.RunCommand acCmdSaveRecord

You can change how many letters you want use as the Prefix by changing all the 3’s to how ever many letters you want as the Prefix.

II.
Always want it to start with specific letter(s)…  This method starts every Sequential ID with the same letters.  Ultimately, you could have a table that determines the Prefix if you want to *tag* which Department, Section or Index Key you assigned the number to.

KB

Me.txtFFESpecification = "KB" & Nz(DMax("ffeSpecificationID", "tblFFESpecifications"), 0) + 1

…using a Department, Section or Index Key as a Prefix…

BR

Me.txtFFESpecificationID = Me.cboIndexKeyID.Column(1) & Nz(DMax("ffeSpecificationID", "tblFFESpecifications"), 0) + 1

…to add a hyphen… BRwithHyphen

Me.txtFFESpecificationID = Me.cboIndexKeyID.Column(1) & "-" & Nz(DMax("ffeSpecificationID", "tblFFESpecifications"), 0) + 1

To use any of the above create your Command Button and the copy/paste the lines between Private Sub… and End Sub in the Event Procedure of your Command Button.  You only need to copy/paste the one you want to use.  Again, be sure to change the name of the Fields and Table below to match your own.

III.
Only want to go to a specific number…

Private Sub Form_BeforeInsert(Cancel as Integer)

     Dim iNext As Integer iNext = Nz(DMax("[ID]", "[tablename]")) + 1 If iNext >= 10000 Then
        Cancel = True
        MsgBox "All ID's have been used, shut off the PC and go home.", vbOKOnly
     Else Me![ID] = iNext
     End If

End Sub

IV.
Create a Function for use with more than one table…

Function lngSequentialID(strID As String, strTable As String) As Long
     lngSequentialID = Nz(DMax(strID, strTable), 0) + 1
End Function

To use copy/paste the above lines into a Module.  To use…

lngSequentialID("YourFieldName", "YourTableName")

 

So, when do those Form Event fire?

Creating a new record
When you move the focus to a new (blank) record on a form and then create a new record by typing in a control, the following sequence of events occurs:

Current (form) → Enter (control) → GotFocus (control) → BeforeInsert (form) → AfterInsert (form)

The BeforeUpdate and AfterUpdate events for the controls on the form and for the new record occur after the BeforeInsert event and before the AfterInsert event.

From: Order of Events for Database Objects

 

More sites about Sequential Numbering…
Auto Increment a Value http://www.devhut.net/2010/10/30/ms-access-auto-increment-a-value/
Sequential Numbering http://scottgem.wordpress.com/2009/11/25/sequential-numbering/
Tech on the Net http://www.techonthenet.com/access/modules/sequential_nbr4.php
FMS http://www.fmsinc.com/microsoftaccess/autonumber%20field/creating.asp

685 total views, 2 views today

2 comments to Another post on Sequential Numbering…

  • Doug,

    I thought *somebody* would come along and point that out, so… THANKS!

    That said, while I do agree somewhat with your statement, I have found that this way, still using an Autonumber field as the Primary Key, gives an alternate approach for those that *really* want to use a *smart* key and maintain database practiced standards.

  • Great post, Gina, but I feel obligated to point out that purists feel that fields like your customized Sequential IDs (Sequential ID with Year prefixing starting fresh each New Year, Sequential ID with YYMMDD with an incremental number suffix, and Sequential ID strings alpha-numeric or numeric alpha) represent a violation of First Normal Form. (see, for example, Elmasri, Ramez and Navathe, Shamkant B. (July 2003). Fundamentals of Database Systems, Fourth Edition. Pearson. p. 315. ISBN 0321204484: “It states that the domain of an attribute must include only atomic (simple, indivisible) values and that the value of any attribute in a tuple must be a single value from the domain of that attribute.”)

    An alternative would be to store the year, date or initials in a separate field from the sequential number, and add a computed field in a query that concatenates them as desired. You’d then use the query anywhere you would otherwise have used the table.