AutoNumber with Date and Sequence Number

Introduction

We always make use of the Autonumber feature in tables to create a unique ID field for the table. It is easy to define and always starts the auto-number with 1 and increments by 1 for each record unless you set the New Values property to Random rather than the default value Increment.

What is the solution if we need different sequence numbers for the records that we create each day?

For example, assume we are working on a Hospital Project and patients getting registered in the hospital on a particular day should have a unique Registration Card Number consisting of the current date and a three-digit sequence number in the format: yyyymmdd000. The sequence number must reset to 001 when the date changes.

If the Hospital maintains history records of the patients in physical files, organized by Date, Month, and Year-wise order, it is easy for them to locate any file with the Registration Card Number.

We can generate this number automatically with a Function.  Let us try it with a sample Table and Form.

The Autonum() Function

Before that copy and paste the following Function Code into a Standard VBA Module and save it:

Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(Now(), "yyyymmdd") * 1000 + 1
   Autonum = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 8)
'get today's date
dt2 = Format(Now(), "yyyymmdd")
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   Autonum = Format(Val(dt1) * 1000 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   Autonum = Format(Val(dt2) * 1000 + 1)
End If

End Function

The Sample Table and Form

1. Create a sample table with the following structure, as shown in the image given below:

   

2. The Cardno Field is a text type with 11 characters in length.  Both second and third fields are also text fields with sizes of 10 and 50 characters respectively.

3. Save the Table with the name Patients.

4. Use the Form Wizard to design a Datasheet Form for the Patient's Table and name the Form as frm_Patients.

5. Open the Form in Design View.

6. Click on the CardNo Field to select it.

7. Display the Property Sheet (F4). If you are using Access2007 then you can select CardNo from the Selection Type drop-down list.

8. Select the Data Tab and set the following Property Values as shown below:

   • Enabled = Yes

   • Locked = Yes

9. Access2007 users select Form from the Selection Type drop-down control. Earlier version users click on the top left corner of the Form (in the intersection where a black rectangle is shown) to ensure that the Property Sheet belongs to the Form and not of any other control on the Form.

10. Select the Event Tab on the Property Sheet.

11. Select the Before Insert event property and select Event Procedure from the drop-down list.

12. Click on the Build (. . .) Button to open the VBA Module of the Form.

13. Copy the middle line of the following procedure and paste it in the middle of the empty Form_BeforeInsert() lines of code in the Form module.

    Private Sub Form_BeforeInsert(Cancel As Integer)
      Me![CARDNO] = Autonum("CardNo", "Patients")
    End Sub

14.  Save the Form frm_Patients with the changes made.

15. Open the form in normal view and type Mr., in the Title Field, and type some name in the Patient Name field.  You can see that the first field is filled with the current date in yyyymmdd format and the sequence number 001 as the suffix.

16. Type a few more records.  Since we have locked the CardNo field Users cannot edit this field’s content. A sample image is given below:

Test Run of the Code

1. Now, we will test whether the sequence number resets to 001 or not when the date changes. To do that, close the frm_Patients Form.
2. Open the Patients' Table directly in Datasheet View.
3. Change the 7th and 8th digits from left (the dd digits of the date) to the previous date in all the records that you have entered so far. 
4. For example: if the date displayed is 20120109001 then change it to the previous day like 20120108001.
5. When you have completed changing all the records close the Table.
6. Open the Form frm_Patients in normal view and try adding a few more records on the Form. 

Tip:  If you prefer to test it on different dates in the next few days you may do so rather than changing the dates and trying it now itself.

You can see that the Sequence number at the end of the CardNo resets to 001 with the current date and subsequent records’ last three digits get incremented automatically. 

The user cannot change the CardNo manually because we have set the Locked Property Value of the field to Yes.  Since the Enabled Property Value is also set to Yes the User can select this field and search for a specific CardNo, if needed.

Displaying the Number Segment Separately

If you would like to display the sequence number part separate from the date with a dash, (like 20120109-005) we can do that by changing the Input Mask Property of the field, without affecting how it is recorded on the table.

1. Open the frm_Patients in Design View.
2. Click on the CardNo field to select it.
3. Display the Property Sheet (F4) of the Field.
4. Type 99999999-999;;_ in the Input Mask property. 

   Tip: When you set the input mask this way the dash character between the date and sequence number is never stored in the table, it is used for display purposes only.  But, if you enter a 0 between the two semi-colons like 99999999-999;0;_ then the dash character also will be stored in the CardNo field on the table.  It is better if we don’t do that.

5. Save the Form and open it in a normal view.  Now you can distinguish the date and sequence number very easily.

Finding Patient Record.

Assume that a Patient approaches the registration desk with her Registration Card, the staff member at the desk should search for the patient’s record with the CardNo to retrieve her historical record, to know the location of her personal file, to know which doctor the patient attended last, etc.  You have two search options when the search control is displayed. 

Try the following:

1. Click on the CardNo field to select it.
2. Press Ctrl+F to display the search control (the search control image is given below).

As shown on the image above, you can search for the CardNo without the dash if you remove the checkmark from the search options Search Field as Formatted.  Put the check-mark on when searched with the dash separating the date and sequence number.

Download Demo Database.

DateAndSeq.zip

Technorati Tags: Autonumber, Forms, Property

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.