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
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 of 10 and 50 characters respectively.
Save the Table with the name Patients.
Use the Form Wizard to design a Datasheet Form for the Patient's 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 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.
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 the current date in yyyymmdd format and the sequence number 001 as the suffix.
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
- 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.
- Open the Patients' Table directly in Datasheet View.
- 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.
- For example: if the date displayed is 20120109001 then change it to the 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 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.
- 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 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:
- Click on the CardNo field to select it.
- 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.
You are great Sir. I was searching for this type of code only for long time but I couldn't find it anywhere except here.This is an unique code really because daily appointments for Out-Patients in a clinic starts as Number One. I am excited on seeing this code and it works like a charm.Thank you Sir...Thanks a lot....
ReplyDeleteThank you for the compliments Anjana. I am glad that you found what you were looking for here.
ReplyDeleteRegards,
I too must add a VERY BIG THANKS for your post regarding AutoNumber with Date and Sequence Number. I am a self taught Access individuals (dangerous-lol) with no formal training. I have been searching many access forums for the past several weeks with extended midnights and early morning hours trying to find just a posting as you have so excellent put together. I have created a database that need exactly the AutoNumber generator you put together but with a twist that I will share in a moment. There was a lot of Serial Number / sequential numbers found but none generated (as they claimed) a daily number that really and truely restarted at 1 for the new date/day. THANKS YOU AGIN FOR YOUR PPOSTING!!!!!!! Now I have a little problem that I need your help? The database I have put together supports a program that uses a Julian Date and the serial number to generate as example 7235-4300 (Julian Date-consist of 7 is the year and 235 is the 235th day of the year) and 4300 is the sequence number that of could is the main part of the Julian Date format and restart with 1 for every date and record. Your posting provide a yyyymmddxxx format and I am not sure how to set it up for the Julian Date format as I do not want to screw up the EXCELLENT working format that I can adaot to in my program. However, I will be very grateful for your assitance to format the Date and serialno format that creates a Julian Date format for my program. I apologize for the long posting but when you find happiness it needs to be shared. Looking forward to your response and a truly thanks.
ReplyDeleteSo far I have not worked with Julian Dates and no idea as how Julian Date values are handled internally by the machine. This is something that I would like explore and find out. Probably, Gregorian to Julian conversion may be possible. Thnaks for giving me something new to work on.
ReplyDeleteRegards,
Thanks for your reply. My research did provide comments to your reference to Gregorian and I have no ideal what it means. See below to what I need your assistance. The format in my database asks for a Julian Date and Serial Number. Let me try this as an example: I have a database program that I want to display the Date (Transaction Date) and a Serial Number (SerialNo) as 2275-4300. The first number; 2 is the Year, the second numbers are the Gregorian Date (?) format 275 days of the year thus when placed together is 2275. The 4300 is the SerialNo (my database name) is a four (xxxx) position sequential numbering system that increase by 1 for each new record for the date/day (4301, 4302, 4303, etc). Now this is where the bang comes in at.....the sequential number (4300 - my sequential number) needs to restart at 4300 for the next new date/day resulting in 2274-4300. I need the Julian Date format on Form_BeforeInsert(Cancel Integer) like your original posting. See below example.
ReplyDeleteExample:
Transaction Date Sequential Number Julian Date
09/27/2012 + 4300 = 2271-4300
09/27/2012 + 4301 = 2271-4301
09/27/2012 + 4302 = 2271-4302
09/27/2012 + 4303 = 2271-4303
09/27/2012 + 4304 = 2271-4304
09/28/2012 + 4300 = 2272-4300
09/28/2012 + 4301 = 2272-4301
09/29/2012 + 4300 = 2273-4300
Hope above clarify how the Julian Date format. I will deploy the program on October 12, 2012 and would appreciate the Julian Date format. If not I will deploy and update the program with your current posting and upgrade to the Julian Date format at a later date. Thanks again and I really, really, really appreciate your help!!!
Respectful
Rename the earlier Autonum() function name into something else, like AutonumX().
ReplyDeleteCopy and Paste the following new Functions into the Standard Module of your Database:
Public Function DaysAsOnMonth(ByVal dt As Date) As Integer
Dim i As Integer, j As Integer, tdays As Integer, d As Integer
Dim leap as Integer
i = Month(dt)
d = DatePart("d", dt)
For j = 1 To i - 1
tdays = tdays + Choose(j, 31, 28 + IIf(Year(dt) / 4 = Int(Year(dt) / 4), 1, 0), 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
Next
leap = Year(dt) Mod 400
If (leap = 100 Or leap = 200 Or leap = 300) And Month(dt) > 2 Then
tdays = tdays - 1
End If
tdays = Val(Right(Year(dt), 1)) * 1000 + tdays
tdays = tdays + d
DaysAsOnMonth = tdays
End Function
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(DaysAsOnMonth(Date) * 10000# + 4300 + 1)
Autonum = dv
Exit Function
End If
'format the number as an 11 digit number
dv = Format(dmval, "00000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 4))
'take the date value separately
dt1 = Left(dv, 4)
'get today's date
dt2 = Format(DaysAsOnMonth(Date))
'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) * 10000# + Seq)
Exit Function
Else 'the dates are different
'take today's date and start the sequence with 1
Autonum = Format(Val(dt2) * 10000# + 4300 + 1)
End If
End Function
The sequence number will be generated as you need now, with Julian Date as prefix.
To know few things about Roman, Julian and Gregorian Calendars refer the Article: Days in Month Function
THANK YOU, THANK YOU, THANK YOU....and thank you!!!!!! I am currently using your original posting and ALL IS WORKING VERY WELL. I was able to modify the date format to a Julian Date view I found on the web. My database functionality is working on all points and I am VERY HAPPY and thanks again for bindng all the pieces together with your original posting and no doubt the revised format. I am currently doing a TEST all things with the databases for break points. I will post the NEW Julian Date format real soon.
ReplyDeletea.p.r.pillai,
ReplyDeleteIts me again....Bowleg and I need your assistance. I have copied and modified a SERIAL NUMBER Table that works very well with a NEW transaction/record is created. However, I need to track serial numbers for the specific quantities and fields from my INVENTORY Table that I create a Received (or shipped) transaction in my INVENTORY Table to be a REQUIRED quantities of serial numbers for the serial number table. Example, of the many fields in the INVENTORY Table, when I have serial numbers (which is often), I want to populate the same quantities of serial numbers into the SERIAL NUMBER Table with selected field from the INVENTORY Table when I create the NEW TRANSACTION/RECORD. The fields I want from the INVENTORY Table are; TRANSACTION NO, NSN/MCN, DOC NUMBER and TRANSACTION DATE when I create the receive quantities. I only need the the fields to populate as a NEW TRANSACTION/RECORD one time as the serial numbers I will click a DUPLICATE Button to create the required quantities of serial numbers.
EXAMPLE:
Inventory Table = NEW TRANSACTION for a quantity of 10 that has 10 each serial numbers:
(1) I click on a Command Button that open a form bsaed on the SERIAL NUMBER Table.
(2) I create the NEW TRANSACTION in a Subform of the INVENTORY Table with quantities I am receiving.
(3) I then click on a Command Button that open the SERIAL NUMBER Table form.
(4) I now MANUALLY enter the transction information in the SERIAL NUMBER Table from the INVENTORY Subform fields.
(5) I then DUPLICATE the transaction with the fields (TRANSACTION NO, NSN/MCN, DOC NUMBER and TRANSACTION DATE) for the serial numbers previously created in the INVENTORY Table Subform.
(5) When I ship the serial numbers, I click on a SELECT field (Yes/No) and push the SERIAL NUMBERS and other fields to my SHIPPED Table.
Need your help/assistance to create a vba that will REQUIRE the NEW transaction for the quantity form the INVENTORY Table to be a REQUIRED quantity of serial numbers in the SERIAL NUMBER Table. I also want the same REQUIRE quantity ffom the SERIAL NUMBER Table for the REQUIRE quantity when I create transaction for MY SHIPPED Table.
Hope this is not to confusing.
Thanks,
Bowleg
[...] Auto-Number with Date and Seqeunce Number [...]
ReplyDeleteThank you so much sir. Just what I am looking for but bit of struggling here just to have this format, 000yyyy. I have modified your code having the sequence as follows at first three entries;
ReplyDelete0012016, 0022016, 0032016
I close the database and change system date to the following year. I open the database and start entering data. The sequence does not restart but the year changes as follows;
0032017, 0042017, 0052017
Would you have a solution to this?
Thanks.
Please help
Sorry for delay in replying. Please copy the modified code into a text file and send to me by email:aprpillai@gmail.com. Let me review the changes to make corrections, if necessary. If possible let me know in detail what exactly is your requirement.
ReplyDeleteRegards.
a.p.r. pillai
Thank you so much sir for solved thing that made me so confused.
ReplyDeleteThe code working very well and im very very very happy :))
Sorry sir, can you help me. How to generate autonumber like 000001, 000002, 000003...
ReplyDeleteThanks
Check this Link: https://www.msaccesstips.com/2010/01/auto-numbering-in-query-column.html
ReplyDeleteThe Number can be formatted like: Format(number,"000000") to display it the way you want it.
it great . i am thank full to you for this great sharing of ms code
ReplyDeleteHola gracias a su web estoy aprendiendo mucho de Access, he seguido los pasos pero me da un error, podría ayudarme por favor.
ReplyDeleteEl error que me da es...
Se a producido el error 5 en tiempo de ejecución
Argumento o llamada al procedimiento no valida
Y la linea que me marca es...
If dt1 = dt2 Then 'si ambas fechas son iguales
Download the Demo Database - DateAndSeq.zip from the following Link:
ReplyDeletehttps://drive.google.com/file/d/1zV0YlGGi97K3zMRh_Pl_N-xligZLV6z6/view?usp=sharing