AutoNumber with Date and Sequence Number
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 on 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 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 Registraion Card Number.
We can generate this number automatically with a Function. Let us try it with a sample Table and Form.
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
- Create a sample Table with the following Structure, as shown in the image given below:
- The CardNo Field is a text type with 11 characters in length. Both second and third fields are also text fields with sizes 10 and 50 characters respectively.
- Save the Table with the name Patients.
- Use the Form Wizard to design a Datasheet Form for the Patients Table and name the Form as frm_Patients.
- Open the Form in Design View.
- Click on the CardNo Field to select it.
- Display the Property Sheet (F4). If you are using Access2007 then you can select CardNo from the Selection Type drop-down list.
- Select the Data Tab and set the following Property Values as shown below:
- Enabled = Yes
- Locked = Yes
- Access2007 users select Form from the Selection Type drop-down control. Earlier version users click on the top left corer 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.
- Select the Event Tab on the Property Sheet.
- Select the Before Insert event property and select Event Procedure from the drop-down list.
- Click on the Build (. . .) button to open the VBA Module of the Form.
- 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
- Save the Form frm_Patients with the changes made.
- 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 current date in yyyymmdd format and the sequence number 001 as suffix.
- Type few more records. Since, we have locked the CardNo field Users cannot edit this field’s contents. Sample image is given below:
- Now, we will test whether the sequence number resets to 001 or not when the date changes. To do that first close the frm_Patients Form.
- Open the Patients Table directly in Datasheet View.
- Change the 7th and 8th digit from left (the dd digits of the date) to previous date in all the records that you have entered so far.
- For example: if the date displayed is 20120109001 then change it to previous day like: 20120108001.
- When you have completed changing all the records close the Table.
- Open the Form frm_Patients in normal view and try adding few more records on the Form.
Tip: If you prefer to test it on different dates on 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 current date and subsequent records’ last three digits gets 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 also set to Yes the User can select this field and search for a specific CardNo, if needed.
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.
- Open the frm_Patients in Design View.
- Click on the CardNo field to select it.
- Display the Property Sheet (F4) of the Field.
- 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.
- Save the Form and open it in normal view. Now you can distinguish the date and sequence number very easily.
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 history 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:
- Click on the CardNo field to select it.
- Press Ctrl+F to display the search control (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 check mark from the search option Search Field as Formatted. Put the check-mark on when searched with the dash separating date and sequence number.