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 this has worked great for a few years now. Earlier this week we noticed that we had some duplicate Contact_ID’s (about 160 of them), which I would have thought to be impossible. No idea how that happened, but it seemed to start last week. I needed to correct this problem, obviously, so I changed the autonumber field to be a number field (which I sincerely hope wasn’t a huge mistake!) so that I could edit the values and assign unique ID’s to each record. I naively thought I could fix the problematic ID’s and then change the field back to be an autonumber – apparently not.
I then added the DMAX+1 function to my forms so that when someone adds a new contact, they get assigned a Contact_ID. I thought my problem was solved – until today when I needed to import a few hundred contacts by doing an append query (and I have a need to do this about once a month). I can’t leave my PK empty, so I thought a workaround would be to put a 0 or 1 in the Contact_ID field, and then run a secondary query that would change the value in that field to be the DMAX+1 result – similar to what is described in this related article:
http://bytes.com/topic/access/answers/694370-inserting-new-unique-sequence-numbers-without-autoseq
But that doesn’t work, either. When I attempt to run the query, I get the error that “Access set 0 fields to Null due to type conversion failure and didn’t add the records to the table due to key violations”.
I’m really wishing I still had my autonumber field, but I’m not seeing any way to get that back. I know I could copy the table and reimport the records back in to a table with the autonumber field, but that won’t work because I definitely have some skipped ID’s (eg, someone started adding a record and decided not to, so the record was just the ID number – I’ve periodically deleted these records from the table). I can’t assign new ID numbers to each contact because these ID’s are tied to mailing lists and other tables.
Could anyone give me advice with this? Either a way to make my append query work with assigning sequential numbers in the Contact_ID field, or a way to recreate an autonumber field and match things up the way they are?
Thanks a million for any recommendations!