Can I reset an auto number in an access table

[Guest]

Is there a way to reset an auto number in access tables with out changing the
number for existing records? I have an auto number starting at 260001; as of
January 1, 2007 I want the numbering sequence to start at 270001 however I
don't want the numbers for previous records to be modified. Is this possible
using the field format of auto number?

 

[ChrisM]

I haven't tested this, but I think when I needed to do somthing similar, I
wrote a query that forced a value into the table that was 1 less than the
next value I required, ie somthing like:

INSERT INTO myTable(myAutoNumberField) VALUES (270000);

Cheers,

ChrisM

 

[Douglas J Steele]

Autonumbers exist for one purpose only: to provide a (practically
guaranteed) unique value that can be used as a primary key. No meaning
should ever be assigned to the value of the autonumber. In fact, usually the
user isn't even aware of what value has been assigned.

Now, you appear to have what's often referred to as an "intelligent key",
which isn't a compliment. It would appear that you're trying to store more
than one piece of information in the same field, which is actually a
violation of database normalization rules.

Having said all that, yes it is possible to do what you want. Insert a row
with a record that has a value of 270000, and the next row to be inserted
will get an autonumber value of 270001. Once that's occurred, you can delete
the bogus 270000 record you inserted. Hopefully, though, you'll rethink what
you're doing and not take that route.

 

[Joseph Meehan]

Is there a way to reset an auto number in access tables with out
changing the number for existing records? I have an auto number
starting at 260001; as of January 1, 2007 I want the numbering
sequence to start at 270001 however I don't want the numbers for
previous records to be modified. Is this possible using the field
format of auto number?

Let me recommend a careful reading of Mr. Steele's response.

I will suggest it even stronger. Using an autonumber anywhere that a
user will see it is an almost sure way to befuddle and confuse the users.

Remember that you can combine fields when displaying them so you can
store a two character or digit (note: they are not the same and you should
be careful about which you chose) and a incremental field (see note above)
and display them as if they were one.

If you already have a data related to the record, then you can pull the
year from there. It is almost always better to use existing data than to
make a second field from that data since someone is bound to change one and
the other will be missed.

 

[Guest]

OK, I'll rethink this but I still need help!
I'll simply join in my reports the prefix "26" or "27" to my job number.
But I still have a problem in getting the number to increase incrementally
for me. I want the number to move from 0001 to 0002 and so on. BUT, I have
to have the ability to reset it back to 0001 on January 1st.

 

[Joseph Meehan]

OK, I'll rethink this but I still need help!
I'll simply join in my reports the prefix "26" or "27" to my job
number. But I still have a problem in getting the number to increase
incrementally for me. I want the number to move from 0001 to 0002
and so on. BUT, I have to have the ability to reset it back to 0001
on January 1st.

You can do that using the DMAX function

Me! = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

Replace the 0 if you want a different starting number

I'll have to think about how to modify that to allow you to start over
at 1 again. Maybe basing it on a query that filters out prior years data?

 

[Guest]

Thanks...maybe this is dumb question but I put that code where in my form? I
was trying to use it on the "on focus" but it was not working.

 

[Guest]

When I type in that code the first thing I notice is the code is
automatically removing the ! after Me...furthermore, it is giving me a
perameter error.

Thanks...maybe this is dumb question but I put that code where in my form? I
was trying to use it on the "on focus" but it was not working.

 

[Douglas J. Steele]

Unfortunately, Joseph made a typo.

It should be:

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

where NameOfField is the name of the field that's supposed to hold the
value.

 

[Joseph Meehan]

Unfortunately, Joseph made a typo.

Who me? Well I never! ...

Well maybe ...

Thanks for the correction, I am sure it would have taken me forever to
notice it.

 

[Guest]

When I use this formula (I'm putting the code at the "ON ENTER" of the field
for Project Number), I get the following error
Compile Error
Expected =
so still no luck in getting this to work.
Thanks for your continued help.

 

[Douglas J Steele]

What's the exact code you've got (copy-and-paste it into your reply, as
opposed to retyping it)

 

[Guest]

Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1

Perhaps this is my problem...I have Project Number as the field in my main
form that I'm trying to update and Project Number in a table called
Project_Number Table where I'm trying to keep my next available number.

Thanks again.

 

[Douglas J Steele]

Because of the spaces in your names, you need square brackets:

Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
Table]"),0)+1

 

[aaron.kempf]

Joseph

you're so wrong

there isn't anything confusing about autonumbers.. most people
understand them.

let's just say it's much better to use an autonumber than it is like a
guid or something

if you want complex autonumber like this; you would need to use SQL
Server. It has much more powerful autonumbering than this silly
autonumber bullshit.

A much easier alternative muight be to
a) prepopulate the numbers you want
b) dont make it an autonumber

 

[Guest]

OK, Now I have it partially working. I created a table called
NewProjectNumber_Table and it has only one field called NewProject Number. I
set the number to 260001. This is the only record in this table. When I run
my form it will create a new project number, 260002 which appears to be OK.
However when I create a second project it gives it the same number. I was
expecting my table called NewProjectNumber to be updated to 260002 so when I
went to create another project it would generate the number 260003. This is
not what is happening.
Am I missing a step or am I expecting too much from access?
Thanks again for your help.

Because of the spaces in your names, you need square brackets:

Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
Table]"),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1

Perhaps this is my problem...I have Project Number as the field in my main
form that I'm trying to update and Project Number in a table called
Project_Number Table where I'm trying to keep my next available number.

Thanks again.

 

[Douglas J Steele]

You need to be querying for the maximum value in whatever table it is in
which you're storing the records.

If you're always querying NewProjectNumber_Table, but never updating the
value there, it's always going to return the same value to you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, Now I have it partially working. I created a table called
NewProjectNumber_Table and it has only one field called NewProject Number. I
set the number to 260001. This is the only record in this table. When I run
my form it will create a new project number, 260002 which appears to be OK.
However when I create a second project it gives it the same number. I was
expecting my table called NewProjectNumber to be updated to 260002 so when I
went to create another project it would generate the number 260003. This is
not what is happening.
Am I missing a step or am I expecting too much from access?
Thanks again for your help.

Because of the spaces in your names, you need square brackets:

Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
Table]"),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1

Perhaps this is my problem...I have Project Number as the field in my main
form that I'm trying to update and Project Number in a table called
Project_Number Table where I'm trying to keep my next available number.

Thanks again.

:

What's the exact code you've got (copy-and-paste it into your reply, as
opposed to retyping it)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use this formula (I'm putting the code at the "ON ENTER" of the
field
for Project Number), I get the following error
Compile Error
Expected =
so still no luck in getting this to work.
Thanks for your continued help.


:

Douglas J. Steele wrote:
Unfortunately, Joseph made a typo.

Who me? Well I never! ...

Well maybe ...

Thanks for the correction, I am sure it would have taken me forever
to
notice it.



It should be:

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

where NameOfField is the name of the field that's supposed to

hold
the

value.

 

[Guest]

I'm hoping that you are still watching these posts! If so, I have a question
regarding the advice I received here.

I used the Dmax code and it works perfectly. I actually installed the
program at a customer site this week and put it on their server. They have
multiple people using the db and it appears that they are getting duplicate
values in the project number that I have coded to always return a value
higher then the last value in the table. It works flawlessly on my lap top
but not in the server environment...is that the case?

Thanks so much for your help,
Gretchen

You need to be querying for the maximum value in whatever table it is in
which you're storing the records.

If you're always querying NewProjectNumber_Table, but never updating the
value there, it's always going to return the same value to you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, Now I have it partially working. I created a table called
NewProjectNumber_Table and it has only one field called NewProject Number. I
set the number to 260001. This is the only record in this table. When I run
my form it will create a new project number, 260002 which appears to be OK.
However when I create a second project it gives it the same number. I was
expecting my table called NewProjectNumber to be updated to 260002 so when I
went to create another project it would generate the number 260003. This is
not what is happening.
Am I missing a step or am I expecting too much from access?
Thanks again for your help.

Because of the spaces in your names, you need square brackets:

Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
Table]"),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1

Perhaps this is my problem...I have Project Number as the field in my main
form that I'm trying to update and Project Number in a table called
Project_Number Table where I'm trying to keep my next available number.

Thanks again.

:

What's the exact code you've got (copy-and-paste it into your reply, as
opposed to retyping it)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use this formula (I'm putting the code at the "ON ENTER" of the
field
for Project Number), I get the following error
Compile Error
Expected =
so still no luck in getting this to work.
Thanks for your continued help.


:

Douglas J. Steele wrote:
Unfortunately, Joseph made a typo.

Who me? Well I never! ...

Well maybe ...

Thanks for the correction, I am sure it would have taken me
forever
to
notice it.



It should be:

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) + 1

where NameOfField is the name of the field that's supposed to hold
the
value.

 

[Douglas J. Steele]

Consider this. Person A and Person B are both working on the database. If
Person B queries after Person A queries, but before Person A updates the
database, they're going to get the same number.

In what event are you using the DMax code? You need to do it in the
BeforeInsert event, so that you look it up just before the record is written
to the database. (Yes, this means you cannot tell the user what the record
number will be until it's been saved.) Even there, it's still possible
you'll get the same number twice, so you need to put in error handling in
case the write fails because of a duplicate id so that you can requery to
get the "correct" next id and use it on a second try.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm hoping that you are still watching these posts! If so, I have a
question
regarding the advice I received here.

I used the Dmax code and it works perfectly. I actually installed the
program at a customer site this week and put it on their server. They
have
multiple people using the db and it appears that they are getting
duplicate
values in the project number that I have coded to always return a value
higher then the last value in the table. It works flawlessly on my lap
top
but not in the server environment...is that the case?

Thanks so much for your help,
Gretchen

You need to be querying for the maximum value in whatever table it is in
which you're storing the records.

If you're always querying NewProjectNumber_Table, but never updating the
value there, it's always going to return the same value to you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, Now I have it partially working. I created a table called
NewProjectNumber_Table and it has only one field called NewProject
Number. I
set the number to 260001. This is the only record in this table. When
I run
my form it will create a new project number, 260002 which appears to be OK.
However when I create a second project it gives it the same number. I
was
expecting my table called NewProjectNumber to be updated to 260002 so
when I
went to create another project it would generate the number 260003.
This is
not what is happening.
Am I missing a step or am I expecting too much from access?
Thanks again for your help.

:

Because of the spaces in your names, you need square brackets:

Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
Table]"),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Me!Project Number = Nz(DMax("Project Number","Project_Number Table"),0)+1

Perhaps this is my problem...I have Project Number as the field in
my main
form that I'm trying to update and Project Number in a table called
Project_Number Table where I'm trying to keep my next available number.

Thanks again.

:

What's the exact code you've got (copy-and-paste it into your
reply, as
opposed to retyping it)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use this formula (I'm putting the code at the "ON ENTER"
of the
field
for Project Number), I get the following error
Compile Error
Expected =
so still no luck in getting this to work.
Thanks for your continued help.


:

Douglas J. Steele wrote:
Unfortunately, Joseph made a typo.

Who me? Well I never! ...

Well maybe ...

Thanks for the correction, I am sure it would have taken
me
forever
to
notice it.



It should be:

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) +
1

where NameOfField is the name of the field that's supposed
to hold
the
value.

 

[Guest]

OK, I'm having another wierd issue...hoping you are still monitoring this
thread.

I developed the DB in 2003, I gave it to a customer who is running 2002.
All the code works fine however, when they tab through the field that creates
the project number (using the DMax code you tought me) it will not show in
the field until they use the scroll bar and move the form back and
forth...then it shows up! Even if you get out of the field, it will not
show...only when you physically scroll the form. Additionally, you have to
scroll it only far enough that the field is out of sight (it scrolls under
the form heading bascially). It does not behave that way in my
enviornmnet...only there. Any thoughts?

Consider this. Person A and Person B are both working on the database. If
Person B queries after Person A queries, but before Person A updates the
database, they're going to get the same number.

In what event are you using the DMax code? You need to do it in the
BeforeInsert event, so that you look it up just before the record is written
to the database. (Yes, this means you cannot tell the user what the record
number will be until it's been saved.) Even there, it's still possible
you'll get the same number twice, so you need to put in error handling in
case the write fails because of a duplicate id so that you can requery to
get the "correct" next id and use it on a second try.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm hoping that you are still watching these posts! If so, I have a
question
regarding the advice I received here.

I used the Dmax code and it works perfectly. I actually installed the
program at a customer site this week and put it on their server. They
have
multiple people using the db and it appears that they are getting
duplicate
values in the project number that I have coded to always return a value
higher then the last value in the table. It works flawlessly on my lap
top
but not in the server environment...is that the case?

Thanks so much for your help,
Gretchen

You need to be querying for the maximum value in whatever table it is in
which you're storing the records.

If you're always querying NewProjectNumber_Table, but never updating the
value there, it's always going to return the same value to you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


OK, Now I have it partially working. I created a table called
NewProjectNumber_Table and it has only one field called NewProject
Number.
I
set the number to 260001. This is the only record in this table. When
I
run
my form it will create a new project number, 260002 which appears to be
OK.
However when I create a second project it gives it the same number. I
was
expecting my table called NewProjectNumber to be updated to 260002 so
when
I
went to create another project it would generate the number 260003.
This
is
not what is happening.
Am I missing a step or am I expecting too much from access?
Thanks again for your help.

:

Because of the spaces in your names, you need square brackets:

Me!Project Number = Nz(DMax("[Project Number]","[Project_Number
Table]"),0)+1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Me!Project Number = Nz(DMax("Project Number","Project_Number
Table"),0)+1

Perhaps this is my problem...I have Project Number as the field in
my
main
form that I'm trying to update and Project Number in a table called
Project_Number Table where I'm trying to keep my next available
number.

Thanks again.

:

What's the exact code you've got (copy-and-paste it into your
reply,
as
opposed to retyping it)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


When I use this formula (I'm putting the code at the "ON ENTER"
of
the
field
for Project Number), I get the following error
Compile Error
Expected =
so still no luck in getting this to work.
Thanks for your continued help.


:

Douglas J. Steele wrote:
Unfortunately, Joseph made a typo.

Who me? Well I never! ...

Well maybe ...

Thanks for the correction, I am sure it would have taken
me
forever
to
notice it.



It should be:

Me!NameOfField = Nz(DMax("YOURFIELDNAME","TABLENAME"), 0) +
1

where NameOfField is the name of the field that's supposed
to
hold
the
value.