Can I reset an auto number in an access table

[Douglas J. Steele]

Sorry, no idea. I've never heard of this happening.

If no one else chimes in, you might try reposting you question, rather than
tagging on to an existing thread (responders often avoid thread that have
been answered). Give as much detail as you can when you repost.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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!)


message
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]

Another problem with this code...
The project number populates perfectly however if the user is in the project
number field and either minimizes the form or uses the scroll bar at the
bottom of the form to flip through records it reassigns the project number of
all the recrods! So if I have 15 records, and I'm on ProjectNumber 15 and hit
the scroll bar, ProjectNumber 15 is automatically changed to ProjectNumber 30
and projectNumber 1 becomes number 16. Very disturbing and frustratin. What
did I do wrong?
Thanks in advance!~

Sorry, no idea. I've never heard of this happening.

If no one else chimes in, you might try reposting you question, rather than
tagging on to an existing thread (responders often avoid thread that have
been answered). Give as much detail as you can when you repost.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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!)


message
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.