Control Value Comparison

[CementCarver]

I'm using some audit code from Martin Greene's webpage and I'm having some difficult with a control that does not allow me to compare the control value and it's oldvalue. The code is below:


Dim cnn As ADODB.Connection
Dim rstAudit As ADODB.Recordset
Dim ctl As Control
Dim datTimeCheck As Date
'Dim strUserID As String
Dim MaxMember_id As Integer

Set cnn = CurrentProject.Connection
Set rstAudit = New ADODB.Recordset


rstAudit.Open "Select * from Audit_Trail", cnn, adOpenDynamic, adLockOptimistic
datTimeCheck = Now()

Select Case UserAction
'existing records
Case "Edit"
For Each ctl In Screen.ActiveForm.Controls
If ctl.Tag = "Audit" Then
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then
'Debug.Print "Old Value is :" & ctl.OldValue
'
Debug.Print "Control Name = " & ctl.Name
Debug.Print "Value is = " & ctl.Value

If Nz(ctl.Value) <> Nz(ctl.OldValue) Then... when the checkbox control in question hits this line, I get the error...."Operations is not supported for this type of object..."

But I use the same control in another form, which runs through this same audit code and it does not complain at all when the code processes the controls of that form. So, why does this control fail at this line now?
With rstAudit
.AddNew
![DateTime] = datTimeCheck
![UserName] = strAccountName
![FormName] = Screen.ActiveForm.Name
![Action] = UserAction
![Record_ID] = MaxMember_id
![FieldName] = ctl.ControlSource
![OldValue] = ctl.OldValue
![NewValue] = ctl.Value
.Update
'Debug.Print "Current values " & UserAction
End With
End If

End If
End If
Next ctl

CementCarver

[pbaldy]

Make sure you've tagged the checkbox and not its label.

[CementCarver]

Pbaldy, can you please define "tagged"? When in a design session, it's not the label, but the small box containing the actual check mark that contains the controls.

In the properties window, it's deemed as a checkbox. Plus the line in the code: If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acCheckBox Then, only tests for textboxes, comboboxes and checkboxes.

CementCarver

[CementCarver]

When I use the debug.print statement for the controls on this form, when it gets to this checkbox control, it's the ctl.oldvalue that is causing the error:

Debug.Print "Control Name = " & ctl.Name
Debug.Print "Value is = " & ctl.Value
Debug.Print "Old Value is = " & ctl.OldValue

I get the error run-time '3251' Operations is not supported for this type of object. But when I'm typing in the line & ctl. when I key in the period, I can select the oldvalue selection.

CementCarver

[CementCarver]

I found this old article based on Access 2000 (This article was previously published under Q207836) Error "Operation is not supported" with old value property:

Your form is based on an AutoLookup query that is based on more than one table, and there is a one-to-many relationship between two of the tables.

When you change the data in one field of a record, the Microsoft Jet database engine saves the entire record, instead of saving only the field that you modified. When you change the data in any field on the "many" side of the relationship, the data in the foreign key field is also saved again. The Microsoft Jet database engine must then requery the fields from the "one" side of the relationship; this ensures that they contain the data corresponding to the value that you just saved in the foreign key field. Once the Microsoft Jet database engine has requeried the field, trying to access the value of the OldValue property of a control bound to that field results in a run-time error because, at this point, the OldValue property is no longer valid.

But I don't understand the work around on how to create a recordclone.....Use the RecordsetClone property of the form to retrieve a control's previous value. In the following example, the OnCurrent property of a form is set to an event procedure that creates a recordset; the recordset is a copy of the form's underlying record source. The procedure uses the Bookmark property of the recordset to find the record that corresponds to the current record on the form.

CementCarver

[pbaldy]

Sorry, I didn't read well enough. I think I understand conceptually what they're doing. Basically, the recordset provides the "old value" for you. I haven't run into this problem, but I do something similar when I use an unbound form and need an audit trail (I use a local table to store the record).

[CementCarver]

PBaldy, I don't understand when you said...."I use a local table to store the record"

CementCarver

[pbaldy]

Sorry, that was probably a needless distraction since it involved unbound forms. Have you tried to implement their recordset solution?

[CementCarver]

I'm still researching how the cloned recordset is going to help me with my ctl.value <> ctl.oldvalue. And I really don't quite understand the concepts around RecordsetClone property of the form. I'm stuck.... again

CC

[CementCarver]

Not sure what this does on the form's Oncurrent event.


Private Sub Form_Current() Set rs = Me.RecordsetClone rs.Bookmark = Me.Bookmark End SubCC

[CementCarver]

So, I've placed the code below on the form's OnCurrent event:

Private Sub Form_Current()
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark

End Sub

So, my question is, if my understanding is correct, then if I've changed a record in the form, how do I point the audit trail code to use the cloned record, and not the one that's current in the form?

CC

[pbaldy]

Your test changes to:

If Nz(ctl.Value) <> Nz(RecordsetValue) Then

though you'd have to get the field name somehow, presumably from the control source property of ctl. They didn't provide that code?

[CementCarver]

No, they didn't.

CC

[pbaldy]

Along the lines of:

If Nz(ctl.Value) <> Nz(rs(ctl.ControlSource)) Then

[CementCarver]

Thanks PBaldy, I'll give that line a try.....CementCarver