Runtime error 424

[NauticalGent]

Tried do something (I thougt!) would be simple... an old question with MANY answers on this forum.

I chose those solution because of its simplicity:
http://www.techrepublic.com/article/a-simple-solution-for-tracking-changes-to-access-data/

However, nothing EVER goes easy for me. I kept getting runtime error 424- Object Required.

424...that is the approximate number of times I dropped an "F" bomb while trying to get this to work. Finally gave up and went home. It was while I was halfway home that I THINK I realized where I went wrong. Going to have to wait til Monday.

More to follow. Beginning to think Uncle Gizmo's idea of a career change as a shelf stocker might not be a bad idea...

 

[pbaldy]

If you're still stuck, it would help to know what line it happens on. And perhaps what your code looks like, as it may have been modified from what's in the link.

 

[NauticalGent]

Will do...I am sure it is the second aurgument I am passing which just so happenes to be the same name as the control. I will test it out on Monday and report. Maybe I should have refrained from posting but it has been one HellaVaDay!

 

[pbaldy]

At least for you it's over. Here it's lunch time. Have a great weekend!

 

[NauticalGent]

Will do Paul, to you as well!

 

[The_Doc_Man]

This usually means an incorrect object declaration, which means you have an error either in a formal function/subroutine argument passing something by reference, or you have a faulty SET object = something statement.

Or you have made a reference to an object before instantiating or after de-instantiating.

To debug it, I would say to set a breakpoint before the line that is offensive and single step. OR if you think the object should be OK as-is, put a watch-point on it and see if something writes to it after you think it is OK.

 

[NauticalGent]

Well, it turns out I do NOT have this figured out.

Here is the code. I altered some of the variable names but for the most part, this is the code I am using.

Const cDQ As String = """"
Sub AuditTrail(frm As Form, recordid As Control)
  'Track changes to data.
  'recordid identifies the pk field's corresponding
  'control in frm, in order to id record.
  Dim ctl As Control
  Dim varBefore As Variant
  Dim varAfter As Variant
  Dim strControlName As String
  Dim strSQL As String
  On Error GoTo ErrHandler
  'Get changed values.
  For Each ctl In frm.Controls
    With ctl
    'Avoid labels and other controls with Value property.
    If .ControlType = acTextBox Then
      If .Value  .OldValue Then
        varBefore = .OldValue
        varAfter = .Value
        strControlName = .Name
        'Build INSERT INTO statement.
        strSQL = "INSERT INTO " _
           & "Audit (EditDate, User, RecordID, SourceTable, " _
           & " SourceField, BeforeValue, AfterValue) " _
           & "VALUES (Now()," _
           & cDQ & Environ("username") & cDQ & ", " _
           & cDQ & recordid.Value & cDQ & ", " _
           & cDQ & frm.RecordSource & cDQ & ", " _
           & cDQ & .Name & cDQ & ", " _
           & cDQ & varBefore & cDQ & ", " _
           & cDQ & varAfter & cDQ & ")"
        'View evaluated statement in Immediate window.
        Debug.Print strSQL
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      End If
    End If
    End With
  Next
  Set ctl = Nothing
  Exit Sub
ErrHandler:
  MsgBox Err.Description & vbNewLine _
   & Err.Number, vbOKOnly, "Error"
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
  Call AuditTrail(Me, ShipperID)
End Sub

After about an hour of Debuging the code and Rebuging myself, the offending line(s) are:

If .Value  .OldValue Then
        varBefore = .OldValue
        varAfter = .Value

Interesting this is that when I change

If .Value  .OldValue Then

to

If .Value <> .OldValue Then

it throws a different error: 3251 - Operation is Not Supported (blah blah blah)

If I comment out those lines ( and the IF..End IF) the remainder of the code runs like a charm.

During my many google sessions, another Forum had mentioned that it might be a DAO reference issue.

I checked my References and I do NOT have MS DAO 3.6 Object Library checked. Eureka! right??!? Wrong.

Checking cause me to get this message: Name conflicts with existing module, project or object library"

Before I started down THIS rabbit-hole, I figured I would let some of the "cool kids" on this Forum weigh in...

Any thoughts on this?

 

[NauticalGent]

Well, I couldn't help myself and consulted Dr. Google and came across this:

https://answers.microsoft.com/en-us...soft-dao/49c5bc34-24e7-4990-80de-b03b4b3d0f59

This answered my question as to what the conflict was:
"The Microsoft DAO 3.6 Object Library is for .mdb databases only.
The equivalent for .accdb databases in Access 2010 is the Microsoft Office 14.0 Access database engine Object Library."

I DO have MS Office 14.0 Access DEOL checked so now I am TRULY dumbfounded as to what the problem is...

That self-stocker position is looking more and more appealing each day...

 

[The_Doc_Man]

OK, I'm coming from another linguistics background so this is one that I don't understand here. What is the meaning of this expression?

      If .Value  .OldValue Then

I've checked a couple of references and I don't recall EVER seeing that there was such a thing as a "default operator" for comparisons. Is this a rendition error? Because Gent, you mention that when you rewrite it to include the "<>" operator, you get a different result. Syntactically, this should throw a "Missing Operator" error instantly at compile time. What am I missing?

As to the issue of this failing, is there any chance that the text box on which this fails happens to be unbound? Because unbound text boxes don't have a .OldValue property.

I discovered a long time ago that auditing will fail miserably for unbound objects. That's why my audit routines have a nasty little function called "HasProperty(obj, property)" that checks whether the object in question has the named property. It is simply that you set a trap in the code and try to test the property in some way. If you succeed, the property exists. If not, you get an error which usually resolves to the "does not exist" error. Then if the property exists, you can copy it, but if not, you can decide what else to do about it. In my case, I put a special string "{NotBound}" as the old value, which was appropriate for my case but might not be right for yours.

 

[NauticalGent]

OK, I'm coming from another linguistics background so this is one that I don't understand here. What is the meaning of this expression?

      If .Value  .OldValue Then

I've checked a couple of references and I don't recall EVER seeing that there was such a thing as a "default operator" for comparisons. Is this a rendition error? Because Gent, you mention that when you rewrite it to include the "<>" operator, you get a different result. Syntactically, this should throw a "Missing Operator" error instantly at compile time. What am I missing?

Noticed that too and figured it didn't come over right when I did the cut-n-past, some sort of HTML thingy. It complies just fine so I figured it HAD to be right, right? Wrong. But when I put in the "<>" and got a different error I figured it had to be right and the problem lay elsewhere. Frustrating. Also, to bear out your observation, when you type "ctl.Value.Oldvalue", Intelisense stops working after Value...but it still compiled so again, I figured it was correct.

As to the issue of this failing, is there any chance that the text box on which this fails happens to be unbound? Because unbound text boxes don't have a .OldValue property.

No, in this case all fields on this form are bound.

Thinking I will find another Audit Trail procedure to use. As I said earlier, I liked this one because it was dynamic and simple.

 

[NauticalGent]

Stand down Doc...although I havnt found the culprit, I know the code works...to be clear

.Value .Oldvalue is NOT correct
.Value <> .OldValue IS

I made a small scale version of this with a simple form and it worked just fine. The issue is some or at least one of my Text boxes. Some are hidden, not enable and so on. It is time for me to pack it in today but I will tackle this tomorrow and report my findings.

 

[The_Doc_Man]

Looks like we posted at the same time so I didn't see your "stand down" message. Here is what I was going to write anyway. If it helps, fine. If not, I will stand down. But the reason I was following up is that your code looks like it should have worked except for that one line.

Well, you said you got this error:

runtime error 424- Object Required.

For the syntax you posted,

If .Value .OldValue Then

The .OldValue, to be valid, has to have an object in front of it and I don't think that in that context, you can re-use the referent from the With clause. As you said, Intellisense stops at the .Value part. So .OldValue in that case, because it SEEMS to be a property, is a property outside of the syntax of the With. And that means it is a property without an object. Which is what error 424 is trying to tell you.

When you add the "<>" operator, you re-establish a context for a new variable, which has the side-effect of re-asserting the visibility of the With clause, so .OldValue makes sense in that context (and I'll bet that Intellisense picks up the .OldValue in that case).

The syntax problem (as I see it from my formal syntax studies many moons ago) is that you CAN string together properties in Access expressions. Further, if the item involved is an object of a more generic type, Access defers the "has property" decision until run-time when the object is instantiated. Well, ... "ctl" in your for loop is technically a generic object; more specific than "AS Object" but less specific than "AS Access.TextBox" - so Access (correctly or not) defers evaluation of the property sequence until execution time, giving you the benefit of the doubt. I'm just not sure why the space between .Value and .OldValue doesn't trigger "Missing Operator" but then again, I've seen Access do some really stupid stuff before.

The scent YOU need to follow is to determine why 3251 - Operation is not Supported gets thrown in your face. THAT is the real error. The question has to be WHY the operation isn't support. My debugging advice is

1. Set a breakpoint on the .IF - or better still, on the trap handler.

2. Open the Locals window

3. Find 'ctl' as a variable

4. Examine its properties to see what is in .Value and in .OldValue at that moment.

 

[pbaldy]

The fact that it works in a smaller sample confirms my belief that you can use .OldValue within a With block. I wondered if it was data-related, so Doc's suggestion/method to examine it is a good one. I might also try

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

to see if Nulls bother it.

 

[NauticalGent]

Well, I didn't pack it in and stuck around a little while longer...which cost me a warm dinner and a happy wife. Fortunately she is used to my "let me try one more thing" paradigm and didn't stay angry too long...

I'm just not sure why the space between .Value and .OldValue doesn't trigger "Missing Operator" but then again, I've seen Access do some really stupid stuff before.

Too clear Doc, if I left a space, I did get a syntax error, but when I removed it and ran the properties in tandem, it took and even complied...but was still jacked up.

I tried something I should have tried before posting but then that would be smart and for some reason, I'm just not allowed to be. I placed a debug.print after the For Each bit and triggered the event again. It went through 2 controls before choking on a text box. Not sure why it did; the control was enabled, not locked and visible. Only difference was that it was in the Header section while the previous ones were in the Detail section.

This prompted me to realize that I didn't need the code to evaluate EVERY control, just certain ones that I was interested in tracking. I used the Tag property for the applicable controls and every thing is coming up roses.

Something this easy is not supposed to be this hard. Appreciate you and Paul weighing in on this...

 

[The_Doc_Man]

Interesting. For future reference, you COULD have also checked whether the parent of the control was the detail section or the header section. But tagging it works, too.

 

[NauticalGent]

Figured you could...save me a gooogle search and tell me?

 

[pbaldy]

Offhand I can't think of why it would matter what section it was in.

 

[The_Doc_Man]

If the detail section was a continuous list of fields and the form header was not, there might be some difference - but I don't get that such is the case from reading Gent's description. My only point was to note that if he wanted to restrict his auditing to things in the detail section, there was a way to know it.

In fact, if you don't like .Parent, there is also .Section, which tells you which section something is in.

acDetail = 0
acHeader = 1            'form header
acFooter = 2            'form footer
acPageHeader = 3
acPageFooter = 4

etc. - higher numbers occur for report group headers and footers.

I'm not suggesting that in general there is a difference between data availability in a form detail section vs. a form header/footer, but only that there is a simple test to decide whether the information in question is in a specific part of the form.

 

[The_Doc_Man]

It went through 2 controls before choking on a text box. Not sure why it did; the control was enabled, not locked and visible. Only difference was that it was in the Header section while the previous ones were in the Detail section.

I'm going to assume that the text box in the header was apparently normally bound to a field in the .RecordSource?