access to oldValue for control in continous form generates error 3251 in beforeUpdate when a checkbox on form is updated

Question

This is one of the stranger issues I have seen in MS Access. I have the following code in a continuous form:

Private Sub thisForm_BeforeUpdate(Cancel As Integer)
If Not Cancel Then
    Debug.Print "pre-logging data changes..."

    ' here we need to doublecheck to see if any values changed.
    ' we simply iterate through the whole list, re-setting oldValue
    ' and newValue.
    For Each control In thisForm.Section(acDetail).controls
        If control.ControlType = acTextBox Or _
           control.ControlType = acComboBox Or _
           control.ControlType = acListBox Or _
           control.ControlType = acOptionGroup Or _
           control.ControlType = acCheckBox Then
            Debug.Print control.Name
            oldValues(control.Name) = control.oldValue
            newValues(control.Name) = control.value
        End If
    Next
End If
End Sub

oldValues and newValues are Dictionary objects (although likely not related to the issue).

My form has 3 textbox controls, and a checkbox control. One of the text box controls is disabled, and is populated via the results of a simple inner join (to get the human readable name associated with a foreign key). The data source comes from the form's recordsource (no DLookup or anything is used).

If I edit one of the other two textbox controls, this code runs absolutely fine. HOWEVER, if I toggle the checkbox on the form, i get a runtime error 3251. In the watches window, I get the error again when i try to view the properties of "control". It shows the value of oldValue for the disabled control to be "Reserved Error".

If it did this consistently, I would think it was due to the control being disabled; but since it works without a problem when the other textboxes receive edits, and only breaks when the checkbox is toggled; I am stumped. I'm almost inclined to believe I found a bug in access, but I could use some extra input.

Anyone else every encounter an issue like this?

EDIT: Upon digging further, I found that in actuality only one of the 3 editable fields will not trigger this error. It holds string data. The other two controls hold a date value, and a yes/no value. Now I am even more confused.

Gaul

Check to see if the field causing the issue is a hyperlink or has an OnUpdate event associated to it. – Flameout

Answer 1

i've got two ideas to that issue.

First one: If the RecordSource of your Form is an ODBC-Table thats linked to a SQL-Server then you should set a standard value for the CheckBox-Column. Otherwise it will try to set NULL to False and throw an error saying that somebody else edited the current record.

Second idea: Sometimes Access just has a little "hiccup" when it compiles the code. You could make a backup of your database and then try to decompile it using "C:\Program Files\Microsoft Office 2007\Office12\MSACCESS.EXE" "C:\yourFolder\yourDatabase.accdb" /decompile in the Run... Window (of course you have to insert the Path as it is on your machine). That often helps solving strange Problems.

Gunter