Autonumber headaches

S

Stapes

Hi
I have a form that adds a new Job record to my database. The record
has an autonumber key field. Instead of putting the next highest
number in, the system has suddenly started again at 1 when it should
be 1694!! Trouble is, 1 already exists, so it now has an error- can't
save record due to duplication. Why has this suddenly occurred?

Stapes
 
R

RoyVidar

Stapes said:
Hi
I have a form that adds a new Job record to my database. The record
has an autonumber key field. Instead of putting the next highest
number in, the system has suddenly started again at 1 when it should
be 1694!! Trouble is, 1 already exists, so it now has an error- can't
save record due to duplication. Why has this suddenly occurred?

Stapes

This is a bug. Here is a description of it, also showing a way of
resolving it (though, I think you need to remove the relationship with
other tables temporarily for it to work)
http://support.microsoft.com/default.aspx?scid=kb;en-us;884185
 
S

Stapes

First as a side note, I suggest you may not want to use Autonumber for
that use. Autonumbers are designed to provide unique numbers. It in not
designed to provide numbers in order and for a number of reasons may not do
so. As a result using them in any application where the user sees the
numbers is likely to end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

However all that does not address you specific problem. That is the
duplication of numbers which is in not related to your use of autonumbers
which may or may not be an issue. I have seen information, on that problem,
and while I am going to guess that the fix may involve a compact and repair
and or other solutions, I frankly don't recall the solution. I have never
personally encountered it so I tend not to remember the solutions to
problems I have not had.

Joseph
You obviously have no idea what you are talking about - so leave it to
the experts eh.
Stapes
 
B

BruceM

The suggestion about not using autonumber when you need an incremented
number that is seen by the users is perfectly valid. It is something on
which almost every expert agrees. I might have offered a more definite
suggestion about addressing the problem, but if you are looking for help you
would do well to stay away from insults, even if you think the suggestion is
inadequate.

A search would have turned up quite a bit of information on the subject.
When reporting something that seems like a bug or malfunction you should
mention your version of Access and the latest service pack that was
installed. For instance, there is an old bug in Access 2000, but if you
have the latest service pack the problem has been fixed. I believe that
Allen Browne's site: http://allenbrowne.com has some information on the
subject of duplicated autonumbers. http://www.mvps.org/access/ may have
some information, and the Microsoft knowledge base is often worth checking.
 
S

Stapes

not using autonumber when you need an incremented
number?

I thought that was precisely what an autonumber was for. I usually use
autonumbers for indexes, and the fields that tables are joined on. I
don't display them for the customers benefit, but occasionally display
them to help debug problems.
 
B

BruceM

The point about incremented numbers applies to something like an invoicing
system, where you do not want to have the gaps in the numbering sequence
that will result from, say, a user going to a new invoice record, then
exiting without saving. Auditors don't like that. If the number is hidden
from the user, and is exposed only for diagnostic purposes as you have
described, that is exactly the situation to which autonumbers are
well-suited. There are a few situations in which the autonumber may be
exposed to the user for valid reasons. For instance, a calls database may
provide a reference number so that when the person calls again they can
refer to the previous conversation.
Your original posting gave the impression (although in fairness it was not
specifically stated) that you were trying to obtain the next number in
sequence. Many people who post here are trying to get autonumber to
increment without gaps, so it is not too big a stretch to read that into
your posting. In any case, there is nothing to gain by insulting people who
do not respond as you would wish, even if they make unwarranted assumptions.
I find myself adding additional explanation at times because somebody has
assumed something about my design, but if I try to explain everything all at
once people tend to glaze over and not respond at all. That's just the way
it is with volunteer newsgroups.
By the way, I had a chance to go to Allen Browne's web site. The article is
here:
http://allenbrowne.com/ser-40.html
 
T

Todos Menos [MSFT]

bruce

shut the **** up you fucking faggot


if your autonumber is broken then move to SQL Server

I mean DUH

a piece of shit ass database? are you tired of it yet?
 
T

Todos Menos [MSFT]

bruce

you're fucking stupid

shut the **** up; and stop playing with baby databases

fucking newbie

jeez
 
T

Tony Toews [MVP]

Ignore the reply from A a r o n K e m p f aka T o d o s M e n o s who also doesn't
work for Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
S

Stapes

Hi

Apologies to everyone, especially Joseph, for my insults. I may have
been a bit 'hot under the collar' yesterday. On the whole, I get first
rate information from this group & am really most grateful.

Stapes
 
T

Tony Toews [MVP]

Joseph Meehan said:
(I hope all those using a
[MVP] tag on their name do.)

MVPs are *NOT* Microsoft employees. If we are hired by Microsoft we are no longer
recognized as such.

We are quite independent atlhough a few see us as being quite biased. I assure you
though we can be very tough in our criticisms.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
L

Larry Linson

Joseph Meehan said:
Actually many people here do not work for MS
(I hope all those using a [MVP] tag on their name do.)

No, MVPs are "recognized" by Microsoft for their contribution to the user
community, but are not Microsoft employees. In fact, if an MVP is hired by
Microsoft, he or she is no longer an MVP.

See http://mvp.support.microsoft.com for an explanation of the MVP program.

Larry Linson
Microsoft Access MVP
 
T

Todos Menos [MSFT]

what the **** dude

ADO is _ALWAYS_ faster and better than DAO.. _BECAUSE_ the fact that
MDB is unreliable
 
T

Todos Menos [MSFT]

I mean seriously dude

other than 'Tony won't have to learn a new library' please give _ONE_
benefit to DAO

DAO is obsolete; anyone that uses it should be fired and then spit
upon



DAO hasn't been included with Office, Windows or MDAC for a decade


lose the training wheels you fucking newbie

'oh but i can't live without dao'


what a joke

ADO does everything bigger better faster, jackass
 
Top