Record has been changed error if data is changed by VBA

I have a MS Access (2016) database using linked tables to a MySQL database. In the access database I have a form I use for data entry. I needed certain fields to be recalculated (manually) when I click a Recalc button.

The problem I am having is that when I run the VBA code to update fields on the form, if I then try to navigate to another record I get the error "This record has been changed by another user since you started editing it...."

I am the only user accessing this database. Everything works fine if I DON'T update a bound field on the form. Once I do, then I get that error when navigating to the next record.

Here is my vba code for the Recalc button:

Private Sub Recalculate()
    vendorID = Me.product_supplier_id
    supplierID = "supplier_id=" & vendorID

    supplierHandling = Me.product_handling
    vendorFee = Me.product_vendor_fee
    supplierMarkupPercent = DLookup("supplier_markup_percent", "suppliers", supplierID)
    supplierMarkupFixed = DLookup("supplier_markup_fixed", "suppliers", supplierID)

    productCost = Me.product_cost
    productShipping = Me.product_shipping
    totalCost = productCost + productShipping + supplierHandling
    totalCost = totalCost + vendorFee
    markup = supplierMarkupFixed + (totalCost * supplierMarkupPercent)
    productPrice = (totalCost + markup) / 0.85
    amzFee = productPrice * 0.15
    totalCost = totalCost + amzFee
    profit = productPrice - totalCost

    Me.product_total_cost = totalCost
    Me.product_price = productPrice
    Me.product_profit = profit

    SetPriceColor
End Sub

The 3 statements near the end (before the SetPriceColor) are the culprits.

I am not sure how to resolve this issue. I have combed through many google searches, but nothing jumps out at me a solution for this specific case.

Solution

Yes, the issue is due to linked ODBC tables. Plus floating point number columns, which can cause problems when Access checks whether your changes in the bound form (be it by VBA or manually) conflict with the previous version of the saved record.

The solution should be to add a TIMESTAMP column with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP to your table.

From here:

ALTER TABLE myTable
ADD COLUMN updated_at 
  TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
  ON UPDATE CURRENT_TIMESTAMP;

See these questions:

Write Conflict messages suddenly start happening in ODBC linked tables

Does MySQL have an equivalent of SQL Server rowversion?

For tables linked from SQL Server, adding a ROWVERSION column definitely fixes the issue. For MySql (and its ODBC driver) it should work, and it did work here.