我需要调试SQL/access/VBA问题的帮助 [英] I need help debugging a SQL/access/VBA problem

问题描述

我已经从一个离开公司的人那里继承了ACCESS数据库.它具有很大的形式，具有大量的下拉列表和文本框控件.填写完毕后，用户单击按钮以将记录更新到外部数据库.

直到三周前，它都可以正常工作，但是现在对于大多数用户而言，会产生间歇性错误.问题在于表单上的最后两个字段并不总是更新.发生这种情况时，代码将生成3164:字段无法更新"错误消息，并且记录也未更新.现在大约有1,000条记录未正确更新.

在记录集中使用了一条SQL SELECT语句，然后该记录集成为了表单上所有控件值的存储位置.每个控件都有一个标记，其中存储有应使用控件值更新的字段名称(在记录集中).因此存在一个循环，类似于:

I have inherited an ACCESS database from someone who has since left the company. It has a big form, which has plenty of dropdown and textbox controls. After filling them out, the user clicks a button to update the record to an external database.

This worked perfectly until three weeks ago, but now creates intermittent errors for most of the users. The problem is that the last two fields on the form do not always update. When that happens, the code generates a 3164: "Field cannot be updated" error message and the record is not updated. There are now about 1,000 records that have not been updated correctly.

There is a SQL SELECT statement which is used in a recordset which then becomes a storage place for all of the values of the controls on the form. Each of the controls has a tag in which is stored the name of the field (in the recordset) that should be updated with the control''s value. So there is a loop, similar to:

rs.Edit
For Each ctl In Me.Controls
                rs(ctl.Tag).value = ctl.value
Next
rs.Update

(进行了一些检查，以确保我们仅使用正确类型的控件，由标记值标识的字段实际上在记录集(rs)中，并且我们之前没有使用完全相同的相同的值，但为清楚起见，我省略了所有内容)

我已经调试了发生该错误的模块，并且错误出现在rs(ctl.Tag).value = ctl.value行中.该代码位于一个函数中，该函数定义为公共子UpdateFields(ByVal rs作为Recordset)"

调用模块创建记录集，如下所示:

(There''s some checking to ensure that we only use the correct type of controls, that the field identified by the tag value is actually in the recordset (rs), and that we haven''t updated this record before with exactly the same values, but I left all that out for clarity)

I''ve debugged the module where it happens and the error is in the rs(ctl.Tag).value = ctl.value line. The code is in a Function which is defined as "Public Sub UpdateFields(ByVal rs As Recordset)"

The calling module created the recordset as follows:

UpdateFields getRecNumSaveRS(txtRecNum)

(其中getRecNumSaveRS是根据作为参数传递的记录号创建记录集的函数的名称)

我将代码修改为:

(where getRecNumSaveRS is the name of a function that creates the recordset based on the record number passed as a parameter)

I modified the code to :

Dim rs As Recordset
.
.
.
Set rs = getRecNumSaveRS(txtRecNum)
UpdateFields rs
rs.Close
Set rs = Nothing

...因为我知道不关闭和处置记录集可能会导致bug，但这似乎没有帮助.

我们小组中没有其他具有IT背景的人，所以我没有任何帮助.如果有人可以提供任何帮助，我可以在这里查看，或者您可能希望通过ian-dot-n-dot-dennis-at-healthnet-dot-com向我发送电子邮件

我尝试过的事情:

1)随处搜寻
2)添加断点以查找代码错误所在的位置(在rs.Update之前)
3)添加rs.Close并设置rs = Nothing
4)添加计时器以尝试重新运行rs(ctl.Tag).value = ctl.value行(再次删除，因为它们没有帮助)
5)检查是否使用了正确版本的应用程序

什么都行不通(麻烦的是它主要在我的机器上工作并且很难模仿)

... because I knew that not closing and disposing of the recordset could cause bugs, but it didn''t seem to help.

There aren''t any other people with an IT background in our group, so I don''t have any help. If anybody can offer any help, I can check back here, or you might like to email me at ian-dot-n-dot-dennis-at-healthnet-dot-com

What I have tried:

1) Googling everywhere
2) Adding breakpoints to find WHERE the code errors out (it is BEFORE the rs.Update)
3) Adding rs.Close and Set rs = Nothing
4) adding timers to try to re-run the rs(ctl.Tag).value = ctl.value line (taken out again because they didn''t help)
5) Checking that the correct version of the app was being used

Nothing works (trouble is it works mostly on my machine and is difficult to emulate)

推荐答案

它相当复杂且不常见，可能会做得更好/更简单的方法，但是您可以通过引用传递记录集开始:

It''s quite convoluted and uncommon, and might get done in a better/simpler way, but you could start by passing the recordset by reference:

Public Sub UpdateFields(ByRef rs As DAO.Recordset)

这篇关于我需要调试SQL/access/VBA问题的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！