Problems with calling a function from a module

[algecan]

Hey,

Im in the process of adapting the audit trail that was posted on here a few years back now by 'ghudson'. the problem that i have with it is that i get an error message when i change the values of combo boxes - "'Operation is not supported for this type of object." I did abit of digging on this problem but i cant find the mircrosoft site that told me why it wouldnt work. However the solution was to create a recordsetclone on the Current Event to identify the before value (which is the bit that will not work according to microsoft). Ive set this up fine on the form...

 Option Compare Database
Dim rs As Recordset
 
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call Audit_Trail(Me, Control_Ref)
End Sub
 
 
Private Sub Form_Current()
Set rs = Me.RecordsetClone
         rs.Bookmark = Me.Bookmark
End Sub
 
 
Public Function GetBeforeValue(CurrentFieldName As Control) As String
GetBeforeValue = rs![CurrentFieldName]
End Function

The problem i have is when i call the function 'GetBeforeValue' from the module. Here is the relevent code from the module...

Public Function Audit_Trail(MyForm As Form, recordid As Control)
....
Dim Ctl As Control
Dim FieldBeforeValue As String
...
FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)

FieldBeforeValue then gets populated with the before value of each field in the form which is used to then insert into a table.

However when i do make a change i get the following error message

run-Time Error 2465
Application-defined or Object-defined error

I've also had Object not defined error when i was trying to fix it. The error occurs on 'FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)'.

Anybody have any ideas where i might be going wrong?

In short im trying to call a function on a form from a module and pass it a control name (such as the combo box name) and this then returns the value of the control before it was changed using the recordsetclone.

 

[ChrisO]

This line looks wrong: -

Public Function GetBeforeValue(CurrentFieldName As Control) As String

Try: -

Public Function GetBeforeValue(CurrentFieldName As String) As String

 

[algecan]

ChrisO, Ive made that change and now i get..

Runtime Error 91
Object Variable or with block variable not set.

I get this error on the same line - FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)

Any more ideas?

 

[ChrisO]

Yes I have.

Please post all the code in: -

Public Function Audit_Trail(MyForm As Form, recordid As Control)

Because lines like: -
….
And
…
make little sense.

 

[algecan]

Here is all the code in the Module

Public Function Audit_Trail(MyForm As Form, recordid As Control)
 
    Dim Ctl As Control
    Dim sUser As String
    Dim strControlName As String
    Dim strSQL As String
    Dim FieldBeforeValue As String
'    sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
    sUser = CurrentUser
 
    'If new record, record it in audit trail and exit function.
    If MyForm.NewRecord = True Then
                        '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 & MyForm.RecordSource & cDQ & ", " _
                   & cDQ & Ctl.Name & cDQ & ", " _
                   & cDQ & "NEW RECORD ADDED" & cDQ & ", " _
                   & cDQ & Ctl.Value & cDQ & ")"
                'View evaluated statement in Immediate window.
                Debug.Print strSQL
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
        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
 
    [COLOR=red]FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)[/COLOR]
 
    'Only check data entry type controls.
    Select Case Ctl.ControlType
    Case acTextBox, acComboBox
        If Ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
            'If new and old value do not equal
            If Ctl.Value <> FieldBeforeValue Then
                '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 & MyForm.RecordSource & cDQ & ", " _
                   & cDQ & Ctl.Name & cDQ & ", " _
                   & cDQ & FieldBeforeValue & cDQ & ", " _
                   & cDQ & Ctl.Value & cDQ & ")"
                'View evaluated statement in Immediate window.
                Debug.Print strSQL
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
            'If old value is Null and new value is not Null
            ElseIf IsNull(FieldBeforeValue) And Len(Ctl.Value) > 0 Or FieldBeforeValue = "" And Len(Ctl.Value) > 0 Then
                                '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 & MyForm.RecordSource & cDQ & ", " _
                   & cDQ & Ctl.Name & cDQ & ", " _
                   & cDQ & FieldBeforeValue & cDQ & ", " _
                   & cDQ & Ctl.Value & cDQ & ")"
                'View evaluated statement in Immediate window.
                Debug.Print strSQL
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
            'If new value is Null and old value is not Null
            ElseIf IsNull(Ctl.Value) And Len(FieldBeforeValue) > 0 Or Ctl.Value = "" And Len(FieldBeforeValue) > 0 Then
                                '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 & MyForm.RecordSource & cDQ & ", " _
                   & cDQ & Ctl.Name & cDQ & ", " _
                   & cDQ & FieldBeforeValue & cDQ & ", " _
                   & cDQ & Ctl.Value & cDQ & ")"
                'View evaluated statement in Immediate window.
                Debug.Print strSQL
                DoCmd.SetWarnings False
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
            End If
    End Select
 
TryNextControl:
    Next Ctl
 
Exit_Audit_Trail:
    Exit Function
End Function

 

[algecan]

just an update...

because the error message was saying that the object variable was not set, i changed the line that was causing the error to...

Set FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)

I'm not sure if this is required but by doing so i now have a new error...

Compile Error:
Object Required.

I get the error here...
Set FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)

 

[namliam]

Only use set for setting objects, for variables you do not use it...
FieldBeforeValue = MyForm.GetBeforeValue(Ctl.Name)

 

[ChrisO]

Sorry but it won’t even compile and the indenting implies a modification by someone else.

May I suggest you don’t just copy/paste code and expect it to work and then if it doesn’t work expect others to get it working for you?

The problem here is that the original code by ghudson was probably correct, he generally didn’t make too many mistakes, but I would say you have modified it to a point you don’t understand.

Please get it to a point that it at least compiles (turn on Option Explicit) and I might look at it over the weekend.

 

[algecan]

Ok thanks for that, ive taken Set out.

Ive just noticed somthing. when i first open the form and make a change to a record so that it will call the module i get an error message on he same line that says...

"Item not Found in this collection"

Now if i press end or debug and then change the record again i get the previous error message of

"Object Variable or with block variable not set."

 

[algecan]

ChrisO, I did a bit of editing to this code so that it would put the values in a seperate table, ghudson's code put the before and after data in a field for the record that was changed.

The code did work apart from on one form and only on the combo boxes which is why i had to modify it to use the recordsetclone. I'll try and dig out the article i found as to why it doesnt work as this may explain things.

 

[algecan]

right this is the article that explains why the orginal code did not work

http://support.microsoft.com/default.aspx?scid=kb;EN-US;207836

the original code used Ctl.OldValue but as the article explains this will not work which is why ive had to change it use the recordsetclone. I haveput a lot of effort into trying to get this to work before i asked for help but im just going round in circles.

Also i got was able to use the recordsetclone in the form by adapting it so when i clicked a button on the form it populated an unbound field on the form with the before value. the problem occured when i then tried to call the function from the module.

 

[ChrisO]

What is the point of even trying to do this if you didn’t turn on Option Explicit?

 

[algecan]

Because i only found out what Option Explicit was about 5 minutes ago. I'm self tought using VBA and having only been using it for about 2 months so i'm picking up on things as i go along.

Option Explicit is now turned on.

 

[RainLover]

How did you turn on Option Explicit

 

[algecan]

In the VBA editor i went to Tools>Options and selected 'Require Variable Declaration'

i also have 'Option Explicit' a the top of my code.

Hopefully this is correct.

 

[RainLover]

Did you compile

 

[algecan]

Yes but it didnt show any errors. Compile is now greyed out so i cant do it again.

 

[RainLover]

It is now at the point that you need to post a sample database.

 

[algecan]

I've tried uploading a sample database but it keeps failing. I will keep trying

Ive been reading into the "Item not found in this collection" error and keep seeing OpenRecordSet, but i'm not sure how this would work

 

[RainLover]

Strip down your database so that it only contains the relative items. Then zip it and try to post that.