Help with - Compile Error Invalid use of property

[DomFino]

I have been adding this audit trail creaated by ghudson
on Access Wrold Forum to my application with some of the modifications suggested by others on that. The audit trail works great on one form (includes subforms) but I receive a compile error when I try to add the functionality to another form (no subforms).

I receive an error: Compile Error Invalid use of property on the Call AuditTrail(Me) line. I read the help for this error but I have no clue as what it means or how to fix it. Can anyone help me figure out why the same Call statement works fine on one form but not another?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If [txtProjectEndDT] < [txtProjectBeginDT] Then
   MsgBox "Project End Date Cannot Be Less Than Project Begin Date"
   txtProjectEndDT.SetFocus
   Cancel = True
End If
    DoEvents
    Call AuditTrail(Me)

End Sub

I have included the module code for the function below:

Option Compare Database
Option Explicit

Public Function AuditTrail(MyForm As Form)
On Error GoTo Err_Audit_Trail
    
'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'[URL unfurl="true"]http://support.microsoft.com/default.aspx?scid=kb;en-us;197592[/URL]
    
    Dim ctl As Control
    Dim sUser As String
    Dim strUserName As String
    
    'get the login user name
    strUserName = Environ("USERNAME")
    sUser = strUserName
    
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
        MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
        Exit Function
    End If
    
    'Set date and current user if the form (current record) has been modified.
    MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"
    
    'Check each data entry control for change and record old value of the control.
    For Each ctl In MyForm.Controls
    
    'Only check data entry type controls.
    Select Case ctl.ControlType
    Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
        If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
            'If new and old value do not equal
            If ctl.Value <> ctl.OldValue Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
            'If old value is Null and new value is not Null
            ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
            'If new value is Null and old value is not Null
            ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
                MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
            End If
    End Select
    
TryNextControl:
    Next ctl
    
Exit_Audit_Trail:
    Exit Function
    
Err_Audit_Trail:
 '  If Err.Number = 64535 Then 'Operation is not supported for this type of object.
    If Err.Number = 3251 Then 'Operation is not supported for this type of object.
        Exit Function
    ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
        Beep
        MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
    Else
        Beep
        MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_Audit_Trail
    
End Function

 

[hneal98]

What kind of object is MyForm!AuditTrail?

 

[DomFino]

hneal98,
Thanks for your reply. I am not sure what you mean when you ask what kind of object is MyForm!AuditTrail.

This is the response another programmer (Lyn Mac) wrote to correct the original code so that it would work with subforms.



I had that problem earlier. I found out that when a form with subform is displayed and edits are being done to the subform the active form is the parent form and not the subform.

This seems to be the effect that this line in the Audit Trail had:

Set frm = Screen.ActiveForm

My solution:

1. Pass the form as an argument of the function

Function AuditTrail(frm as Form)

2. Comment out these lines in the code

'Dim frm as Form
'Set frm = Screen.ActiveForm

3. Call the function in the BeforeUpdate event of the form and or subform as
follows:

Call AuditTrail(Me)

 

[hneal98]

In your code above, you had this piece of code:

MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"

I needed to know if MyForm!AuditTrais is a subform, textbox, etc.

That would determine whether it is the culprit in the error message you were getting. I am assuming that tbAuditTrail is a tab control? Are you trying to pass the actual object to the other object?

Perhaps I should ask if you understand what I mean when I refer to an Object?

 

[DomFino]

Thanks again for your reply. Let me try to explain further.

I have a memo field in each table that I am storing all the audited information called AuditTrail.

On each form, I have the following code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

    Call AuditTrail(Me)

Form_BeforeUpdate_Exit:
    Exit Sub

Form_BeforeUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_BeforeUpdate_Exit

End Sub

Private Sub tbAuditTrail_DblClick(Cancel As Integer)
On Error GoTo tbAuditTrail_DblClick_Err

    Beep
    DoCmd.RunCommand acCmdZoomBox
    Me.txtLastName.SetFocus
tbAuditTrail_DblClick_Exit:
    Exit Sub

tbAuditTrail_DblClick_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume tbAuditTrail_DblClick_Exit

End Sub

tbAuditTrail is simply a text box with the control source AuditTrail.

I am fairly new to this VBA stuff and may not understand exactly what an object is as you surmised. However, I thought an object was a form, table, query, etc. Nevertheless, I simply do not understand why the Call AuditTrail(Me) works on one form and not another which is less complex and has no subforms.

I appreciate your patience and any explanation of what may be occurring is greatly appreciated. The original concept of the code is remarkable. It actually tracks any changes to a form and places the date, time, user, and what the field was and is after the change.

Again, thanks for your continued help.
Dom

 

[hneal98]

based on the error you are getting, you need to check all objects (and yes, you got it correct as to what an object is,) and make sure that anything being assigned to them matches their type. The reason i was asking about those objects is becuase it looks like you are trying to assign a text box to MyForm!AuditTrail. I am still not clear on what MyForm!AuditTrail is. Is it another text box?

The thing about text boxes, is if you want to access the data they hold, you should use MyForm!tbAuditTrail.text. I would go through each object like that and try assigning the value, not the object.

Let me know if that is not clear.

 

[DomFino]

hneal98,
The way I read it (and I could be way off base here) is that MyForm!AuditTrial is the current open form or Myform and the AuditTrail is the control source on the form or memo field in the table for that open form where all the audit data is stored.

Therefore, if I am opening the frmProjects form the MyForm!AuditTrail would equate to frmProjects!AuditTrail.

Hope that clears it up somewhat.

Since the original programmer made this a universal audit tracking tool, it would appear to me that a few things are going on.

1) The form that is open has a text box field named tbAuditTrail with a control source of AuditTrail.

2) The form that is open has a table associated with it that has a memo field named AuditTrail that stores the data changes for the open form.

3) All tables have one memo field for storing data changes (AuditTrail)

4) All forms have one text box field (tbAuditTrail) with a control source of AuditTrail

I suspect that maybe the error may have something to do with the current open form not being pointed too properly. Therefore causing the Call AuditTrail(Me) to not work correctly. Then again, that is just a guess.

Dom

 

[hneal98]

That cleared it up more, thanks. Based on the error "Compile Error Invalid use of property", it looks like you are using a property some place that is not the correct use. Unfortunately, VB is notorious for showing one error when the problem is something else down the line. Although I can't just blame it on VB, because all of the languages do the same thing.

Have you updated your Access since this app was created? It still seems to me like you need to be more specific with the text box and the memo field because the way it looks now, it looks like you are trying to add the textbox itself to the memo textbox, which would be a problem.

Try rewriting it like this:

MyForm!AuditTrail = MyForm!tbAuditTrail.text

and see if you still get that error.

 

[DougStevens]

Is AuditTrail even on the calling form? If it is and if its bound to a text box control (guessing that). Then make sure that the text box control has a different name from the control source. (e.g. name the control txtAuditTrail).

 

[DomFino]

hneal98 & Doug,

hneal98
I did as you suggested by adding the .text to the code. It did not solve the problem. I still get the same error on the same line of code Call AuditTrail(Me). I then tried the other form that worked but now that form give an error 2185 You Can't reference a property or method for a control unless the control has focus.

Doug,
No the field AuditTrail is not on any of the forms. It is the name of the data element in the table behind each form. Its field type is MEMO.

 

[hneal98]

Set a break point inside the module, at the beginning. Then run it and tell me where it stops with the error at.

 

[DomFino]

I did as you suggested. The code goes all the way through the module code and stops on the form's bold line below.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If [txtProjectEndDT] < [txtProjectBeginDT] Then
   MsgBox "Project End Date Cannot Be Less Than Project Begin Date"
   txtProjectEndDT.SetFocus
   Cancel = True
End If
    DoEvents
    Call AuditTrail(Me)

[b]End Sub[/b]

 

[hneal98]

I see where your problem is. Your sub has the Cancel variable declared as Integer. You are trying to set it to True which is boolean. So, you either need to declare it as boolean or set it to an integer.

 

[DomFino]

No such luck. I tried that but no cigar. As a matter of fact I have module (basUtilityFunctions) that handles this globally as follows:

Function IsLoaded(ByVal strFormName As String) As Boolean
 '---------------------------------------------------------------------------------------------------------
 'This code is used to find what form is loaded. It comes from the Microsofts Northwinds database.
  '---------------------------------------------------------------------------------------------------------

    Const conObjStateClosed = 0
    Const conDesignView = 0

    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
        If Forms(strFormName).CurrentView <> conDesignView Then
            IsLoaded = True
        End If
    End If

End Function

 

[hneal98]

What does that have to do with "Form_BeforeUpdate" and the Cancel problem?

 

[DomFino]

hneal98,
I wanted to get back to you with my final result. First, I tried your last suggestion but continued to have the same error.

Over the past few days, I finally got it to work. What I did was delete the offending form entirely. Then I did a decompile of the database (msaccess.exe /decompile). Then I rebuilt the form from the ground up. (It was a long weekend!)

When I went to compile the code -- NO ERROR. I suspect something was hosed up in the background of Access. I will probably never really know the cause. However, the end result is a working program.

Thank you once again for all your help. I learned a few things along the way especially how to use the Access debugger. I also learned that Access can be a real pain in the gludious maximus at times.

Have a safe and happy holiday,
Dom

 

[hneal98]

DomFino, hope you had a great holiday season. You are right, Access can be a pain at times. Often it gets corrupted in the back ground and makes everything go bad. You handled it just fine.