Form data entry history

[John]

Hi

I am using the following code to save the changes to a form's data in the
form's Before Update event;

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.OldValue <> ctl.Value Then
st = st & ctl.Name & "=" & ctl.Value & vbCrLf & vbCrLf
End If
End If
Next ctl

If st <> "" Then
' Save st in a table
End If

The problem is that this code does not always work and save the changes on
some of the fields particularly the ones which are memo. Any reason why this
does not always work?

Thanks

Regards

 

[tina]

i don't have any suggestions as to why your code does not work consistently.
but if nobody else is able to help you solve the issue, i can offer an
alternative suggestion: take a look at MVP Allen Browne's instructions for
creating an Audit Log, at http://allenbrowne.com/AppAudit.html
i've implemented the setup in a couple databases, and it works very nicely.

hth

 

[John]

Its bit complicated for my meagre requirements. What I have discovered after
tracing the code is that the expression ctl.OldValue <> ctl.Value returns
null (instead of true or false) if ctl.OldValue has a value and ctl.Value is
null.

Here is an example from command window;

? ctl.OldValue
76577687

? ctl.Value
Null

? (ctl.OldValue <> ctl.Value)
Null

Is this a bug with access 2000 sp3?

Thanks

Regards

 

[Randy Harris]

Its bit complicated for my meagre requirements. What I have discovered after
tracing the code is that the expression ctl.OldValue <> ctl.Value returns
null (instead of true or false) if ctl.OldValue has a value and ctl.Value is
null.

Here is an example from command window;

? ctl.OldValue
76577687

? ctl.Value
Null

? (ctl.OldValue <> ctl.Value)
Null

Is this a bug with access 2000 sp3?

No, this is no bug. A null has no value and cannot be compared to a value.
Any null compared to anything will always be null. You could use:

?(ctl.OldValue <> Nz(ctl.Value, 0)

Or

? Nz(ctl.OldValue <> ctl.Value, 0)

which would be 0 (false) if either value (or both) were null)

HTH,
Randy