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 25th 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

Advertisements

Displaying data from related tables on one form

This is a question I often get asked on Q&A sites. But before I get into the methods, you need to understand one of the principles of relational databases. That principle is that data should exist in one place only. Having the same data in multiple tables is a violation of normalization. Related records are indicated by a Foreign Key within the record that holds the Primary Key value of the parent record. So when you want to have data from multiple tables on a data entry form, you set it up to display the data not store it in the form’s Recordsource
There are basically four ways to display related data on a form; Subforms, The Column property, DLookups and Listboxes. Ill discuss each in turn and suggest where to use each.
1) Subforms. You can use a subform to display several fields from the related table. Create the form using the Subform wizard or create a separate form and place it on the main form as a subform (I generally create a separate form). Using the wizard, you go through the following steps.
• Select whether to use an existing form or create a new one
• If you are creating select the table and fields to use
• Select the linking fields, usually accept the defaults Access proposes
You can customize the subform, so it looks like part of the main form, by removing record selectors, navigation buttons, borders etc. I use subforms when I want to display 4 or more fields from the related record. Another advantage of using subforms is where you have a One to Many relation. Using a Continuous Form or Datasheet view, you can display multiple related records at once.
2) Column Property. Generally Foreign Keys are entered by selecting the related value from a combobox. The combobox uses a query as it Rowsource. This query displays the records from that parent table. At the least, the query includes the primary key field as its bound column and a description field. However, you can add as many other fields from the table as you want. These fields can then be reference using the Column property. Click the Build button […] next to the Rowsource property to enter Query Design Mode. In Query Design Mode you can add tables and fields to the query. You can control what fields actually display in the pull down list by setting their Column Width. Setting the width of an individual column to 0″ will hide that column (Note: Column widths are entered separated by a ; for each column listed in the column Count). The combobox will only display the first non zero length column after selection. The following properties of a combo are key to using combos in this way: RowSource (the list), Bound column (what’s actual stored), Column Count (how many columns in the list, Column Widths (the size of each column in the list).
You can then set the ControlSource for an unbound control to:
=[comboxname].Column(x)
Where comboxname is the name of the control and x is the number of the column in the query for that field. Note: the column count starts with 0 so the 3rd column is 2.
Since the combobox selects a single record, the Column property will also reflect a single record. I use this method if I need to display 3 or less values from the related record.
3) DLookups. DLookups allow you pull a value from a field in a specific record. It uses the syntax: DLookup([fieldname],table/queryname,Criteria). The Criteria is used to specify the record you want to return. Since the Comboxname will store the FK value you would use a criteria like:
“[keyfield] = “ & [Comboboxname].
This would also be used as the controlsource of an unbound control. Each DLookup should only be returning data from a single record. If its possible that the DLookup might not find a matching record you should use it within a NZ (NullZero) function to prevent errors. I use DLookups when I need to pull data from different tables based on a key value.
4) Listboxes. A Listbox can have multiple columns with column headers. It also can be set to display multiple matching records. I will, sometimes, use a Listbox in place of a continuous form or datasheet subform. Listboxes will also display multiple matching records.
There are two exceptions to the rule of not repeating data in multiple tables. The first is the PK value. Obviously, that value has to be repeated as the FK to relate the records to each other. The other exception is time sensitive data. Sometimes you need to freeze data that will change over time. The best example of this is price data. For example: In an order entry application, you want to freeze the price at the time of the order. In such a case, you would have the Price field repeated in the OrderDetails table. Generally you would use the Column property for this and populate the control in the After Update event of the Products combo use code like:
Me.txtPrice = Me.cboProduct.Column(2)
These guidelines should help you build forms that preserve normalization and are well organized and easy for the user to use.
Scott<>

© 2006 Scott B. Diamond — Diamond Computing Associates