MS Access has created duplicates

[dotolee]

Hi there.
I'm noticing that my access database has created duplicate records, including the PK. I am running this on a network with multiple users. I tried to delete the duplicate record but it "seems" to create more errors. For example, in the spot where the deleted record used to be, i get #### everywhere (after a compact database) or #Deleted# in all fields.
Also, for some reason, after i delete this record, the system doesn't recognize previously recogized values in the table and puts "error" everywhere. When I try to click on a field with "error" in it, a messagebox appears that states there is no error message assigned to this error number.
I remember reading somewhere here an FAQ about using your own Autonumber... can someone help me find that article and also confirm that this is what could be the problem?

Please and thanks.

 

[dotolee]

I found this article:

http://allenbrowne.com/ser-40.html

and i've been reading it over. none of the items apply to me except I'm having all users check their version of JET.
If anyone else has other suggestions, I'd really appreciate it. I also need to know how to "properly" delete the record. It has a related child record on another table (which has not been duplicated)

Oh btw. Whenever I compact my back end database, do I have to reseed the autonumber on my table?

 

[JoeAtWork]

Sounds like a pretty corrupt database. A decompile might help.

 

[dotolee]

sorry, what do you mean when you say decompile?

 

[dotolee]

The errors only occur when I attempt to delete the duplicate record. I know this because I took a backup before deleting. Once I delete the record, the system starts giving the error messages.

 

[JoeAtWork]

Example of decompiling a database, run the following command (of course, change file paths as appropriate)

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile "C:\MyBadDB.mdb"

This sometimes fixes corruption that Compact & Repair is unable to. If it does fix it, I would recommend creating a new database and importing all the tables from the old one. Starting with a clean new database may help purge the bug.

 

[dotolee]

ok. I'll give that a try on Friday... next time I'm in.
Thanks.

 

[TheAceMan1]

How are ya dotolee . . .

I believe the FAQ your looking for takes residence here faq700-184

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[dotolee]

Thanks theAceMan1. I will read all those posts.
Sorry for asking such silly questions.
But I do appreciate the responses from everyone.

 

[dotolee]

So here's what I've discovered so far, just in case anyone is interested.
When you have duplicated records, and you delete one, MS Access puts "#DELETED#" in all the fields for the record that's left. So the proper way to handle duplicates is to delete both, and reenter. This kinda stinks if you are using the autonumber somewhere else in your business process because after reentering the record, you'll have a new number assigned.

 

[dotolee]

After interviewing my end users, it appears that MS Access created this duplicate when the user tried to edit an existing record. No errors were displayed but after the editing was completed, she noticed that it was duplicated.