Duplicate records won't duplicate

[Pezzini]

Hi, I've been asked to give users the ability to copy a record to a new record - sounds easy enough I know, and I've definitely done this before. The fact that I have to actually duplicate information in four different tables doesn't even phase me, but I am rather struggling with the fact that I don't seem to be able to duplicate the records in the main table.

I've writen the SQL using INSERT INTO (as I have before), but it won't work. After bashing head on wall with the problem for about two hours, I decided that I'd just give them the facility to copy the main table only for the moment as a stop gap and figure the rest out after. So I went to my test version and put on a button using the wizard to select [Record Operations] and [Duplicate record], simple huh? I thought so, only when I try it, the record won't duplicate, it won't give the next projectid (which is an autonumber). Anyone got any ideas why it would do this and how I overcome it?

Thanks.:banghead:

 

[AccessBlaster]

Maybe this could help. http://allenbrowne.com/ser-57.html

 

[Pezzini]

Looks interesting, will give that a go. Thanks.

 

[Pezzini]

Followed exactly, but still get error 3022 (duplicate values). It seems to try to set the autonumber instead of letting the table set that. Weirdly the number it tries to set seems to be a count of the number of times I've run (tried to run) the duplicate function.

 

[AccessBlaster]

Is it possible to post your DB, minus sensitive records.

 

[Pezzini]

Wouldn't have the vaguest idea how to post the DB - However I solved the problem late last night. It's a bit Heath Robinson but it works.

What I've done is created an append query that takes the maximum of the existing project numbers and adds one to it to insert that as the ID. Now it's a non-duplicate ID, it accepts it and it all works. I can see potential conflict in this, but as the inputing is limited to three people, the need to duplicate is less than 1% of the input and this is only a proof of concept db, this version will do for now.

Which means I may have to return to the issue later, but for now it's sufficient.

Thanks to everyone who helped.

 

[Minty]

If you are able to append a record with a specific ID then your the Autonumber is NOT an Autonumber field. I would check the table names and filed names you are using.

 

[Pezzini]

Minty - Logically I agree with you, only the datatype is set to autonumber and it autopopulates with the next number when a new record is added.