How to avoid incrementing the autonumber field if duplicate record is enter in access

[Arv1nn]

Is there a way to check the field in access for duplicate record to avoid the autonumber field to increment?.. Basically I need a code to check if the entered value in a field already exists and if there is duplication it should not increase the autonumber field index.

[Seth Schrock]

There are a couple of ways to do this. First you could have your form unbound, check for the duplicate record, and then write to the table if a duplicate isn't found. The second method involves using a temp table and have your form bound to it. Either way, it involves manually (through code) entering the data into the final table. The first method is almost impossible if you have multiple related records when you make your check. It does make the copy more complicated, but you would really need to use a temp table(s) in this situation.

[zmbd]

Seth is correct in that the form will have to be unbound.

As soon as you start data entry into the record the autonumber increments.

As far as being almost "impossible " for multiple related records... I wouldn't go that far. It requires that your database be logically designed, properly normalize, and the data entry copied to the correc tables in the correct order, I do this quite often.


Ideally, one should not use the autonumber for any meaningfull application, i.e. serial numbers, check numbers, etc... where a serial sequence is to be maintained. The intent of the autonumber is to provide a unique record id for use as a primary key. If you will search this site there are several methods for developing and maintaining sequential numbering.

[Seth Schrock]

@Z I would love to hear how you have multiple related records with everything unbound. You can PM me if you don't feel it fits in this thread.

[NeoPa]

FYI: It is possible to reset the AutoNumber sequence in two ways :

1. Compact and Repair the database. Afterwards the sequence will be set to the first number after the highest currently found in the table.
2. APPEND a record that includes a value for the AutoNumber field (It is possible - to allow for restoring backups). Afterwards the sequence will be set to the first number after the value added.