Autonumber

[crimsonk91]

In my subform when I add a record the first one is assigned #1, when I create
another record the autonumber field puts in the same #1 for record number 2.
I then recieve an error that duplicate entries are in my table and should be
changed to be saved. Why is the autonumber duplicating the same number?

 

[Jerry Whittle]

Is that field an autonumber datatype in the table?

Is this field the primary key for the the table?

Is there any fancy code that inserts the autonumber or does Access take care
of it.

Is this autonumber field the foreign key field to the table on the form,
and, if so, are the parent and child tables joined on this field?

I'm betting it's the last. If it's the FK, it should not be an autonumber
field. Instead it should be Long Integer data type. If liked to the parent
table, Access will try to populate it with the PK data from the table. That
will cause dupes.

 

[Arvin Meyer [MVP]]

You may have and Access 2000 format table without all your Service Packs
connected on every computer that uses the database. To fix this, you will
need to first apply all service packs and hotfixes, then change your
autonumber to a long integer then create a new autonumber. Add the key to
all of the many-side tables. Run update queries with the links on the old
keys to put the new autonumber key in all affected tables. Once you are sure
that everything is working, delete the old number fields and reset all the
relationships on the new ones.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Now import everything into a new clean database.

 

[crimsonk91]

This is a brand new database I'm trying to create. I'm working on one
computer, I have a form with a sub form, in the form is a client with the
usual info; name, address, phone...In the subform is a list of their kids.
I'm not even sure if I need an autonumber for the kids or not. I don't know
how to create the relationship between the two.

 

[Arvin Meyer [MVP]]

Yes you need a key. My guess is that the table is corrupted, so it makes
sense at this point to delete and recreate it. This specific corruption only
occurs when all the Service Packs are not applied, so you need to make sure
of that first.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Ron2006]

It sounds as if you may be confusing the autonumber Key of the second
table with the Foreign Key necessary to link the second table with the
first.


The second table should probably have

1) an ID field that is an autonumber field. usually called ID

2) A second LONG Integer field called something like ParentID. This
should NOT be an autonumber field. This is the foreign Key that will
link the child table with the parent table.


In the child/parent properties of the sub form you should have the
following:

The link Master field with a value ID
The Link Child field with a value of ParentID


Ron

 

[David W. Fenton]

then change your
autonumber to a long integer then create a new autonumber

Er, Autonumber fields can't have the data type changed once they've
been created.

 

[Rick Brandt]

Er, Autonumber fields can't have the data type changed once they've
been created.

Was that new in Jet 4? I have no problem doing do in A97.

 

[David W. Fenton]

Was that new in Jet 4? I have no problem doing do in A97.

Brain fart on my part, I guess.

 

[Arvin Meyer [MVP]]

Nope. There should only be 1 autonumber, that is the Primary Key of the main
table. All secondary tables will have a number of datatype long integer as a
Foreign Key.

 

[Arvin Meyer [MVP]]

Sure they can, but they no longer function as an autonumber. His autonumber
field is corrupted due to a bug in the Access 2000 format that occasionally
occurs upon a compact. I've seen it on 1 client's machines about once every
year or so, and on another client's machine just once in 7 years.

 

[David W. Fenton]

Sure they can, but they no longer function as an autonumber. His
autonumber field is corrupted due to a bug in the Access 2000
format that occasionally occurs upon a compact. I've seen it on 1
client's machines about once every year or so, and on another
client's machine just once in 7 years.

Yes, my brain fart -- it's the other way around that doesn't work
(you can't convert an existing number field to Autonumber).

I'd likely create a new long integer field and populate it with the
old Autonumber values, delete the old Autonumber and add a new
Autonumber field. If you want to retain most of the old Autonumber
values, then I'd create a new table and append the existing data
(excluding any problem records, of course).

 

[Tony Toews [MVP]]

In my subform when I add a record the first one is assigned #1, when I create
another record the autonumber field puts in the same #1 for record number 2.
I then recieve an error that duplicate entries are in my table and should be
changed to be saved. Why is the autonumber duplicating the same number?

Resetting AutoNumbers
http://www.allenbrowne.com/ser-40.html

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database
http://support.microsoft.com/?kbid=257408

AutoNumber Field Duplicates Previous Values After You Compact and
Repair a Database
http://support.microsoft.com?kbid=291162

How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0
Database Engine
http://support.microsoft.com/?id=239114

You may receive an error message when you try to insert a new record
in a table that contains an Autonumber field in Access 2007 or in
Access 2003
http://support.microsoft.com/?id=884185

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/