duplicate record (1 Viewer)

kitty77

Registered User.
Local time
Today, 11:39
Joined
May 27, 2019
Messages
693
I have a form that is based on a query. When I try and use a command button (with wizard) to duplicate a record, I get an error.
Will create a duplicate record in the index, etc... and you can not save the record.

When I open the query directly, I can copy the record and paste the record, over and over.

What is going on here...

Thanks.
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
Is one of the controls on the form bound to an AutoNumber column?
 

kitty77

Registered User.
Local time
Today, 11:39
Joined
May 27, 2019
Messages
693
Probably... I have lots of code doing lots of things. Any way to pin point what you are talking about?
So I can find out what code and field and or value
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
Any way to pin point what you are talking about
You mean to find whether or not you have a form control bound to an AutoNumber column?? Well you'd have to examine your form properties and dropdown lists (which lists all controls on the form), check out each control's Controlsource, if you don't remember, and evaluate whether or not its bound to an Autonumber column...

I don't usually use any of the built in Wizards to generate command button code, but I'm just thinking of it from this perspective:
- If your command button tries to duplicate EVERY field on the bound form to a 'new' record, and if one of those fields is bound to an Autonumber column, then that should err out, because you can't explicitly duplicate an Autonumber column (that being the point of Autonumber columns).
- Whereas, Access is smart enough (I use that term loosely), to handle things differently when you Select a record in a datasheet view table, Copy, and Paste it. It doesn't really Paste everything...it actually increments the Autonumber.

See what I mean?

This may not be your scenario - I was just taking a stab at it. What's the code behind the duplicate record command button?
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
Edit ... I actually just created a table with an Autonumber field, and a form with all table's elements on it, and a button using the wizard > duplicate record functionality, and it does not cause that error. So probably that's not the problem.

Can you post a sample database?
 

Cronk

Registered User.
Local time
Tomorrow, 02:39
Joined
Jul 4, 2013
Messages
2,770
Remove the Index from the table
That's a bit drastic, no? Especially if it's a key field.

@kitty77, if the autonumber is a key index, you cannot duplicate it. Nor can you duplicate any field that has a unique index. That's the purpose of having a unique index, not to have duplicate values. You can duplicate the value of any other field in a new record.
 

Isaac

Lifelong Learner
Local time
Today, 08:39
Joined
Mar 14, 2017
Messages
8,738
Cronk, UG, maybe I misinterpreted this post - but I think what OP is asking is, why would it let me duplicate a record in a query datasheet view but not in the code/form scenario. Would these two things not invoke the same index violation rule

I might be wrong. Will step back and let OP clarify.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 15:39
Joined
Jul 9, 2003
Messages
16,245
Nor can you duplicate any field that has a unique index.

Exactly, if you need to have duplicate values then you can't have an index that excludes them, hence remove it...
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:39
Joined
Sep 21, 2011
Messages
14,056
Exactly, if you need to have duplicate values then you can't have an index that excludes them, hence remove it...
If you need the index, why not just accept duplicate values.?
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 15:39
Joined
Jan 14, 2017
Messages
18,186
As Gasman stated, you can still allow duplicates without removing the index:

1595277205211.png
 

Users who are viewing this thread

Top Bottom