Year prefix on primary key autonumber, but restart with '1' yearly

[AndreasO]

Basically keeping the original autonumber and create another auto added one
with the prefix, have all foreign tables use the auto added number while the
autonumber runs in the background....?
All in Main table:
1. Rename and keep the existing autonumber (ProjectID_orig) in order to keep
all existing records as they are.
(do need to delete the relation ships and reinstall after renaming, scary)
2. Create new ProjectID (auto added) and copy all existing original
ProjectIDs into the new field.
3. Create field for year prefix
4. Concatenating the year prefix YY with the ProjectID and have the form do
that at the event Form_BeforeUpdate

Did I think this right, current ProjectID (the autonumber) runs from 1 to
1300 ?
I don't need to change the existing records ProjectID's

I could have a senario where I have two ProjectIDs with one ProjectID_orig,
not good...
What does the concatenating code look like?
How can I set the auto added ProjectID number back to zero at the beginning
of a new year?
Will the foreign tables with the current field ProjectID still run while I
like to keep the existing records' ProjectID?

Can I add the YY prefix and concatenate in the code below for the? How?
Plus restart with 1 at the new year...

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[ProjectID]) Then
Me.[ProjectID] = Nz(DMax("[ProjectID]", "Main"), 0) + 1
End If
End Sub


Thanks for your help
Andreas

 

[AndreasO]

I guess I have to forget about this autonumber thing and copying stuff.
I did not want to loose any existing records.
Does anyone have a link to sample codes with the two digit year auto add
number ID? I did find a code here that concatenates a prefix, but it does not
change from year to year.

Thanks for your help
Andreas

 

[John W. Vinson]

I guess I have to forget about this autonumber thing and copying stuff.
I did not want to loose any existing records.
Does anyone have a link to sample codes with the two digit year auto add
number ID? I did find a code here that concatenates a prefix, but it does not
change from year to year.

Thanks for your help
Andreas

It would be easy enough to do. Assuming you want to have an ID like

08-003125

entered as the last record on December 31 this year, and

09-000001

as the first record on January 2 next year; and that the form has a textbox
named txtID bound to the ID field, use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[ID]", "[tablename]", "[ID] LIKE Format(Date(), 'yy\*'")
iNext = NZ(vLast) + 1
Me!txtID = Format(Date, "yy") & "-" & Format(iNext, "000000")
End Sub

 

[AndreasO]

Thanks John, will give me something to do during the holidays.
Happy Holidays and a Happy New Year 2009 to you and everyone!

Andreas

I guess I have to forget about this autonumber thing and copying stuff.
I did not want to loose any existing records.
Does anyone have a link to sample codes with the two digit year auto add
number ID? I did find a code here that concatenates a prefix, but it does not
change from year to year.

Thanks for your help
Andreas

It would be easy enough to do. Assuming you want to have an ID like

08-003125

entered as the last record on December 31 this year, and

09-000001

as the first record on January 2 next year; and that the form has a textbox
named txtID bound to the ID field, use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[ID]", "[tablename]", "[ID] LIKE Format(Date(), 'yy\*'")
iNext = NZ(vLast) + 1
Me!txtID = Format(Date, "yy") & "-" & Format(iNext, "000000")
End Sub

 

[AndreasO]

I get an error, something is missing in the vLast line, it is creating the id
but not the second...
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE
Format(Date(), 'yy\*'")
iNext = Nz(vLast) + 1
Me![Project ID] = Format(Date, "yy") & "-" & Format(iNext, "0000")
End Sub

I guess I have to forget about this autonumber thing and copying stuff.
I did not want to loose any existing records.
Does anyone have a link to sample codes with the two digit year auto add
number ID? I did find a code here that concatenates a prefix, but it does not
change from year to year.

Thanks for your help
Andreas

It would be easy enough to do. Assuming you want to have an ID like

08-003125

entered as the last record on December 31 this year, and

09-000001

as the first record on January 2 next year; and that the form has a textbox
named txtID bound to the ID field, use the Form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[ID]", "[tablename]", "[ID] LIKE Format(Date(), 'yy\*'")
iNext = NZ(vLast) + 1
Me!txtID = Format(Date, "yy") & "-" & Format(iNext, "000000")
End Sub

 

[AndreasO]

I get an error, something is missing in the vLast line, it is creating the
the first id as shown below

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]"
iNext = Nz(vLast) + 1
Me![Project ID] = Format(Date, "yy") & "-" & Format(iNext, "0000")
End Sub

But running it with the full line to created followinig ids not.
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE Format(Date(),
'yy\*'")

There is a ) missing somewhere or one to many, I tried a lot of ......

Thanks for any help.
Andreas

 

[John W. Vinson]

I get an error, something is missing in the vLast line, it is creating the
the first id as shown below

Well, that's not the only error if you want this number to start over with 1
every year. If you do, then you need to search for the maximum value *so far
in the current year*; your current code (if fixed) would find the maximum
value in the table.

Try

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE '" _
& Format(Date, "yy\*\'"))
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 3)) + 1
End If
Me![Project ID] = Format(Date, "yy") & "-" & Format(iNext, "0000")
End Sub

 

[John W. Vinson]

I get an error, something is missing in the vLast line, it is creating the id
but not the second...
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE
Format(Date(), 'yy\*'")
iNext = Nz(vLast) + 1
Me![Project ID] = Format(Date, "yy") & "-" & Format(iNext, "0000")
End Sub

Ah. My mistake - word wrap in the posting should have been corrected in the
code. See my reply in the other thread, and please accept my apologies!

 

[AndreasO]

Its giving me the first one ok... 08-0001, but the next ones are all
08-0000.... we are getting closer...

I get an error, something is missing in the vLast line, it is creating the
the first id as shown below

Well, that's not the only error if you want this number to start over with 1
every year. If you do, then you need to search for the maximum value *so far
in the current year*; your current code (if fixed) would find the maximum
value in the table.

Try

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE '" _
& Format(Date, "yy\*\'"))
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 3)) + 1
End If
Me![Project ID] = Format(Date, "yy") & "-" & Format(iNext, "0000")
End Sub

 

[AndreasO]

I also get two -- (i.e. 08--0005) every other record after manually
advancing the last digit..

 

[John W. Vinson]

Its giving me the first one ok... 08-0001, but the next ones are all
08-0000.... we are getting closer...

Put a breakpoint in the code by clicking in the grey bar to the left of the
vLast= statement. Try to insert a record, the code will stop at that point.
You can then step through the code using Debug... Step (or F8, the hotkey to
do the same).

Check the values of the variables and see if they're being assigned correctly.
What I posted was untested "air code", since I don't have a copy of your
database (nor do I want one, thank you!). If there are things in the code that
you don't understand please ask, I'll try to explain it - but please don't
treat it as a magical black box that does everything you want!

I get an error, something is missing in the vLast line, it is creating the
the first id as shown below

Well, that's not the only error if you want this number to start over with 1
every year. If you do, then you need to search for the maximum value *so far
in the current year*; your current code (if fixed) would find the maximum
value in the table.

Try

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[Project ID]", "[Main]", "[Project ID] LIKE '" _
& Format(Date, "yy\*\'"))
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 3)) + 1
End If
Me![Project ID] = Format(Date, "yy") & "-" & Format(iNext, "0000")
End Sub

 

[AndreasO]

I fixed it....
Its the 4th character, not Val(Mid(vLst, 3)) + 1

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

Thanks again!

 

[SierraJuliet]

I want to do more or less the same thing. The only difference is that my format is:
1015/0001
1015/0002
...
The 10 refers to the table and the 15 to the year.
In 2016 I want it to go:
1016/0001
1016/0002
...
The table is "RoadIM" and the id field is "RIMID"
My code is:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[RIMID]", "[RoadIM]", "[RIMID] LIKE "I don't know")
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 4)) + 1
End If
Me![RIMID] = "10" & Format(Date, "yy") & "/" & Format(iNext, "0000")
End Sub

Every valid expression I have put after the LIKE has resulted in every new record taking the 1015/0001 id.
What I want is to put after the LIKE something that means "10yy/*", where "yy" is the current year.

I would really appreciate any help, because this has caused me hours of frustration, since I'm new to basic.
Thank you!

I am writing this for anyone else to get the quick and dirty to get up and running. So this is straight to the point. Tested in MS Access 2013 standard database (i.e. didn't change to ANSI-92).

Add the following to your form's [Event Procedure] BEFOREINSERT otherwise if you update content in the record later it WILL change the record number. You've been warned! Do not use the date of a "Last Modified" otherwise you will regret it in the future if you change/update anything in the record. Ensure you have a dedicated "DateCreated" column that doesn't change after inserting/adding the record.

Here is the code:


Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim vLast As Variant

Dim iNext As Integer

vLast = DMax("[RIMID]", "[RoadIM]", "[RIMID] LIKE '" & Format([txtDateCreated], "yy\*\'"))

If IsNull(vLast) Then

iNext = 1

Else

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

End If

Me![RIMID] = "10" & Format([txtDateCreated], "yy") & "/" & Format(iNext, "0000")

End Sub



"[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. 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.

Now enter the following otherwise the VBA will kick an error.
Go to your Form and navigate to Property Sheet > Data tab > Default Value is all of the following

="10" & Format([txtDateCreated],"yy") & "/" & Format([txtRIMID],"0000")

 

[SierraJuliet]

I want to do more or less the same thing. The only difference is that my format is:
1015/0001
1015/0002
...
The 10 refers to the table and the 15 to the year.
In 2016 I want it to go:
1016/0001
1016/0002
...
The table is "RoadIM" and the id field is "RIMID"
My code is:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim vLast As Variant
Dim iNext As Integer
vLast = DMax("[RIMID]", "[RoadIM]", "[RIMID] LIKE "I don't know")
If IsNull(vLast) Then
iNext = 1
Else
iNext = Val(Mid(vLast, 4)) + 1
End If
Me![RIMID] = "10" & Format(Date, "yy") & "/" & Format(iNext, "0000")
End Sub

Every valid expression I have put after the LIKE has resulted in every new record taking the 1015/0001 id.
What I want is to put after the LIKE something that means "10yy/*", where "yy" is the current year.

I would really appreciate any help, because this has caused me hours of frustration, since I'm new to basic.
Thank you!

This is an edit because the last one I posted will not auto increment the last value. The following is the code will allow increment value and starts over each year to 0001. All changes are colored red.

I am writing this for anyone else to get the quick and dirty to get up and running. So this is straight to the point. Tested in MS Access 2013 standard database (i.e. didn't change to ANSI-92).

Add the following to your form's [Event Procedure] BEFOREINSERT otherwise if you update content in the record later it WILL change the record number. You've been warned! Do not use the date of a "Last Modified" otherwise you will regret it in the future if you change/update anything in the record. Ensure you have a dedicated "DateCreated" column that doesn't change after inserting/adding the record.

*Columns Required: [AutoNumber] <=not used here, [Column with Custom Name/Number], [DateCreated]

Here is the code:


Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim Prefix As String
Dim vLast As Variant
Dim iNext As Integer​

Prefix = "10"​

vLast = DMax("[RIMID]", "[RoadIM]", "[RIMID] LIKE '" & Prefix & Format([txtDateCreated], "yy\*\'"))​

If IsNull(vLast) Then

iNext = 1

Else

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

End If

Me![RIMID] = Prefix & Format([txtDateCreated], "yy") & "/" & Format(iNext, "0000")​

End Sub



"[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. 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.