How to set custom ID field?

Question

I have autonumber field in MS Access.

Here, it's starting with 1, 2, 3,…

But I want to enumerate numbers starts, with

2017ICLAA001, 2017ICLAA002, 2017ICLAA003,…

How to do that?

Answer 1

Should be able to figure out what you want with this since "2017...." is not always going to be the same and if you change it then it will jack up your database.

Example of Year-Number output: 17-0001

When the year changes the number auto resets to 1, because it checks the date then the number value which is incremental from 1 to 9,999 each year. You can delete records and it won't affect numbering since it always checks for the largest integer based on the current year, which is defined by your computers time/clock.

Must have the following columns: [AutonumberID] [DateCreated] [ColumnForYear-Number]

You should set the "[DateCreated]" column's "Default Value" in the Table's DesignView to "=Date()" (without quotes) so a date is added automatically when creating a record.

Add the following to your form's [Event Procedure] BEFOREINSERT otherwise if you update content in the record later (BeforeUpdate) it WILL change the record number everytime a change is made. You've been warned!

Do not use the date of a "[Last Modified]" type of column otherwise you will regret it in the future if you change/update anything in the record when the year changes and edits are made (think about it). Ensure you have a dedicated "[DateCreated]" column that doesn't change after inserting/adding the record no matter what year you decide to make any changes.

Here is the code:

Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim vLast As Variant

Dim iNext As Integer

vLast = DMax("[ColumnForYear-Number]", "[Table]", "[ColumnForYear-Number] LIKE '" & _
Format([txtDateCreated], "yy\*\'"))

If IsNull(vLast) Then

iNext = 1

Else

iNext = Val(Right(vLast, 4)) + 1

End If

Me![ColumnForYear-Number] = Format([txtDateCreated], "yy") & "-" & Format(iNext, "0000")

End Sub

To get more than 9,999 records in one year change the number 4 in Val(Right(vLast, 4)) to a larger integer, then change the zeros in Format(iNext, "0000") to reflect the number of placeholders. The number 4 and there are four zeros. The same thing applies to the year, just change anywhere there is "yy" to "yyyy" for a four digit year. When making changes ensure the data type for the table's field/column can accept the total characters to be calculated or it will chop off any excess characters. Default is usually 255 characters for text however if your's says 8 characters are allowed for the [ColumnForYear-Number] and you are trying to add 9 or more then you will get frustrated troubleshooting a simple problem. Just FYI.

"[txtDateCreated]" is where the actual date entry exists and not the same as "[DateCreated]" which is the column name, unless you named your label that under the "Other" tab in Property Sheet. In other words columns are [columnname] and the textbox area where values are added/changed/viewed in FORMS should be labeled [txtcolumnname] (minus the brackets of course).

Additional options that are already configured into the format you request are listed in the next response (see below).

Answer 2

Since I had some more time on my hands I decided to answer your question more directly with a couple of options. My assumptions are: (1) You want the year 2017 to change automatically and (2) a prefix you define ICLAA followed by (3) an incremental number 001 that resets with each new year and (4) this is for a form with entry boxes (hence [txt...]).

Table Columns Required:
[AutoNumber] <=Not used here, it's just to show it still exists

[Column4UniqueValue] set the data type to Short Text and ensure your columns field size is set to 12 or more otherwise it will not work and will kick an error.

[DateCreated] set to Date/Time with format as General Date default value set =Date(), set Show Date Picker to Never for good measure, and set Locked value to Yes so user cannot change\override the value in the form. Note: this column [DateCreated] is not required if you decide to go with option two (2) listed below.

After you created the columns above in your table go to your form and add the new fields onto the form, click inside the newly added text field box and set its Other name as txt.... , then go into VBA Code Builder [Alt+F11] and add the code from either option one or option two.

Option One (with DateCreated field):

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim Prefix As String
    Dim vLast As Variant
    Dim iNext As Integer

    Prefix = "ICLAA"

    vLast = DMax("[Column4UniqueValue]", "[tblSource]", "[Column4UniqueValue] LIKE '" & Format([txtAreaOfDateCreated], "yyyy\*\") & Prefix & "*'")

    If IsNull(vLast) Then
         iNext = 1
    Else
         iNext = Val(Right(vLast, 3)) + 1
    End If

    Me![txtAreaOfColumn4UniqueValue] = Format([txtAreaOfDateCreated], "yyyy") & Prefix & Format(iNext, "000")
End Sub

Option Two (without DateCreated field):

Private Sub Form_BeforeInsert(Cancel As Integer)
    Dim Prefix As String
    Dim vLast As Variant
    Dim iNext As Integer

    Prefix = "ICLAA"

    vLast = DMax("[Column4UniqueValue]", "[tblSource]", "[Column4UniqueValue] LIKE '" & Format(Date, "yyyy\*\") & Prefix & "*'")

    If IsNull(vLast) Then
         iNext = 1
    Else
         iNext = Val(Right(vLast, 3)) + 1
    End If

    Me![txtAreaOfColumn4UniqueValue] = Format(Date, "yyyy") & Prefix & Format(iNext, "000")
End Sub

Your end results will look exactly like this 2017ICLAA001 and auto increment each year starting from one. Test it by creating a few records then change your computer's date/time clock to a later or earlier year and add another record. It should change with the year and when the year changes it will auto increment to the next highest value for that year. You can test this by toggling the computer year back and forth just to watch the values remain consistent when you add new records.

Answer 3

Simply type "2017ICLAA"000 into the ID field's Format Property