I have a record ID field that is setup as an auto number. When the append query runs if the record ID is already used it wont update that record. I there away to fix this.

I know I can change the property Yes (No Duplicates) to Yes Duplicates OK but Im afriad that could cause more issues.

thanks

jon

Don't add the new record ID, let Access generate a new number for you...

i thought about that but there is a problem with that. I didnt put everything in the orignal post. I'm running 2 append query's back to back to 2 different tables. 1 table captures raw data the other is collecting the dates and times. I need the record ID to match in the different tables. So What I was thinking is I need to look at the table get the ID number and then somehow add 1 to it.

Assuming that your two tables have some information in common, then I would be inclined to make a query that joins them together and then extract your information from this query, not the tables separately.

Any hickups and you could have a right mess.

I cant combine the tables because then there would be to many fields.

to be honest it sounds like a normalisation probelm

you shouldnt need to add the same autonumber record id to to different tables - even if you need to do something like this, the pk of one should be an fk in the other