如果 VBA 更改了数据，则记录已更改错误 [英] Record has been changed error if data is changed by VBA

问题描述

我有一个 MS Access (2016) 数据库，使用链接表到 MySQL 数据库.在访问数据库中，我有一个用于数据输入的表单.我需要在单击重新计算"按钮时(手动)重新计算某些字段.

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.

我遇到的问题是，当我运行 VBA 代码来更新表单上的字段时，如果我然后尝试导航到另一条记录，我会收到错误消息自从您开始编辑该记录以来，该记录已被其他用户更改……"

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.

这是我的重新计算按钮的 vba 代码:

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

接近结尾的 3 个语句(在 SetPriceColor 之前)是罪魁祸首.

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.

推荐答案

是的，问题是由链接的 ODBC 表引起的.加上浮点数列，当 Access 检查绑定表单中的更改(无论是通过 VBA 还是手动)与之前版本的保存记录冲突时，这可能会导致问题.

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.

解决方案应该是在您的表中添加一个带有 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 的 TIMESTAMP 列.

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

来自这里:

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

查看这些问题:

在 ODBC 链接中突然开始发生写入冲突消息表格

MySQL 是否具有等效的 SQL Server rowversion?

对于从 SQL Server 链接的表，添加 ROWVERSION 列肯定可以解决问题.对于 MySql(及其 ODBC 驱动程序)，它应该可以工作，而且在这里确实可以工作.

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.

这篇关于如果 VBA 更改了数据，则记录已更改错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！