Error 3251 on .oldValue control property

Question

I am currently working on adding an audit trail to a MS-Access 2010 database and I am struggling with

"error 3251 : operation is not supported for this type object"

Here is the code of my audit trail module, mostly arranged code coming from web :

Public Function auditChanges(RecordID As String, userAction As String, cForm As Form)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim ctl As Control
    Dim userLogin As String

    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT * FROM T_AUDIT")
    userLogin = getCurrentUser

    Select Case userAction
        Case "New"
            With rst
                .AddNew
                ![Date] = Now()
                ![utilisateur] = userLogin
                ![nomFormulaire] = cForm.Name
                ![Action] = userAction
                ![RecordID] = cForm.Controls(RecordID).Value
                .Update
            End With
        Case "Delete"
            With rst
                .AddNew
                ![Date] = Now()
                ![utilisateur] = userLogin
                ![nomFormulaire] = cForm.Name
                ![Action] = userAction
                ![RecordID] = cForm.Controls(RecordID).Value
                .Update
            End With
        Case "Edit"
            For Each ctl In cForm.Controls
                If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) Or (ctl.ControlType = acCheckBox) Then
                    If (Nz(ctl.Value, "") <> Nz(ctl.OldValue, "")) Then
                        With rst
                            .AddNew
                            ![Date] = Now()
                            ![utilisateur] = userLogin
                            ![nomFormulaire] = cForm.Name
                            ![Action] = userAction
                            ![RecordID] = cForm.Controls(RecordID).Value
                            ![champs] = ctl.ControlSource
                            ![ancienneValeur] = ctl.OldValue
                            ![nouvelleValeur] = ctl.Value
                            .Update
                        End With
                    End If
                End If
            Next ctl
    End Select
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
End Function

This function is called in beforeUpdate event of the forms I want to track.

The error is fired when I try to edit a bound textbox. And the line If (Nz(ctl.Value, "") <> Nz(ctl.OldValue, "")) Then is the line provoking the error

The form is based on 2 tables linked with a One-To-Many relationship. The function is working when I edit fields from the table which are sided to the "One" part of the relationship but it throws the error when I want to edit fields from the "Many" side.

I hope I am clear enough, thank you

Edit : More details

My form is based on that request :

SELECT T_REVISION.ID_revision, T_REVISION.fk_ID_proposition, T_REVISION.numero, T_REVISION.fk_etat_revision, T_REVISION.EOTP, T_PROPOSITION.reference_simple, T_PROPOSITION.libelle, T_REVISION.description_localisation
FROM T_PROPOSITION INNER JOIN T_REVISION ON T_PROPOSITION.ID_proposition = T_REVISION.fk_ID_proposition
ORDER BY T_REVISION.numero DESC;

The error is fired from T_PROPOSITION.reference_simple control. The error 3251 occurs when : I try to edit T_REVISION.EOTP, T_REVISION.description_localisation fields. The error 3251 does not occur when I edit T_PROPOSITION.reference_simple, T_PROPOSITION.libelle !

So : I'am able to edit values coming from the "One" side of the relation but when I want to edit the "Many" side it seems I can't access the oldValue property

How can I solve this ?

Answer 1

Not exactly an answer, but the comment area is not suitable...

If would add 2 lines BEFORE the If (Nz(ctl.Value, "") <> Nz(ctl.OldValue, "")) Then :

debug.print ctl.Name, ctl.value
debug.print ctl.name, ctl.oldvalue

This will allow you to see if the error is linked to a specific control and to a specific property, and narrow your search.

---

Edit: After you edited your OP indicating that the issue arises on the "many" side of you join, I think you should change your form to a "main form - subform" architecture. This will allow you to track updates to each TABLE correctly.

Answer 2

It's hard to tell exactly what's causing the error when you don't specify the line that's popping up the error, but there is one obvious possibility.

You're trapping for Null in your control loop

If (Nz(ctl.Value, "") <> Nz(ctl.OldValue, "")) Then

And more importantly you're missing a front parentheses as " ("

But then you don't trap for that possibility when assigning the control's value and old value to your recordset.

![ancienneValeur] = ctl.OldValue
![nouvelleValeur] = ctl.Value

Do your fields ancienneValeur and nouvelleValeur allow for null or zero length fields to be assigned to them?

In any case you should be consistent and make sure you trap for null values before assigning them to your table's fields.

![ancienneValeur] = NZ(ctl.OldValue,"")
![nouvelleValeur] = NZ(ctl.Value,"")