By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,317 Members | 1,339 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,317 IT Pros & Developers. It's quick & easy.

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

P: 1
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.
Mar 25 '14 #1
Share this Question
Share on Google+
4 Replies

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Mar 25 '14 #2

Expert Mod 5K+
P: 5,397
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.
Mar 25 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
@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.
Mar 25 '14 #4

Expert Mod 15k+
P: 31,768
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.
Mar 26 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.