By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

Custom Autonumber Incrementation Question

P: n/a
I have a table that has a PK field with the following format:
Dyymm123. So that, a typical number might look like this D0806270.

The first character is literal and never changes. The next four digits
are derived by the year and month. The final three digits are the
sequential numbers given to units throughout the year. In the above
example, 270 would simply be the 270th issuance for this entire year.
The last three digits reset to 001 on the new year.

My question is what is the best way to automatically increment this
number so that the yy and mm numbers change with the calendar and the
last three digits increment by one each time an employee needs a new
number? And then, how do I reset the last three digits on the change
of calendar year?

Thanks for the help.

Troy Lee
Jun 27 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
tr******@comcast.net wrote:
I have a table that has a PK field with the following format:
Dyymm123. So that, a typical number might look like this D0806270.

The first character is literal and never changes. The next four digits
are derived by the year and month. The final three digits are the
sequential numbers given to units throughout the year. In the above
example, 270 would simply be the 270th issuance for this entire year.
The last three digits reset to 001 on the new year.

My question is what is the best way to automatically increment this
number so that the yy and mm numbers change with the calendar and the
last three digits increment by one each time an employee needs a new
number? And then, how do I reset the last three digits on the change
of calendar year?

Thanks for the help.

Troy Lee
Does it really need to be the PK? Why not use an autonumber as the PK
instead? You can always create the key you want for display purposes.

I would never create the key you mentioned above, if you don't want
sequence breaks, until a new record is saved. Reason, what happens if
you have person A go into a new record (key is generated) and person B
goes into a new record, and person A cancels the add.

You could break the key into secions:
1st field char is status type
2nd field is a date field
3rd field is sequence

You could then make sequence when you add the record
Me.Sequence = Dmax("Sequence","TableName",_
"Year(DateFieldName) = Year(date)") + 1

You can then display the value as
DisplayID : Status & Format(DateField,"yymm") & Sequence
in a query. Use the autnonumber as a link to other tables.

If you absolutely need to make it an id type field, do it in the
BeforeUpdate event of your form.
If Me.NewRecord then
Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"
set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & NZ(rst!Expr1,0) + 1
rst.close
set rst = nothing
Endif

Kusha Las Payas
http://www.youtube.com/watch?v=0wzKT_TEtOQ
Jun 27 '08 #2

P: n/a
On Jun 27, 1:49 pm, Salad <o...@vinegar.comwrote:
troy_...@comcast.net wrote:
I have a table that has a PK field with the following format:
Dyymm123. So that, a typical number might look like this D0806270.
The first character is literal and never changes. The next four digits
are derived by the year and month. The final three digits are the
sequential numbers given to units throughout the year. In the above
example, 270 would simply be the 270th issuance for this entire year.
The last three digits reset to 001 on the new year.
My question is what is the best way to automatically increment this
number so that the yy and mm numbers change with the calendar and the
last three digits increment by one each time an employee needs a new
number? And then, how do I reset the last three digits on the change
of calendar year?
Thanks for the help.
Troy Lee

Does it really need to be the PK? Why not use an autonumber as the PK
instead? You can always create the key you want for display purposes.

I would never create the key you mentioned above, if you don't want
sequence breaks, until a new record is saved. Reason, what happens if
you have person A go into a new record (key is generated) and person B
goes into a new record, and person A cancels the add.

You could break the key into secions:
1st field char is status type
2nd field is a date field
3rd field is sequence

You could then make sequence when you add the record
Me.Sequence = Dmax("Sequence","TableName",_
"Year(DateFieldName) = Year(date)") + 1

You can then display the value as
DisplayID : Status & Format(DateField,"yymm") & Sequence
in a query. Use the autnonumber as a link to other tables.

If you absolutely need to make it an id type field, do it in the
BeforeUpdate event of your form.
If Me.NewRecord then
Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"
set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & NZ(rst!Expr1,0) + 1
rst.close
set rst = nothing
Endif

Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_TEtOQ
Thank you Salad for another great response.

I would never make this the PK either; the original developer did, so
all of the tables are linked through this PK for the entire db. In
addition, only one person in the office will ever create a new
issuance, thus no fear of simultaneous creations of a new number.

I love the idea of breaking it up. I will give that a try. My only
concern is having to "retrofit" all the historical records when I am
through with the new application, so this may become a big problem
down the road. Any thoughts there?
Jun 27 '08 #3

P: n/a
tr******@comcast.net wrote:
On Jun 27, 1:49 pm, Salad <o...@vinegar.comwrote:
>>troy_...@comcast.net wrote:
>>>I have a table that has a PK field with the following format:
Dyymm123. So that, a typical number might look like this D0806270.
>>>The first character is literal and never changes. The next four digits
are derived by the year and month. The final three digits are the
sequential numbers given to units throughout the year. In the above
example, 270 would simply be the 270th issuance for this entire year.
The last three digits reset to 001 on the new year.
>>>My question is what is the best way to automatically increment this
number so that the yy and mm numbers change with the calendar and the
last three digits increment by one each time an employee needs a new
number? And then, how do I reset the last three digits on the change
of calendar year?
>>>Thanks for the help.
>>>Troy Lee

Does it really need to be the PK? Why not use an autonumber as the PK
instead? You can always create the key you want for display purposes.

I would never create the key you mentioned above, if you don't want
sequence breaks, until a new record is saved. Reason, what happens if
you have person A go into a new record (key is generated) and person B
goes into a new record, and person A cancels the add.

You could break the key into secions:
1st field char is status type
2nd field is a date field
3rd field is sequence

You could then make sequence when you add the record
Me.Sequence = Dmax("Sequence","TableName",_
"Year(DateFieldName) = Year(date)") + 1

You can then display the value as
DisplayID : Status & Format(DateField,"yymm") & Sequence
in a query. Use the autnonumber as a link to other tables.

If you absolutely need to make it an id type field, do it in the
BeforeUpdate event of your form.
If Me.NewRecord then
Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"
set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & NZ(rst!Expr1,0) + 1
rst.close
set rst = nothing
Endif

Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_TEtOQ


Thank you Salad for another great response.

I would never make this the PK either; the original developer did, so
all of the tables are linked through this PK for the entire db. In
addition, only one person in the office will ever create a new
issuance, thus no fear of simultaneous creations of a new number.

I love the idea of breaking it up. I will give that a try. My only
concern is having to "retrofit" all the historical records when I am
through with the new application, so this may become a big problem
down the road. Any thoughts there?
Hmmm...I think the code I supplied for your form's BeforeUpdate event
should pretty much create the key of your dreams. You only want the key
created when its a new record value. I called your key "ID" in the
code. Change it to your field name.

BTW, I did'n format the resulting number to be zero padded. It should be
Me.ID = "D" & Format(Date,"yymm") & Format(NZ(rst!Expr1,0) + 1,000)

You'll also notice I prefaced your key with the letter "D". I don't
know if it's always to be a "D". I guess you can figure out what letter
to provide.

You could actually make it a function.
Private Function MakeNewKey() As String
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"

set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & & FormatNZ(rst!Expr1,0) + 1,"000")

rst.close
set rst = nothing
End Function

Then in your forms BeforeUpdate event simpley enter
If Me.NewRecord then Me.ID = MakeNewKey()

The Ketchup Song
http://www.youtube.com/watch?v=n-Razc6_ibE
Jun 27 '08 #4

P: n/a
On Jun 27, 4:43 pm, Salad <o...@vinegar.comwrote:
troy_...@comcast.net wrote:
On Jun 27, 1:49 pm, Salad <o...@vinegar.comwrote:
>troy_...@comcast.net wrote:
>>I have a table that has a PK field with the following format:
Dyymm123. So that, a typical number might look like this D0806270.
>>The first character is literal and never changes. The next four digits
are derived by the year and month. The final three digits are the
sequential numbers given to units throughout the year. In the above
example, 270 would simply be the 270th issuance for this entire year.
The last three digits reset to 001 on the new year.
>>My question is what is the best way to automatically increment this
number so that the yy and mm numbers change with the calendar and the
last three digits increment by one each time an employee needs a new
number? And then, how do I reset the last three digits on the change
of calendar year?
>>Thanks for the help.
>>Troy Lee
>Does it really need to be the PK? Why not use an autonumber as the PK
instead? You can always create the key you want for display purposes.
>I would never create the key you mentioned above, if you don't want
sequence breaks, until a new record is saved. Reason, what happens if
you have person A go into a new record (key is generated) and person B
goes into a new record, and person A cancels the add.
>You could break the key into secions:
1st field char is status type
2nd field is a date field
3rd field is sequence
>You could then make sequence when you add the record
Me.Sequence = Dmax("Sequence","TableName",_
"Year(DateFieldName) = Year(date)") + 1
>You can then display the value as
DisplayID : Status & Format(DateField,"yymm") & Sequence
in a query. Use the autnonumber as a link to other tables.
>If you absolutely need to make it an id type field, do it in the
BeforeUpdate event of your form.
If Me.NewRecord then
Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"
set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & NZ(rst!Expr1,0) + 1
rst.close
set rst = nothing
Endif
>Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_TEtOQ
Thank you Salad for another great response.
I would never make this the PK either; the original developer did, so
all of the tables are linked through this PK for the entire db. In
addition, only one person in the office will ever create a new
issuance, thus no fear of simultaneous creations of a new number.
I love the idea of breaking it up. I will give that a try. My only
concern is having to "retrofit" all the historical records when I am
through with the new application, so this may become a big problem
down the road. Any thoughts there?

Hmmm...I think the code I supplied for your form's BeforeUpdate event
should pretty much create the key of your dreams. You only want the key
created when its a new record value. I called your key "ID" in the
code. Change it to your field name.

BTW, I did'n format the resulting number to be zero padded. It should be
Me.ID = "D" & Format(Date,"yymm") & Format(NZ(rst!Expr1,0) + 1,000)

You'll also notice I prefaced your key with the letter "D". I don't
know if it's always to be a "D". I guess you can figure out what letter
to provide.

You could actually make it a function.
Private Function MakeNewKey() As String
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"

set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & & FormatNZ(rst!Expr1,0) + 1,"000")

rst.close
set rst = nothing
End Function

Then in your forms BeforeUpdate event simpley enter
If Me.NewRecord then Me.ID = MakeNewKey()

The Ketchup Songhttp://www.youtube.com/watch?v=n-Razc6_ibE
WOW! Thanks for the insight.

One more question. Will this automatically reset the counter at the
turn of the year to where the last three digits reset to 001?

Thanks again.

Troy Lee
Jun 30 '08 #5

P: n/a
tr******@comcast.net wrote:
On Jun 27, 4:43 pm, Salad <o...@vinegar.comwrote:
>>troy_...@comcast.net wrote:
>>>On Jun 27, 1:49 pm, Salad <o...@vinegar.comwrote:
>>>>troy_...@comcast.net wrote:
>>>>>I have a table that has a PK field with the following format:
>Dyymm123. So that, a typical number might look like this D0806270.
>>>>>The first character is literal and never changes. The next four digits
>are derived by the year and month. The final three digits are the
>sequential numbers given to units throughout the year. In the above
>example, 270 would simply be the 270th issuance for this entire year.
>The last three digits reset to 001 on the new year.
>>>>>My question is what is the best way to automatically increment this
>number so that the yy and mm numbers change with the calendar and the
>last three digits increment by one each time an employee needs a new
>number? And then, how do I reset the last three digits on the change
>of calendar year?
>>>>>Thanks for the help.
>>>>>Troy Lee
>>>>Does it really need to be the PK? Why not use an autonumber as the PK
instead? You can always create the key you want for display purposes.
>>>>I would never create the key you mentioned above, if you don't want
sequence breaks, until a new record is saved. Reason, what happens if
you have person A go into a new record (key is generated) and person B
goes into a new record, and person A cancels the add.
>>>>You could break the key into secions:
1st field char is status type
2nd field is a date field
3rd field is sequence
>>>>You could then make sequence when you add the record
Me.Sequence = Dmax("Sequence","TableName",_
"Year(DateFieldName) = Year(date)") + 1
>>>>You can then display the value as
DisplayID : Status & Format(DateField,"yymm") & Sequence
in a query. Use the autnonumber as a link to other tables.
>>>>If you absolutely need to make it an id type field, do it in the
BeforeUpdate event of your form.
If Me.NewRecord then
Dim strSQL As String
Dim rst As DAO.Recordset
strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"
set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & NZ(rst!Expr1,0) + 1
rst.close
set rst = nothing
Endif
>>>>Kusha Las Payashttp://www.youtube.com/watch?v=0wzKT_TEtOQ
>>>Thank you Salad for another great response.
>>>I would never make this the PK either; the original developer did, so
all of the tables are linked through this PK for the entire db. In
addition, only one person in the office will ever create a new
issuance, thus no fear of simultaneous creations of a new number.
>>>I love the idea of breaking it up. I will give that a try. My only
concern is having to "retrofit" all the historical records when I am
through with the new application, so this may become a big problem
down the road. Any thoughts there?

Hmmm...I think the code I supplied for your form's BeforeUpdate event
should pretty much create the key of your dreams. You only want the key
created when its a new record value. I called your key "ID" in the
code. Change it to your field name.

BTW, I did'n format the resulting number to be zero padded. It should be
Me.ID = "D" & Format(Date,"yymm") & Format(NZ(rst!Expr1,0) + 1,000)

You'll also notice I prefaced your key with the letter "D". I don't
know if it's always to be a "D". I guess you can figure out what letter
to provide.

You could actually make it a function.
Private Function MakeNewKey() As String
Dim strSQL As String
Dim rst As DAO.Recordset

strSQL = "SELECT Max(Right([ID],3)) AS Expr1 " & _
"FROM TableName " & _
"WHERE Mid(Id,2,2) = Format(Date,""yy"")"

set rst = currentdb.openrecordset(strsql,dbopensnapshot)
Me.ID = "D" & Format(Date,"yymm") & & FormatNZ(rst!Expr1,0) + 1,"000")

rst.close
set rst = nothing
End Function

Then in your forms BeforeUpdate event simpley enter
If Me.NewRecord then Me.ID = MakeNewKey()

The Ketchup Songhttp://www.youtube.com/watch?v=n-Razc6_ibE


WOW! Thanks for the insight.

One more question. Will this automatically reset the counter at the
turn of the year to where the last three digits reset to 001?
I guess the best way to see if it resets is to test it out. Make a copy
of your front/back end. Link the tables in the front end copy to the
backend copy. Now change your computer's date to 1/1/2009. Then add a
record and see if it works. Since it's going by the system date, I
expect it would change from 08 to 09. After the test, change your
calendar/system date back.

Lambada
http://www.youtube.com/watch?v=5AfTl5Vg73A
Thanks again.

Troy Lee
Jun 30 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.