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

 

[RD]

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

Andreas,

That's a confusing jumble of ideas and questions. For one thing, I
think Autonumber will not work for you. Take a look at writing a
function to produce yuor own ProjectID sequence. When writing a new
record you could use DLookup to grab the last used ID, add one and
hold that value in a variable untill you're ready to commit the
record. By rolling your own ID sequence you can concatenate anything
thing you want to it, reset it at will and generally manipulate it any
way you wish.

HTH,
RD

 

[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!