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 (so