473,326 Members | 2,168 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
4 1218
Seth Schrock
2,965 Expert 2GB
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
zmbd
5,501 Expert Mod 4TB
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
2,965 Expert 2GB
@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
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

0
by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is...
8
by: Mark | last post by:
When my form goes to a new record, I have a procedure that copies the last record added to the form's underlying table into the form. The intent is that a series of new records may have the same...
1
by: S. van Beek | last post by:
Dear reader, By append a new record to a table I always expect the next higher value in the range of the autonumber field. But sometimes if I have deleted same records from a table the...
5
by: marko | last post by:
I would like my autonumber field to start at 100000. How can i do that?
6
by: HS1 | last post by:
Hello I have a table in Access Database. This table has a AutoNumber field. I use a DataGrid to show that table When I insert a new record in for this table using a DataGrid, there is a...
1
by: gtwannabe | last post by:
I'm having a problem with a form that uses AutoNumber as the primary key. I have an Abort button to delete the current record and close the form. If AutoNumber is assigned, the code executes a...
1
by: BMF | last post by:
Howdy, I am mainly a MySQL user but I have been playing with Access 07 a little bit recently. I created a database with 3 tables. Table ID --- userID -- AutoNumber UserInfo -...
3
by: Katie Howard | last post by:
Hi, I’m desperately looking for some help… I maintain a Contacts database which has about 27,000 records. It was initially set up for the field Contact_ID to be an autonumber (also my PK), and...
2
by: yaozaah04 | last post by:
Hello, I am working on a data entry form using Microsoft Access 2010 and I am supposed to give the user a separate option to look for a duplicate record. However the duplicate record-checking works...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.