CODEWITHSUNDEEP
sql-serverms-accessvbaodbc
Rominus
Rominus

Reputation: 1221

Preventing 'The data has been changed' error when using Me.Refresh with ODBC linked tables

In my Access application's data entry form I'm using Me.Refresh to show the user the new value of a calculated field based on data they entered in a non-calculated field. The purpose is to give them the deadline for doing a job next.

This Access app is using linked SQL Server 2012 tables via ODBC. The calculated field is in the underlying SQL Server table, not part of the Access Record Source query because I want to store the calculated value in the actual data, not just as an interface element.

The calculation is very simple:

nextjob = jobdate + 79

So I have the field for jobdate set to run Me.Refresh after update. All well and good.

The problem is that if the user updates jobdate, triggers the refresh by moving to another field, then returns to the jobdate field and changes the date they entered Access throws a "The data has been changed by another user" error.

I tested the method using native Access tables and the problem does not occur. However the data needs to stay on the server, so moving to native tables is not a solution for me.

Upvotes: 0

Views: 1466

Answers (1)

Andre
Andre

Reputation: 27634

There are several possible solutions.

1- If it's always jobdate + 79, don't store it at all, use a view that has the calculated field.

2- Use Me.Requery instead of Me.Refresh. If the form shows multiple records, you must navigate back to the current record, you can use Me.Bookmark for that.

3- Move the calculation into the Access frontend - make nextjob an ordinary column and set it in the form, so it isn't another user (the server) that updates the data.

Upvotes: 1

Related Questions