I need help debugging a SQL/access/VBA problem

See more:

VBA

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

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

(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

... 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)