OldValue not available on sub forms

[Pete]

I am using the "audit trail code of record changes in a form" (kb 197592 -
Access 2000) using Access 2003.
I have placed this code in a module and this works perfectly when called
from the main form.
However, when the code reaches the line where the (new) Value is checked
against the OldValue.
If ctl.Value <> ctl.OldValue Then

At this point, the app. raises an error:-
Run-time error '3251': "Operation is not supported for this type of object".

I am calling this code from the BeforeUpdate event of the sub form when this
error is generated.
I have even tried passing the sub form's reference to the Audit Trail
procedure but this raises the same error.

It's as though the OldValue property for a control is not available when
used on a subform.

Could someone please advise as to why this is and is there a possible
work-around.

Many thanks.

 

[Allen Browne]

What is the source of your subform?

If it is a multi-table query, you may find that some of the fields are not
editable (e.g. fields from a lookup table.) Similarly if there are some
calculated fields, they will not be editable. The attempt to examine the
OldValue of these uneditable fields will therefore fail.

Temporarily comment out the error handling. When it stops, open the
Immediate Window (Ctrl+G), and enter:
? Ctl.Name
You will then know which field it is struggling with. If this is an
uneditable field and you just want to ignore it, add error handling to
ignore error 3251.

 

[Douglas J. Steele]

I can't see any reason why it would work any differently for a subform.

Now, the KB article you cited uses different variable names than you seem to
be. Exactly what did you put in the in the BeforeUpdate event?

 

[Pete]

Hi Douglas,

I have adapted the Microsoft code to pass the subform's reference as the
original code looks at the active form and I wasn't sure if a subform would
be the active form when the code is running from a module; called from the
subform.

I am calling the Audit_Trail procedure from the BeforeUpdate event with:-
Call Audit_Trail(Me, True)
The 2nd argument is used to flag a subform being used so that different code
in the Audit_Trail code can reference and append to the Audit trail memo
field on the parent form.

The main form works fine but the OldValue property doesn't work for controls
on the subform and raises the error I mentioned.

Hope this helps.
Pete.

 

[Pete]

Hi Allen,

It appears to be the Description field.

The subform is based on a 2 table query (order lines and Stock) and this
field is just there to show the description of the actual product code
selected (from the stock table) and would normally be locked; having unlocked
it to sort this problem out.

I will try adding code to ignore error '3251' and test.

Many thanks for the suggestion.
Pete.

 

[Pete]

Trapping the error code 3251 and moving onto the next control has
circumvented the problem.

Many thanks for your help.

Kind regards,
Peter