Access sometimes jumps to existing record on save new record - Access2k FE/SQL2005 BE

A client is reporting occasional similar problems. It started immediately after they started using merge replication.

I've informed several contacts within the Microsoft Access product group as well as my fellow Access and SQL Server MVPs.

Please email me your email address so I can forward that to my contacts at Microsoft as I would assume they would want to contact you directly. tony at granite.ab.ca

BTW excellent trouble shooting and detailed problem description.

It definitely sounds like a record locking issue. Are you using autonumbers as PK? Have you tried 2 computers adding a record on the same form at the same time (meaning one of them will fire the insert event while the other has added a new record on the form one but is still editing it)?

Could you check in a way or another if the PK of the inserted record after insertion in the table stays similar to the PK given before the insertion (by adding for example a few 'debug.print's to your code)?

A scenario could be 2 pending inserts been given by the machine the same PK, the second one being then automatically changed at insert time, resulting in your form loosing the 'active' record.

I'm wondering about the scenario where you are using a form to add records that has any other records for the user to jump to.

That is, I don't believe in using the same form to edit records as is used to create them.

Instead, I use an unbound dialog to collect all the required fields, insert the record in SQL, then open the main editing form to that single record (not a form with the whole table navigated to the record that was just added).

Keep in mind that in a main form/subform scenario, creating a record in the subform when the parent form is unsaved causes the parent record to be saved. You might want to check if there is any code in the Insert and Update events of the main form that would cause a requery of the main form on the insert of a new record (triggered by editing the subform).

But I would still suggest that the best architecture is to avoid this kind of possible scenario by loading only single records, so there is no other record to jump to. That would certainly limit the possibilities of where the user could end up when the problem occurs.