AutoNumber Error

[Barb]

I am working with an Access 2003 database. The table I am having a problem
with uses AutoNumber as the primary key. The last record on the table
contains data but does not have an actual number in the primary key field.
When I try to save data entered into the table I get the message "The value
of an (AutoNumber) field cannot be retrieved prior to being saved. Please
save the record that contains the (AutoNumber) field..." Is there any way I
can delete this record, or get a number into the primary key field so that it
can be saved?

 

[Ken Sheridan]

If you can't delete the corrupted record manually, or if you can but it
doesn't solve the problem, then perhaps your best option would be to create
an empty copy of the table and insert all the rows from the problem table
where the value of the primary key column is <= the highest value in the
column, e.g.

INSERT INTO NewTable
SELECT *
FROM ProblemTable
WHERE MyID <= 123456;

If this is successful then you can delete the original table and rename the
new table to the original name. Before deleting the original table you might
first have to delete ant relationships of which it is a part, and then
recreate them when you've filled and renamed the new table.

Ken Sheridan
Stafford, England

 

[Tom Wickerath]

Hi Barb,

The only time I have experienced problems with an Autonumber field in the
past is when the table in question was created with an earlier version of the
JET database engine. In my case, Access 2003 with the latest service pack for
the JET database engine was attempting to assign a duplicate value, which, of
course, was failing since the field was set as a primary key.

The owner of this database confirmed to me that the table had been created
several years ago, around the time of JET SP-4 or earlier. It didn't matter
that I had the latest SP for JET currently; strangely enough, what mattered
was the version of JET at the time the table was created. First, make sure
that any PC's that you use Access with currently have the latest updates for
the operating system, the version of Office, and the JET database engine.
This KB article serves as a good guide to performing these steps:

How to keep a Jet 4.0 database in top working condition
http://support.microsoft.com/?id=303528


I was able to fix the duplicate number problem by copying the existing table
(structure + data) to a new table name. I then deleted the existing
relationships between the source table and other tables, deleted the source
table itself and compacted the database. Finally, I renamed to newly copied
table back to the original name, re-established all the relationships, and
all was well.

You might try this sequence of steps to see if it helps in your case. Before
doing anything, make sure to create a backup copy of your database.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

['69 Camaro]

Hi, Barb.

The table I am having a problem
with uses AutoNumber as the primary key. The last record on the table
contains data but does not have an actual number in the primary key field.

An AutoNumber won't be assigned to the new record until the record is saved,
and the record can't be saved until any restrictions and constraints are
resolved satisfactorily. For example, if a certain column is required, then
the user can't leave it blank when attempting to save the rest of the
record.

When I try to save data entered into the table I get the message "The
value
of an (AutoNumber) field cannot be retrieved prior to being saved.

How are you trying to save the record? Are you entering it directly into
the table and pressing the Save button on the CommandBar or else selecting
the File -> Save menu? If so, that doesn't save the record. It only saves
the object that currently has the focus.

You should only enter and edit data via a form bound to a query that
contains the table, so that data validation and form events can handle the
user's input (including mistakes) in a user-friendly way. The new record
will be saved (and a new AutoNumber assigned) as soon as the record cursor
moves to another record on the form, which can be done using form buttons,
or the record navigation buttons, or the record navigation's "Record # of #"
section.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Ken Sheridan]

Not quite; a value is assigned to an autonumber column as soon as data is
begun to be entered into a new record, not when the record is saved. If the
new record is abandoned for any reason the autonumber value which was
assigned to it is not re-used for the next record unless the database is
first compacted.

In the OP's case the record appears to have been saved but without a value
in the autonumber column, she say's "The last record on the table contains
data but does not have an actual number in the primary key field", which
suggests corruption. The duplicate autonumber value problem is well known,
but I've never come across a case of an empty one before.

Ken Sheridan
Stafford, England

 

['69 Camaro]

Hi, Ken.

Not quite; a value is assigned to an autonumber column as soon as data is
begun to be entered into a new record

You're right. I don't know what I was thinking.

Gunny

 

[George Hepworth]

I thought when I read the OP's statement that maybe she was seeing default
values for certain fields where they were defined being displayed in the new
record and interepreting that as being data without an autonumber.

George

 

[Tony Toews [MVP]]

Not quite; a value is assigned to an autonumber column as soon as data is
begun to be entered into a new record, not when the record is saved.

<picking nits>In a Jet database. However not in a SQL Server
database. </picking nits>However that is highly unlikely to be the
case here.

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
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Ken Sheridan]

That would explain why she can't delete the 'record', but not the error. I'm
still inclined to suspect there's some corruption, but if you're right then
it could be on any row, which might prevent her inserting the rows into a new
table via a single SQL statement. It might be necessary to iterate through
the table in code and insert each row into a new table one by one until the
problem row is encountered.

Ken Sheridan
Stafford, England