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.

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.

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.

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_Update¹ 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

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 

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 Two

 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 one

 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

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.
This 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.

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.

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

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

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

…to add a hyphen… 

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

 7,003 total views,  1 views today