Append to table with autonumber

[origin7891]

Hi,

I have a table where there is a field set as autonumber.

i want to be able to add information from another table using an append query, and for it to automatically assign an autonumber, but I get the error message saying that it cant append due to "validation rule violations".

I want to be able to do this without using VBA.

Any help would be greatly apprecciated.

 

[John Big Booty]

Are you trying to append information into an AutoNumber field, if so this will cause you problems.

 

[origin7891]

No not trying to append to the autonumber field, but trying to append to other fileds in a table with an autonumber. This autonumber field is also the primary key.

 

[boblarson]

You could also have a problem if you are trying to insert nulls where nulls aren't allowed.

 

[John Big Booty]

I think you will need to look at how each field is set up, and the information that you are trying to append. One of the field may be set up as Indexed Yes (No Duplicates).

My suggestion would be to create a new append query and build it up one field at a time until you find the one that causes the validation error.

 

[neileg]

Not clear what you are doing with the autonumber field. You can append a value to an autonumber field so long as that value is integer and unique. As Bob implies, if you don't have a value, or it is null, then don't try to append to the autonumber and Access will allocate one.

Of course, if you have validation rules set for other fields, it may be that your data is failing these rules and it is nothing to do with the autonumber.

 

[kzutter]

In your append query, select all of the (desired) fields, except the Autonumber field. The receiving table will add values to its Autonumber field.