Setting Autonumber

[Lani]

Hi...
I'm having some trouble appending a record to my table
with the autonumber data type. I run the append query
with only the created table's field to append, but it
shows that 0 records have been appended. What am I doing
wrong? Thanks for the help!

Lani

 

[Nikos Yannacopoulos]

Lani,

To append to a table with an autonumber field, you must append all (or some
of) the other fields, but not the autonumber one. The value of that one will
be assigned by Access.

HTH,
Nikos

 

[Robin Proctor]

Nikos/Yani

You can append to an Autonumber field with an Append query, providing
the values you supply do not break table validation or relationship
rules you have set up in the database. This is the basis of a common
method of effectively converting an existing Number field to an
Autonumber, whiost retaining the values present in the existing records.

Robin

 

[Ngan Bui]

A followup question about dealing with autonumbers and
appending...

I have two tables, a one-many relationship involving
autonumbers. If I need to append certain records from the
two tables to another set of two tables, how do I go about
keeping the relationship together?

For instance:
tblPerson
PersonID...
1
4
9

tblAddress
AddieID PersonID
2 1
3 1
6 4
11 9
12 9
13 9

If I want to append PersonID 9 name and address to another
set of two tables that has autonumbers for their PK, how
do I do it so I keep the relationship? If I first append
the Person's info, the ID changes and then I can't bring
over the address info because the PersonID don't match.

 

[Robin Proctor]

Ngan

When you append Person 9 (to tblPerson2 say) include the PersonID field
in the Append query:

INSERT INTO tblPerson2 ( PersonID, ... )
SELECT PersonID, ...
FROM tblPerson
WHERE PersonID=9

which should be OK if tblPerson2 has no record with PersonID=9, even if
PersonID in tblPerson2 be an AutoNumber type. I'm not sure why you want
want it to be an AutoNumber here since the origin of records appears to
in tblPerson.

You can then append to tblAddress2 with:

INSERT INTO tblAddress2
SELECT AddieID, PersonID
FROM tblAddress
WHERE PersonID=9

Again the AddieID field in tblAddress2, even if an AutoNumber type, will
be populated with the values from tblAddress.

 

[Ngan Bui]

Robin,

What if both tblPerson and tblPerson2 PK are autonumbers?

I can do the first append...but when Person9 in tblPerson
is copied to tblPerson2, the PK for that person may
change. There may be more records in tblPerson2 than in
tblPerson. So when I append it, Person9's new ID would be
45.

If I want to then append Person9's address to the new
tblAddress2, how do I make sure I grab the right address
and then have the foreign key in the tblAddress2 be the
new PK (45)?

I would like to do an append for more than one person at a
time. An example of this is if two people need to enter
info on people, but they each have a copy of the db (they
live in different cities). After they are finished, I
want to then replicate (I guess that is the word) and
merge the info together.

Ngan