Year Numbers

[Lyle Fairfield]

Year Numbers

A recent post prompts me to point out a capability of JET 4.0, to set the
seed and increment of identity columns. (This is not a solution for that
post.)

I create a Table named PurchaseOrders with an AutoNumber Field named ID.

-----
I run this code:
CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
COLUMN ID IDENTITY (2005000001, 1)"
I add a New Record to PurchaseOrders. Its ID is 2005000001.
I add another New Record to PurchaseOrders. Its ID is 2005000002.

I note that I can create 999 999 records for 2005. If this is not
sufficient, I can use a Seed of 5000000001 which will allow for 999 999
999 records for 2005 (5). I will be limited to 99 999 999 records for
2010 (10) of course.

2006 comes along.
I run this code:
CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
COLUMN ID IDENTITY (2006000001, 1)"
I add a New Record to PurchaseOrders. Its ID is 2006000001.
I add another New Record to PurchaseOrders. Its ID is 2006000002.

Someone I love dearly says, "Oh honey, I forgot to enter five records for
2005; now you'll hate me forever!'
I say, "Not quite!"

Being fiendishly clever I look up the last ID for 2005. It is 2005008345.
I run this code:
CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
COLUMN ID IDENTITY (2005008346, 1)"
She adds her five New Records to PurchaseOrders. Their IDs are
2005008346, 2005008347, 2005008348, 2005008349, 2005008350.

Being fiendishly clever (twice already!) I look up the last ID for 2006.
It is 2006000021.
I run this code:
CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
COLUMN ID IDENTITY (2006000022, 1)"
I add a New Record to PurchaseOrders. Its ID is 2006000022.
I add another New Record to PurchaseOrders. Its ID is 2006000023.

Life is good.

Notes:
1. Yes, you must use OLEDB and JET 4.0. (This means that you can do this
with Access Version 2000 or later and ADO, that is, just use the code as
shown.)
2. Yes, I know this requires user intervention and will not happen
automatically at midnight on December 31 / January 1. I would not suggest
implementing this idea in a situation where an automatic rollover is
required.
3. Yes, I know that one can create other kinds of solutions involving
calculated fields. I'm not recommending this method as a solution to
anything; I'm pointing out that it can be done.




--
Lyle Fairfield

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: Year Numbers
<snip>
: I run this code:
: CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
: COLUMN ID IDENTITY (2005000001, 1)"
: I add a New Record to PurchaseOrders. Its ID is 2005000001.
: I add another New Record to PurchaseOrders. Its ID is 2005000002.
<snip>
: 2006 comes along.
: I run this code:
: CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
: COLUMN ID IDENTITY (2006000001, 1)"
: I add a New Record to PurchaseOrders. Its ID is 2006000001.
: I add another New Record to PurchaseOrders. Its ID is 2006000002.

: Someone I love dearly says, "Oh honey, I forgot to enter five records for
: 2005; now you'll hate me forever!'
: I say, "Not quite!"

: Being fiendishly clever I look up the last ID for 2005. It is 2005008345.
: I run this code:
: CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
: COLUMN ID IDENTITY (2005008346, 1)"
etc <snipped>
: 2. Yes, I know this requires user intervention and will not happen
: automatically at midnight on December 31 / January 1. I would not suggest
: implementing this idea in a situation where an automatic rollover is
: required.

I don't believe this. Am I just so left behind in an old Unix
universe where arranging something like this would just
require a few more variables, a little more code, an ability
to read the system date and the equivalent of a cron utility,
that I don't recognize that automating this isn't possible,
or is it just that accomplishing it is more trouble than you
think worthwhile?
--thelma
: --
: Lyle Fairfield

[Lyle Fairfield]

It would be quite simple.
What wouldn't be quite simple is to decide when 2005 ends in the User's
world. Nor would it be so simple to deal with the five 2005 records
that we forgot to enter after we rolled over to 2006. Of course, we
would have to ensure that we did not automatically roll over our
numbers multiple times. But all this could be done.
Regardless, these embellishments are beyond the scope of what I am
trying to demonstrate, viz, the use of seeds and increments in JET 4.0
to number records by year (or month or whatever).
I invite you to extend this procedure by adding the automatic turnover
component, and the [don't do it twice] safety component, and the [what
if I wanna roll the clock back (and then forward again)] component.
I would be "enchanté"!

[RoyVidar]

Lyle Fairfield wrote:
[snip]

I create a Table named PurchaseOrders with an AutoNumber Field named ID.

-----
I run this code:
CurrentProject.AccessConnection.Execute "ALTER TABLE PurchaseOrders ALTER
COLUMN ID IDENTITY (2005000001, 1)"
I add a New Record to PurchaseOrders. Its ID is 2005000001.
I add another New Record to PurchaseOrders. Its ID is 2005000002. [snip] Notes:
1. Yes, you must use OLEDB and JET 4.0. (This means that you can do this
with Access Version 2000 or later and ADO, that is, just use the code as
shown.)

[snip]
Change one word in the SQL statement, and you can run it anyway you like
DoCmd.RunSql, currentdb.execute, doubleclick a stored query

"ALTER TABLE PurchaseOrders ALTER COLUMN ID COUNTER (2005000001, 1)"

as long as the autonumber field isn't part of relationships.

[Lyle Fairfield]

Yes. I had not thought to try Counter, and had thought that modifying
the seed and increment values were oledb/ado dependent as things such
as creation of a decimal field are, or are documented to be.

[Peter Sutton]

On 30 Dec 2005 08:20:51 -0800, "Lyle Fairfield"
<ly***********@aim.com> wrote:

It would be quite simple.
What wouldn't be quite simple is to decide when 2005 ends in the User's
world.
Funny that!! But why do people feel compelled to have the year in the
primary key? Something to be avoided wherever possible in my opinion
but I do recognize you are demonstrating technique, not advocating
anything.
Nor would it be so simple to deal with the five 2005 records
that we forgot to enter after we rolled over to 2006.
Another way, which may or may not be better but does work with Jet
versions prior to 4, is to use an append query, specifying the PK
values. Of course, this will be adding one or more records, not
setting the next value as per your suggestion.
Of course, we
would have to ensure that we did not automatically roll over our
numbers multiple times. But all this could be done.
Regardless, these embellishments are beyond the scope of what I am
trying to demonstrate, viz, the use of seeds and increments in JET 4.0
to number records by year (or month or whatever).
I invite you to extend this procedure by adding the automatic turnover
component, and the [don't do it twice] safety component, and the [what
if I wanna roll the clock back (and then forward again)] component.
I would be "enchanté"!

<smile>

P

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: It would be quite simple.
: What wouldn't be quite simple is to decide when 2005 ends in the User's
: world. Nor would it be so simple to deal with the five 2005 records
: that we forgot to enter after we rolled over to 2006. Of course, we
: would have to ensure that we did not automatically roll over our
: numbers multiple times. But all this could be done.
: Regardless, these embellishments are beyond the scope of what I am
: trying to demonstrate, viz, the use of seeds and increments in JET 4.0
: to number records by year (or month or whatever).
: I invite you to extend this procedure by adding the automatic turnover
: component, and the [don't do it twice] safety component, and the [what
: if I wanna roll the clock back (and then forward again)] component.
: I would be "enchanté"!

Ok. I've tried it. It doesn't seem to be such a great idea anymore
because it depends so heavily on the system clock: I can imagine all
sorts of havoc wrought if something should go wrong with that system
clock...

It's neither tested code nor air code. I tried to anticipate details,
but I haven't had access to Access [running Max OS X here] for quite a
while, and my usual style of coding is to make lots of mistakes and
let the computer choke on them so that 'we' work together until 'we'
finally get something that works

Elookup is an extension of Dlookup that I found (via google) on Allen
Browne's most invaluable site. Thank you Allen Browne: I've gotten
much more help from you than I've ever acknowledged.
'LF> => Lyle
'TRL> => Thelma
dim IDnext as bigint 'Evil Global Variable for remembering last record
'in current year
private sub form_open()

'---------------------------------------------------------------------'
'LF> What wouldn't be quite simple is to decide when 2005 ends in the '
'LF> User's world. '
'---------------------------------------------------------------------'
'TRL> Below is my attempt to make that decision: this would need to go'
'TRL> into every form that might add a record to the table, and still '
'TRL> I wouldn't be protected if user edited the table directly! '

dim CurYear as String
CurYear = Left(Date,4) 'Current Seed should begin w/ CurrentYear
'Date is function I found that returns
'SystemDate
'as a (string)variant mm-dd-yyyy

dim oMax as string
oMax = "ID DESC" 'Order by for Allen Browne's Elookup fn.

IDnext = Elookup("ID","PurchaseOrders", ,oMax) ' This is table's IDlast
if int(Left(IDnext,4)) < int(CurYear) Then 'Do this ONLY when year
'advances
IDnext = int(CurYear & "000001")
CurrentProject.AccessConnection.Execute "ALTER TABLE _
PurchaseOrders ALTER COLUMN ID IDENTITY (" & IDnext & ", 1)"
else
IDnext = IDnext+1 ' Now it's really IDnext
end if

' Any other open code...

end sub
'------------------------------------------------------------------'
'LF> Nor would it be so simple to deal with the five 2005 records '
'LF> that we forgot to enter after we rolled over to 2006. '
'------------------------------------------------------------------'
'TRL> For this I would have something like a combo box SetYear to
'TRL> allow resetting of the year within a range that I controlled:
'TRL> for most applications I don't think I'd want to allow user to
'TRL> reset the year to something like 1685(birth of Bach) or 3300 or...
'In the Before_update event code:

dim IDre as variant
dim minval as bigint, dim maxval as bigint
dim yrSQL as string, dim oMax as string
if Me.SetYear > 0 Then
minval = int(Me.SetYear.text & "000001")
maxval = int(format(Me.SetYear.text+1) & "000001")
yrSQL = "ID >= minval and ID < maxval" 'Criteria
oMax = "ID DESC" 'Order by
IDre = Elookup("ID","PurchaseOrders", _
yrSQL,oMax) ' This is IDlatest for that year
if isNull(IDre) Then IDre = minval-1 Then
CurrentProject.AccessConnection.Execute "ALTER TABLE _
PurchaseOrders ALTER COLUMN ID IDENTITY (IDre+1, 1)"
end if

' In the After_Update event code:

if Me.SetYear = 0 Then
IDnext = IDnext + 1 ' Keep updated for possible resets below
Else ' Need to Put NextRecord ID back to Current
CurrentProject.AccessConnection.Execute "ALTER TABLE _
PurchaseOrders ALTER COLUMN ID IDENTITY (IDnext, 1)"
Me.SetYear.Value = 0 ' Unset so year won't reset w/o Request
End If

------------------------------------------

--thelma
I tried

[Thelma Lubkin]

Thelma Lubkin <th****@alpha2.csd.uwm.edu> wrote:
<snip text, code...>

: 'In the Before_update event code:
<snip>

No--Before and After_Update is too much; I should use
Before and After_Insert events.

Happy NewYear to All
--thelma

[Lyle Fairfield]

Just looking at the code makes me believe Old Unix coders have logic
and clarity pretty well down pat. I'll squirrel away a copy against
future need if you don't mind.

[Lyle Fairfield]

I would disallow 1946.
How do you feel about 1770 ... 1833?
Any others?

[rkc]

Thelma Lubkin wrote:

dim IDnext as bigint 'Evil Global Variable for remembering last record
'in current year

Public variables in class modules can be evil. They are unmanaged
properties.

Declaring them private removes any threat from the outside world.

Writing Property get/set/let procedures makes them accessible to the
outside world while providing a way to manage potential evil.

[Lyle Fairfield]

But does Dim create a Public/Global variable?

[Thelma Lubkin]

Lyle Fairfield <ly***********@aim.com> wrote:
: Just looking at the code makes me believe Old Unix coders have logic
: and clarity pretty well down pat. I'll squirrel away a copy against
: future need if you don't mind.

I'm saving this to show to my husband the next time he tries to
convince me that I can't explain anything at all; of course, I
never can, to him. We've done several projects together where he does
the physics and I write the code, and still, somehow, stayed out of
divorce court.

Do what you want with the thing.

As to excluded numbers, yours look like a genealogy project.

--thelma

[Lyle Fairfield]

Thelma Lubkin wrote:

Lyle Fairfield <ly***********@aim.com> wrote:
: Just looking at the code makes me believe Old Unix coders have logic
: and clarity pretty well down pat. I'll squirrel away a copy against
: future need if you don't mind.

I'm saving this to show to my husband the next time he tries to
convince me that I can't explain anything at all; of course, I
never can, to him.

But I Don't Want to pass into the world of Eunuchs!

[rkc]

Lyle Fairfield wrote:

But does Dim create a Public/Global variable?

I guess the answer to that made that whole thing pointless, huh?