Strange bug where one record sort of collapsed into another

[TaliaKlein]

So this is a weird one...

I have one client (autonumber key 6888) who seems to have disappeared. However the key is still present in related tables like address, phone number, etc.
Here's where is gets weirder, when I search for the key 6888, I am returned another client key 17116.
When I sort the table in the backend by key 17116 appears where 6888 should be.

Any ideas anyone? I need to know 2 things:
1. Can I get client 6888 back
2. How can I resolve this? Do I need to delete 17116 and make new records for each client and then manually relink them to their related records in other tables?

Many thanks,
Talia

[ItsMe]

Do you have an expression in the Format property of your Key field, maybe at the table level?

[TaliaKlein]

Do you have an expression in the Format property of your Key field, maybe at the table level?

It's the default Access 2007 autonumber

[NTC]

topic 1: a deleted key of one table, can continue to exist in other tables. these are called orphan records. if one does not have cascade deleting (either as the embedded option or as a custom designed routine) then a db will have orphan records. while this is a design no-no it often is not the end of the world these days of lots of cheap storage and in particular in dbs that don't have large quantities of records.

topic 2: you write: " I search for the key 6888, I am returned another client key 17116" - - well a database's search query doesn't make errors so the question would be on what criteria one is actually searching

topic 3: you write: " sort the table in the backend by key 17116 appears where 6888 should be" .... same answer as topic 2 unless you have a corrupted table, which I tend to doubt....what is the sort criteria

topic 4: one can use an Update Query to insert a value into an auto number field. It is a rare requirement but it could be done. Depending on how many tables one is discussing it could be simpler just to add a new record for the missing person - get a new key number - - - and then manually change those other tables' foreign key values to the new number.

[ItsMe]

It's the default Access 2007 autonumber

AutoNumber types also have a Format property.

[orange]

Talia,

Have you done a compact and repair? It seems, as NTC said, that you do not have referential integrity turned on with Cascade Deletes.
What are the actual tables involved? Can you tell us about the relationships involved?

[TaliaKlein]

Hi Orange,
I'm sorry it took so long to reply. Thank you for your interest.
I do not have Cascade deletes on. I didn't even know that was a thing. But I also don't write the relationships into the table formats, as per the instructions of some table guru some time past. In this instance, it is just one table with a very strange bug happening in the autonumber, which is set to the default setting.

[orange]

I think we need to see some data and code.
You can set up relationships via the Relationships window. But with a 1 table database that doesn't make a lot of sense.
Can you post your database in zip format? And give us some direction to the problem area?
Good luck.

[TaliaKlein]

Can you post your database in zip format?

I am working on creating a version of the table that does not contain as much proprietary info, but to get to the next step... I did a compact and repair on the backend and 6888 is no longer returning as 17116, which is good although 6888 is gone. However, I now have 2 17116, which should be impossible. Also, when I first open the table it sorts with one of the 17116's in the 6888 spot.

AccessForumVersion.zip

[orange]

It would be impossible if keyClient was the Primary Key on your table.
What exactly is tblDonations --there is a reference to it in your sub datasheets?

[TaliaKlein]

It's gives me a list of all the donations that that particular client has made. It's a good way to check that they haven't made any donations before we delete them.
I appreciate that it is impossible, but there it is!!

[ItsMe]

It is likely corruption. Perhaps you can create a new db and use an import process to bring the problem table over to the new blank db. Of course, create backups before starting the process.

With the table isolated in the new db, recreate both of the problem records by deleting them, compact and repair, and recreate them. You can paste info in or use a query. If you use a query, you could recreate the original key values buy typing info onto a second table where the PK is long integer. Use an append query to append to the first table.

[orange]

My point was that you do not have a PK on your table.

[ItsMe]

My point was that you do not have a PK on your table.

I did not look at the uploaded file to see, but I guess Talia could check the properties while in design view for us.

[TaliaKlein]

Okay. I think I will need to do that. I have just discovered that the Compact and Repair has caused problems all over my database. One of my most important, complex and hardest working forms is now getting an Error 2105 and won't open on a new record. The only way I have of solving this in the short run, without stopping my whole office from working, is to go back to the backend before I did the compact and repair. It sounds like I need to take the database apart and put it back together again. Is there anyway I can tell if there are more errors floating around?