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

Question

I am really posting this out of desperation after searching around a lot for an answer and trying a few different things with no success.

I have an Access database where I have recently migrated the tables to SQL 2005, Access continues to function to the users as a front-end providing forms, reports, and queries.

However, since moving to the Access FE/SQL BE setup, the users have been reporting that sometimes, when they are entering a new record, they click into a subform (saving the record) or click save on the menu itself, it jumps to an existing record. The new record has been saved, but for some reason access switches to a different record as it refreshes. The user then has to close out, find the saved record, and continue editing it.

Scenario: A user is entering a quote and fills out all the quote details, customer, date, etc, then clicks in the line-items subform to add a product (or clicks save in the menu), and suddenly the quote form (and line-item subform) is showing the details of some random quote. The random quote could be recent, or from years ago, and has nothing in common with the quote they were entering.

This weird behavior only happens on inserting a new record, never on editing an existing record. Users tell me that it happens 'more often' when they go to add a new (quote, customer, whatever) after opening the database.

I have noticed it is only happening on forms that have subforms, so my first thought is that it had to do with Access sending through the subform data before the form data is saved, causing a PK violation. But this doesn't appear to be happening: there are no errors on the SQL server, and the record is successfully saved. Forcing the users to save the main form record before adding subform records (i.e. on a quote, forcing them to save the quote before they can add line items) didn't work, it just causes the jump (sometimes) on the save.

It isn't vba running on the save or on current, I have set breakpoints on all the event handlers as it jumps and no vba is being executed. Some of the 'jumping' forms have no vba on the form. But all have subforms. I suspect it has to do with record locking.

The server running the tables is SQL Server 2005, the users are using a mix of Access 2000 and 2003, mostly XP SP3 with a couple of old Win2k boxes. They are using Merge replication and a couple of users are running replicated SSEE2005 editions and subscribing to the main server. Most users are not replicated, just connecting directly to the server via ODBC or SQL native client connections. But I have verified that this is happening to all users, usually once or twice a day, and it has happened to me before. So it isn't a user issue.

The worst part about this behavior is that it only happens some of the time and I haven't managed to find a scenario that will always cause it to happen.

If anyone has experienced anything like this before, please let me know how you sorted it out, or even suggestions would be welcome.

Update: (1/10/09) Problem solved, thanks to David Fenton. Setting the form to Data Entry mode (Form.DataEntry = true) before opening it to add records does indeed prevent the jumping. Client reports no issues at all since I changed this a week ago.

Answer 1

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.