Starting Over at 1

  • Thread starter Thread starter Sondra
  • Start date Start date
S

Sondra

I want to create a database that starts to renumber my
records when the year changes. I was going to use an
autonumber, but realized that wouldn't let me go back to
the number 1 when the year turns 2004. What would someone
suggest????

I have a field in their for the year at this point and I
do have an autonumber field.

Please help.
 
but how can I make it so that the user doesn't have to
enter the next available number. I want them to open the
database and get the next number available. We are using
these numbers for tracking files. Each year we will start
over at 1. For example the file will contain the
following "Year"-"FileType"-"FileNumber". FileNumber will
be consecutive.

Thanks again.
 
I want to create a database that starts to renumber my
records when the year changes.
Click to expand...

First of all the design point: keep the number in a separate field from the
YearNumber.

You can use a little bit of VBA in the BeforeUpdate event on the form,
something like

' don't bother if there is already a number in the box
If Not IsNull(Me!txtSerialNumber) Then Exit Sub

' everything that follows can be forced into a one-line
' function if necessary, but it's spelled out here
' so that you can see what is happening
'
' look up the highest number in the current year
strLookFor = "YearNumber = " & Me!txtYearNumber

' get it into a Variant first because it will be a Null if
' it's the first one this year
varNewSerial = DMax("SerialNumber", "MyTable", strLookFor)

' and adjust it
If IsNull(varNewSerial) Then
' first one so far
varNewSerial = 1

Else
' add one to the last one
varNewSerial = varNewSerial + 1

End If

' also note that there is a chunk of extra checking needed if
' you have a multiuser database, because another user could have
' added a record in the time between reading the old value and you
' putting in yours.


Hope that helps


Tim F
 
Okay I'm not a programming guru... here are the fields I
have so far:

Year
FileCode
FileNumber

Year (DEFAULT VALUE)= Year(Date())
FileCode = Dropdown list to choose from
FileNumber = AutoNumber at present time

So now I want to set this up so that I create it for 2004
so that the FileNumber will no longer be an Autonumber it
will be a Number Data Type Field in my table. But what
and where do I put information to make it automatically
start renumbering at 1 again in 2005.

Just to let you know. I have been replicating this
database yearly so that we have new year data starting at
one with current files number YY-FileCODE-FILENUMBER.
Must have 3 separate databases out there with same format.

PLEASE HELP.
 
in your form where you enter new records, for textbox bound to FileNumber in
default value property enter:
=nz(dmax("FileNumber","Table1","Year=" & Year(Date())),0)+1

should be ok for start
 
replace Table1 with name of your table


Alex Dybenko said:
in your form where you enter new records, for textbox bound to FileNumber in
default value property enter:
=nz(dmax("FileNumber","Table1","Year=" & Year(Date())),0)+1

should be ok for start
Click to expand...
 
replace Table1 with name of your table
Click to expand...

and replace

"Year="

with

"[Year]="

or else (preferred) change the name of the field to something that does not
clash with the reserved word. Try CreateYear or FirstYear or
AnnualVolumeNumber or something descriptive.

B Wishes


Tim F
 
I entered this

=nz(DMax("code#","cscr","[YearNumber]=" & Year(Date())),0)
+1

and my restarts aren't 0001 they are 1001.

Please help again.
-----Original Message-----
replace Table1 with name of your table
Click to expand...

and replace

"Year="

with

"[Year]="

or else (preferred) change the name of the field to something that does not
clash with the reserved word. Try CreateYear or FirstYear or
AnnualVolumeNumber or something descriptive.

B Wishes


Tim F

.
Click to expand...
 
check what returns
=DMax("code#","cscr","[YearNumber]=" & Year(Date()))
probably not null, so you have a record for current year

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



I entered this

=nz(DMax("code#","cscr","[YearNumber]=" & Year(Date())),0)
+1

and my restarts aren't 0001 they are 1001.

Please help again.
-----Original Message-----
replace Table1 with name of your table
Click to expand...

and replace

"Year="

with

"[Year]="

or else (preferred) change the name of the field to something that does not
clash with the reserved word. Try CreateYear or FirstYear or
AnnualVolumeNumber or something descriptive.

B Wishes


Tim F

.
Click to expand...
Click to expand...
 
I really messed this up. Okay let me see if I can decipher
what I have and if you can help me from there:

Fields:
YearNumber - Current Year
CodeNumber - AutoNumber

Now if I read right, I need to creat a separate field for
the numbering of the records which is not Autonumber???
If that is correct I will call the field SerialNumber.

Then I can write the code in the table as follows:

' don't bother if there is already a number in the box
If Not IsNull(Me!txtSerialNumber) Then Exit Sub

' everything that follows can be forced into a one-line
' function if necessary, but it's spelled out here
' so that you can see what is happening
'
' look up the highest number in the current year
strLookFor = "YearNumber = " & Me!txtYearNumber

' get it into a Variant first because it will be a Null if
' it's the first one this year
varNewSerial = DMax("SerialNumber", "CSCR", strLookFor)

' and adjust it
If IsNull(varNewSerial) Then
' first one so far
varNewSerial = 1

Else
' add one to the last one
varNewSerial = varNewSerial + 1

End If


Now you also state that there is a concern if the db is
multiuser (which it is) do I need to write code for that???
 
Now you also state that there is a concern if the db is
multiuser (which it is) do I need to write code for that???
Click to expand...

This looks like my code, so I'll pick this up. <g>

The issue with multi users is that when two users want to get new records
at the first time, they both read the same current value for highest-so-
far; one of them succeeds in getting that record number and the next one
receives a Key Violation error.

The easiest way to handle this is probably to catch the error in the
Form_Error event, and use that to call the allocate-a-number routine again
to get the new next-available number.

The cleanest way is to have a single transaction that allocates the number
and creates the record all in one go. I tend to use a loop that creates
INSERT commands which simply runs until it finds an available number --
albeit usually on the first time round. The trouble with this is that it
has to happen before the user starts typing into the record, so if he or
she cancels out of the record, then the number is orphaned, just like with
an autonumber -- this may or may not be what you want.

Hope that helps a bit

Tim F
 
Back
Top