Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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
  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 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 Patients 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 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.
  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 current date in yyyymmdd format and the sequence number 001 as suffix.
  16. Type few more records.  Since, we have locked the CardNo field Users cannot edit this field’s contents. Sample image is given below:
  1. 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.
  2. Open the Patients Table directly in Datasheet View.
  3. 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. 
  4. For example: if the date displayed is 20120109001 then change it to 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 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.

  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 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:

  1. Click on the CardNo field to select it.
  2. 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.

Technorati Tags: ,,
Share:

11 comments:

  1. 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....

    ReplyDelete
  2. Thank you for the compliments Anjana. I am glad that you found what you were looking for here.

    Regards,

    ReplyDelete
  3. 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.

    ReplyDelete
  4. So 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.

    Regards,

    ReplyDelete
  5. 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.

    Example:

    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

    ReplyDelete
  6. Rename the earlier Autonum() function name into something else, like AutonumX().

    Copy 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

    ReplyDelete
  7. 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.

    ReplyDelete
  8. a.p.r.pillai,

    Its 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

    ReplyDelete
  9. Thank 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;

    0012016, 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

    ReplyDelete
  10. 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.

    Regards.
    a.p.r. pillai

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports Animations msaccess animation msaccess forms Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards ms-access functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security DOS Commands Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Array Auto-Number Command Button Custom Functions Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess functions msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Copy Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Rich Text Sequence SetFocus Summary Tab-Page Time Difference Union Query User Users Variables Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

DIR Getting File Names From Folder

We all know Dir() Function from the time of Windows DOS Operating System.  This is the first Command introduced to those who sit on a Perso...

Labels

Blog Archive

Recent Posts