>>3251 "Operation is not supported for this type of object."

[Jonathan]

Hi using Access 2003. In the before update event of a form for each data
entry control I am checking whether each control's value and old value match.
For most of these controls there is no problem. However for some of these
controls (mostly textboxes and, some combo boxes and check boxes) this
routine triggers error number 3251 when attempting to return a control's old
value and I don't know why as all controls seem to be set the same.

Any idea's or suggestings to resolve this are appreciated

Many thanks,
Jonathan

 

[Jeanette Cunningham]

Jonathan,
If you have any unbound controls, they do not have an old value property.

Jeanette Cunningham

 

[Jonathan]

Jonathan,
If you have any unbound controls, they do not have an old value property.

Jeanette Cunningham

Hi Jeanette, thanks for responding to my question. These controls are all
bound.

Many thanks,
Jonathan

 

[Jeanette Cunningham]

Jonathan,
the other thing I find with the old value is that is always a string data
type - this caused me some grief when I was learning.
You can check this in the vba help for old value.

Jeanette Cunningham

 

[Jonathan]

Jonathan,
the other thing I find with the old value is that is always a string data
type - this caused me some grief when I was learning.
You can check this in the vba help for old value.

Jeanette Cunningham

Jeanette, I tried assigning the old value to a variant and still get this
same error. Even hovering the Pointer over ctl.oldvalue triggers this error.

Jonathan

 

[Jeanette Cunningham]

Jonathan,
Another thought is that it might be the start of corruption.
Perhaps it's quicker to save a copy of this form with a different name and
see if you still get the error.
Perhaps you need to save the database and back it up, then do a compact and
repair.

If the above fails, would you post the code that is causing the error.
What is your form doing before this error comes up?
How complicated is your form?
Could you describe its setup?

Jeanette Cunningham

 

[Jonathan]

Jonathan,
Another thought is that it might be the start of corruption.
Perhaps it's quicker to save a copy of this form with a different name and
see if you still get the error.
Perhaps you need to save the database and back it up, then do a compact and
repair.

If the above fails, would you post the code that is causing the error.
What is your form doing before this error comes up?
How complicated is your form?
Could you describe its setup?

Jeanette Cunningham

Thanks for the suggestions. Unfortunitely still fails.

*** code snippet ***

If ctl.Visible And ctl.Enabled And Not ctl.Locked Then 'Only log if
user can change it
newVal = CStr(NZ(ctl.Value, "<EMPTY>"))
oldVal = CStr(NZ(ctl.oldValue, "<EMPTY>"))
If newVal <> oldVal Then
LogField frm, ctl, oldVal
End If
End If

*** end snippet ***

The above is run as part of the form_beforeUpdate event. This is triggered
when moving to another record or closing form.

Purpose is to log any changes and keep previous values.

I guess the form is rather complex in that it contains a tab control with 9
tabs; some of which contain one or more subform. However only 9814 records.

Jonathan

 

[Jeanette Cunningham]

When you step through the code, which line gives the error?
What happens when you check what access interprets for each line that you
posted like this:

debug.print ctl.Visible & ", " & ctl.Enabled & ", " & ctl.Locked
If ctl.Visible And ctl.Enabled And Not ctl.Locked Then
newVal = CStr(NZ(ctl.Value, "<EMPTY>"))
debug.print newVal
oldVal = CStr(NZ(ctl.oldValue, "<EMPTY>"))
debug.print oldVal
'rest of code
End If

What values does access give for each of those things above?


Jeanette Cunningham

 

[Jonathan]

When you step through the code, which line gives the error?
What happens when you check what access interprets for each line that you
posted like this:

debug.print ctl.Visible & ", " & ctl.Enabled & ", " & ctl.Locked
If ctl.Visible And ctl.Enabled And Not ctl.Locked Then
newVal = CStr(NZ(ctl.Value, "<EMPTY>"))
debug.print newVal
oldVal = CStr(NZ(ctl.oldValue, "<EMPTY>"))
debug.print oldVal
'rest of code
End If

What values does access give for each of those things above?


Jeanette Cunningham

Doh! Sorry, I should have answered these questions in my previous response.

The following line triggers the error.
oldVal = CStr(NZ(ctl.oldValue, "<EMPTY>"))

Hovering the pointer over
ctl.oldValue
triggers the error.

The following are examples...
debug.print newVal when a combobox had value 23
debug.print newVal when a text box had value 'Jane'

20 controls are successfully handled
11 controls fail on ctl.oldValue

creating new controls using controls that are handled successfully as a
template does not overcome problem.

Jonathan

 

[Jeanette Cunningham]

Not sure why those controls would fail to have an old value if they are
bound controls.
To check further, if you open the form in edit mode, then open the query the
form is based on, what can you see as the query value for each field for
those 11 controls that fail the old value test?
Does it make any difference if you temporarily delete the source objects
from all the subforms and try again?

If it continues to be a problem, and no one else jumps in with the reason,
you can set up your own way to capture the original value for those 11
controls.

It is a bit of coding work. You can store the original value in the
control's tag and use that to compare with the current value. In the On
current event for the form put code that visits each of those controls and
writes the contol's value into the control's tag.


Jeanette Cunningham

 

[Jonathan]

Thanks Jeanette, I'll go with the suggestion to use control tags. The code is
really only a slight variation of the code snippet in this post.

Jonathan

 

[Jeremy Ellison]

I am running into this same problem. How are you putting the old values into
the control tag?

I don't understand why this is happeneing either...

 

[Jeremy Ellison]

OK, I got the .tag to work right now using the on current event of the form.
it goes through each control and puts the .value into the .tag


the problem is when I compare using the before_update event anything that is
not text thinks it is changed, even if it is not changed.....

here are some records:

as you can see, even though the incidentdate is teh same, it still thinks it
is not the same... any thoughts?

AUDIT_CHANGE_ID CHANGE_DATE CHANGE_USER RECORD_ID SOURCE_TABLE SOURCE_FIELD BEFORE_VALUE AFTER_VALUE
127 6/12/2009 3:27:10 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIntakeTime 1:40:00 PM 1:40:00 PM
128 6/12/2009 3:27:10 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIncidentDate 5/30/2009 5/30/2009
129 6/12/2009 3:27:11 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIncidentTime 10:52:00 AM 10:52:00 AM
130 6/12/2009 3:27:12 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtSelectAddressType 1 1
131 6/12/2009 3:27:14 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtStreetDirection E W

 

[Jonathan]

OK, I got the .tag to work right now using the on current event of the form.
it goes through each control and puts the .value into the .tag


the problem is when I compare using the before_update event anything that is
not text thinks it is changed, even if it is not changed.....

here are some records:

as you can see, even though the incidentdate is teh same, it still thinks it
is not the same... any thoughts?

AUDIT_CHANGE_ID CHANGE_DATE CHANGE_USER RECORD_ID SOURCE_TABLE SOURCE_FIELD BEFORE_VALUE AFTER_VALUE
127 6/12/2009 3:27:10 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIntakeTime 1:40:00 PM 1:40:00 PM
128 6/12/2009 3:27:10 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIncidentDate 5/30/2009 5/30/2009
129 6/12/2009 3:27:11 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIncidentTime 10:52:00 AM 10:52:00 AM
130 6/12/2009 3:27:12 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtSelectAddressType 1 1
131 6/12/2009 3:27:14 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtStreetDirection E W

Hi Jeremy, have you taken into consideration that you may have to convert
the tag value from string to a specific data type (or vice versa) for the
comparison.

Luck
Jonathan

 

[Jeremy Ellison]

Nope. I will give that a shot. Thanks!

Jeremy

OK, I got the .tag to work right now using the on current event of the form.
it goes through each control and puts the .value into the .tag


the problem is when I compare using the before_update event anything that is
not text thinks it is changed, even if it is not changed.....

here are some records:

as you can see, even though the incidentdate is teh same, it still thinks it
is not the same... any thoughts?

AUDIT_CHANGE_ID CHANGE_DATE CHANGE_USER RECORD_ID SOURCE_TABLE SOURCE_FIELD BEFORE_VALUE AFTER_VALUE
127 6/12/2009 3:27:10 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIntakeTime 1:40:00 PM 1:40:00 PM
128 6/12/2009 3:27:10 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIncidentDate 5/30/2009 5/30/2009
129 6/12/2009 3:27:11 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtIncidentTime 10:52:00 AM 10:52:00 AM
130 6/12/2009 3:27:12 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtSelectAddressType 1 1
131 6/12/2009 3:27:14 PM Jeremy Ellison
[178600] 16 RS_frmComplaint_Intake txtStreetDirection E W

Hi Jeremy, have you taken into consideration that you may have to convert
the tag value from string to a specific data type (or vice versa) for the
comparison.

Luck
Jonathan