Can't duplicate record

  • Thread starter Thread starter hubbiida via AccessMonster.com
  • Start date Start date
H

hubbiida via AccessMonster.com

Hi,
I have a created a "duplicate" button on a form in Access 2003. Two fields on
the form are set as "duplicates not allowed" at the table level: one is a
standard field and one is an auto-number field (the key field for the primary
table of the underlying query). If I duplicate a record and attempt to go to
the next or previous record forgetting to change the value in the no-dupes
field first, I get the standard Access error. The funny thing is that if I
then change the standard no-dupes field (the other, again, being auto-number
field), I still get the error. As a result of this, I must exit the form, at
which point I get a message saying my changes will not be saved.

But, here is the really odd part: if I return to the form and successfully
duplicate a record after first erroring out, I find that the auto-number
field has jumped one ahead in the numeric sequence, although no new record
has been created. In other words, if the highest number in that field before
the error was 100, after the error the next number assigned to a new record
will be 102, not 101, as though there is a ghost record...

Any ideas?

(PS: If I do not forget to update the standard no-dupes field before
attempting to go to another record, there is no problem.)
 
If you can see the AutoNumber field on your form, you will notice that
Access assigns a value to this field as soon as you *start* to add a new
record. Once the autonum has been assigned, it is never reused. That means
that any time you abort the entry of a new record, that autonum will never
be assigned (unless you immediately compact the database, which resets the
number again.)

Remember that Access is a multi-user program. For example, Fred might an
autonumber and starts adding a record. Before he is finished his entry, Jo
starts entering another record, so she gets the next autonum. Now, if Fred
aborts his entry, what do you expect Access to do? It cannot allow the
number previously assigned to Fred to be reused, because it has already
issued a higher one. Hope that makes sense to you.

It is possible to duplicate a record by assigning just the fields you want
to set, instead of the copy'n'paste approach the wizard creates for you.
Typically, you set the form's RecordsetClone's Bookmark to the current
record, move the form to a new record, and then selectively copy the fields
you want duplicated from the RecordsetClone, which is still pointing to the
record you wished to copy from.
 
Thank You Allen,
Everything you say makes perfect sense. Subsequent to my original post I have
discovered that changing the no-dupes field from a memo field to a text field
has solved the problem of the records not duplicating. Does this make any
sense at all?
Thanks again,
Andrew

Allen said:
If you can see the AutoNumber field on your form, you will notice that
Access assigns a value to this field as soon as you *start* to add a new
record. Once the autonum has been assigned, it is never reused. That means
that any time you abort the entry of a new record, that autonum will never
be assigned (unless you immediately compact the database, which resets the
number again.)

Remember that Access is a multi-user program. For example, Fred might an
autonumber and starts adding a record. Before he is finished his entry, Jo
starts entering another record, so she gets the next autonum. Now, if Fred
aborts his entry, what do you expect Access to do? It cannot allow the
number previously assigned to Fred to be reused, because it has already
issued a higher one. Hope that makes sense to you.

It is possible to duplicate a record by assigning just the fields you want
to set, instead of the copy'n'paste approach the wizard creates for you.
Typically, you set the form's RecordsetClone's Bookmark to the current
record, move the form to a new record, and then selectively copy the fields
you want duplicated from the RecordsetClone, which is still pointing to the
record you wished to copy from.
I have a created a "duplicate" button on a form in Access 2003. Two fields
on
Click to expand...
[quoted text clipped - 24 lines]
(PS: If I do not forget to update the standard no-dupes field before
attempting to go to another record, there is no problem.)
Click to expand...
Click to expand...
 
Back
Top