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

Incrementing Question Revisited

P: n/a
A while ago I posted this for help.

"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? "

I got a response that told me that he would never use this as a PK. I
didn't. The original developer did and I have to work around this.

In response he offered the following function with a call to this
function in the BeforeUpdate event of the bound form. The code window
tells me that rst!New is not set and I get an error at run time that
too few parameters are set with the Set rst = line highlighted in
debug mode.

Does anyone have any idea a) what might be wrong with this approach or
b) could provide a better approach?

Public Function MakeNewKey()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim vDate As String

vDate = Format(Date, "yy")

strSQL = "SELECT Max(Right([RMA #],3)) AS New " & _
"FROM RMAs " & _
"WHERE Mid([RMA #],2,2) = vDate"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Me.txtTestNum = "D" & Format(Date, "yymm") & Format(Nz(rst!New, 0)
+ 1, "000")

rst.Close
Set rst = Nothing
End Function

Thanks for the help.

Troy
Sep 9 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
tr******@comcast.net wrote:
A while ago I posted this for help.

"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? "

I got a response that told me that he would never use this as a PK. I
didn't. The original developer did and I have to work around this.

In response he offered the following function with a call to this
function in the BeforeUpdate event of the bound form. The code window
tells me that rst!New is not set and I get an error at run time that
too few parameters are set with the Set rst = line highlighted in
debug mode.

Does anyone have any idea a) what might be wrong with this approach or
b) could provide a better approach?

Public Function MakeNewKey()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim vDate As String

vDate = Format(Date, "yy")

strSQL = "SELECT Max(Right([RMA #],3)) AS New " & _
"FROM RMAs " & _
"WHERE Mid([RMA #],2,2) = vDate"

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Me.txtTestNum = "D" & Format(Date, "yymm") & Format(Nz(rst!New, 0)
+ 1, "000")

rst.Close
Set rst = Nothing
End Function
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SQL WHERE clause should be like this:

"WHERE Mid([RMA #],2,2) = '" & vDate & "'"

You should set up the Recordset opening like this:

Dim db as DAO.Database, rs As DAO.Recordset

set db = currentdb
set rs = db.OpenRecordset(strSQL)

I've found that not having the object variable "db" set causes the rs
variable to "disappear" after it has opened the recordset.

I believe "New" is an SQL reserved word, so you'll either have to change
the field name or put square brackets around the word "New":

SELECT ... AS [New]

and

rst![New]

Since MakeNewKey() is a Function you should return the generated value
via the function return:

MakeNewKey = "D" & Format(Date, "yymm") & Format(Nz(rst!New,0)+1, "000")

rather than setting the Control "Me.txtTestNum."

For the new calendar-year number reset, you'll have to read the table
that holds the PK to check if the last PK [MAX(PK_column_name)] was in
the previous year. If it was then you'd set the new serial number to
001.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMbMKIechKqOuFEgEQK0tACeLQFjFsAVratx9/GgDBARnl3OXu4AnRoL
7wOXT7t2Off/A0/P6IjK2PVz
=fhIP
-----END PGP SIGNATURE-----
Sep 9 '08 #2

P: n/a
On Sep 9, 3:19 pm, MGFoster <m...@privacy.comwrote:
troy_...@comcast.net wrote:
A while ago I posted this for help.
"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? "
I got a response that told me that he would never use this as a PK. I
didn't. The original developer did and I have to work around this.
In response he offered the following function with a call to this
function in the BeforeUpdate event of the bound form. The code window
tells me that rst!New is not set and I get an error at run time that
too few parameters are set with the Set rst = line highlighted in
debug mode.
Does anyone have any idea a) what might be wrong with this approach or
b) could provide a better approach?
Public Function MakeNewKey()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim vDate As String
vDate = Format(Date, "yy")
strSQL = "SELECT Max(Right([RMA #],3)) AS New " & _
"FROM RMAs " & _
"WHERE Mid([RMA #],2,2) = vDate"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Me.txtTestNum = "D" & Format(Date, "yymm") & Format(Nz(rst!New, 0)
+ 1, "000")
rst.Close
Set rst = Nothing
End Function

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SQL WHERE clause should be like this:

"WHERE Mid([RMA #],2,2) = '" & vDate & "'"

You should set up the Recordset opening like this:

Dim db as DAO.Database, rs As DAO.Recordset

set db = currentdb
set rs = db.OpenRecordset(strSQL)

I've found that not having the object variable "db" set causes the rs
variable to "disappear" after it has opened the recordset.

I believe "New" is an SQL reserved word, so you'll either have to change
the field name or put square brackets around the word "New":

SELECT ... AS [New]

and

rst![New]

Since MakeNewKey() is a Function you should return the generated value
via the function return:

MakeNewKey = "D" & Format(Date, "yymm") & Format(Nz(rst!New,0)+1, "000")

rather than setting the Control "Me.txtTestNum."

For the new calendar-year number reset, you'll have to read the table
that holds the PK to check if the last PK [MAX(PK_column_name)] was in
the previous year. If it was then you'd set the new serial number to
001.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMbMKIechKqOuFEgEQK0tACeLQFjFsAVratx9/GgDBARnl3OXu4AnRoL
7wOXT7t2Off/A0/P6IjK2PVz
=fhIP
-----END PGP SIGNATURE-----
Thank you so much. I hope all of this works. This is such a messed up
problem to have, but I think you just put the final piece in place.

Any thoughts on the best way to evaluate if the PK is in the previous
year and, if it is, how to tell Access to start over at "001"?

Thanks again.

Troy Lee
Sep 9 '08 #3

P: n/a
MGFoster,
Just wanted you to know that it works like a charm. This is a big-time
breakthrough and I learned a lot in the process.

As I stated in my previous post, if you have a good idea for
evaluating the calendar year and resetting the sequence, I would
appreciate it. Normally, I would want to work through that on my own,
but haste is my friend right now.

Thanks again. Brilliant.

Troy Lee
Sep 9 '08 #4

P: n/a
tr******@comcast.net wrote:
MGFoster,
Just wanted you to know that it works like a charm. This is a big-time
breakthrough and I learned a lot in the process.

As I stated in my previous post, if you have a good idea for
evaluating the calendar year and resetting the sequence, I would
appreciate it. Normally, I would want to work through that on my own,
but haste is my friend right now.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It depends on where you're storing the PK serial number. You'd just
look in that table, at the Max(PK), and parse out the year. Then
compare the current date's (Date()) year to the PK's year. If the
current year PK_year then the new serial nbr = 001.

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSMc4UYechKqOuFEgEQJS6wCg/hjk4+pSgakDtrLs8XcRQKzpO04AoNmh
Mc9fofFFjR9G4KzDTP9lpMtZ
=hOGz
-----END PGP SIGNATURE-----
Sep 10 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.