Sequential Numbering

A common question I’ve received has to do with sequential numbering. People often want to use some identifier for their records that involves information about the record and a sequential number. Sometimes these numbers are sequential throughout the table, other times the sequence is specific to other criteria.

To illustrate how to do this, I’m going to use the following scenarios;

1)      Acme Co. needs to sequentially number their Purchase Orders to conform to standard accounting rules.

2)      Apex Inc. wants to sequentially number inquiries they receive but within each year.

3)      Hollywood & Vine, a law firm, wants to tag all documents they receive with a client ID, case number and then number them sequentially by case.

There are three issues common to all three scenarios. First, the sequential number is stored separately in the table as an Integer datatype (might need Long Integer). Second, the DMax function is used to  determine the last number used. Third, the number needs to be generated immediately prior to saving  the record.

One other caveat here; the number generated in these scenarios is NOT a primary key. These tables  should use an Autonumber as the PK. The possible exception would be scenario 1, but I would still tend to use an Autonumber as the PK.

We’ll start with the simplest, scenario 1. Acme will use a straight sequential number for its Purchase Order numbers. Add a (Long?) Integer field to the table named PONum. Add a control to your Purchase Order form bound to that field. Then use the following expression to populate that control:

Me.txtPONum = Nz(DMax(“[PONum]”,”tblPO”),0)+1

The Nz function will return 0 if there are no records so this expression will return a 1 the first time and then increment the highest number each additional time. The key is where to put this line of code. As I said it needs to be generated immediately prior to saving the record so that another user does not get the same number. So generally I would put this behind a Save button or a button to specifically generate the number. I would then add a second line to commit the records. Either:

Me.Dirty = False

or

DoCmd.RunCommand acCmdSaveRecord

Next we will look at scenario 2. The variation here is that Apex wants to start each year with anew sequence of numbers. Also, they want to include the year when displaying this number. We still use a DMax, but we need to add criteria to determine the year. So Apex needs to have a field in their table that indicates the date. Generally, such inquiries will have a date/time stamp to indicate when received or the user will input the date and time. So we will assume there is a field in record called InquiryDate. We will also add an Integer field named Sequence. On the form we will add a control bound to Sequence, but set its Visible property to No. The line of code will look like this:

Me.txtSequence = Nz(DMax(“[Sequence]”, ”tblInquiry”, ”Year([InquiryDate]) = “ &
Year(Me.[txtInquiryDate])),0)+1

With this expression we check only for records that are in the same year as the Inquiry date to increment the sequence. Again, the NZ function will automatically restart the sequence with each year. Cleary, though, we cannot generate this number until the InquiryDate has been entered. Is this value is a user entry, then you may want to check that it’s filled in before generating the number. Something like:

If IsNull(Me.txtInquiryDate) Then

            MsgBox “Inquiry Date must be entered first!”, vbOKOnly

            Me.txtInquiryDate.SetFocus

Else

Me.txtSequence = Nz(DMax(“[Sequence]”, ”tblInquiry”, ”Year([InquiryDate]) = “ &
Year(Me.[txtInquiryDate])),0)+1

End If

Also again, you want to generate this number immediately before saving the record.

The final piece is to display the identifier. For that you use the following expression:

=Format([InquiryDate],”yyyy”) & “-“ & Format([Sequence],”0000”)

This expression can be used anywhere you want to display this identifier. It will appear like this:

2009-0010,
indicating the tenth inquiry of 2009. You can use that as the Controlsource of a control on a form or report. In a query you would preface with an Alias rather than the equals sign (i.e.; Inquiry Code:).

The final scenario is very similar to Scenario 2. The two main differences are in the criteria of the DMax and the expression to display the identifier. The document table already has two fields to identify a
document; ClientCode, which is a 5 character code that identifies the client and CaseNumber, which is an alpha-numeric code assigned to the case. Since the Case Number is unique to the Client, we only need to set the criteria in the DMax for the case number. We will also need to add an Integer field named
Sequence to the table. So the Dmax expression will look like this:

Me.txtSequence = Nz(DMax(“[Sequence]”, ”tblDocument”, ” [CaseNumber] = ‘“ & Me.[txtCaseNumber]
& “’”),0)+1

Notice here that we need to surround CaseNumber with Single quotes since it’s a Text datatype. As in the other scenarios, The NZ function automatically restarts the sequence when a new CaseNumber is used. In this scenario we would want to check whether the CaseNumber has been entered before generating the sequence.

For displaying this identifier, we would use an expression like:

=[ClientCode] & “/“ & [CaseNumber] & “-“ & Format([Sequence],”000”)

In this scenario we are assuming that there will be no more than 999 documents attached to a case. In Scenario 2 we assumed no more than 9999 inquires during a year. So you need to adjust the number of zeros when formatting Sequence for the anticipated number of records. Of course this can always be changed later. You also don’t need to format the sequence with leading zeros as the Format function does. As shown the expression returns something like: DCASD/CI123-025 for the 25^th document in case CI123 for client DCASD. Without leading zeros it would be: DCASD/CI123-25. The advantage to the latter is that you don’t have to anticipate the number of records you might have in the sequence, but I’ve found many users prefer a more uniform number with the leading zeros.

As you’ve seen, the identifier is built using other data besides the sequence number. Since this other data is a part of the record, you don’t want to store this identifier since that means storing the same data twice. That’s why we use an expression to display the identifier rather than store it.

To recap, you use a DMax function to return the highest number in the Sequence and increment it by 1. You assign the incremented number to a control on your form and then immediately save the record. If your identifier includes additional information from the record, you use an expression that concatenates that information with the sequence number to display the full identifier.

With these techniques, you can generate a sequential number for just about any situation. I hope you find this information helpful.

© 2009 Scott B. Diamond — Diamond Computing Associates