Audit Trail

[DOF2001]

Hi i have this piece of code in my access databse that tracks changes made to 1 form. the code work properly.
I upgrating my databse to SQL using access as the front end application. All forms will be in access and all Tables in SQL. The access DB will be linked to the SQL to access the data.

For some reason the code that worked fine in acces now doesn't work. Here is the code:

Public Function TrackChanges()
    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim strCtl As String
    strCtl = Me.ActiveControl.Name
    'strCtl = ActiveControl.Name
    strSQL = "SELECT Audit.* FROM Audit;"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
        If rs.RecordCount > 0 Then rs.MoveLast
        With rs
            .AddNew
            rs!FormName = Me.Name
            rs!ControlName = strCtl
            rs!DateChanged = Date
            rs!PriorInfo = Me.ActiveControl.OldValue
            rs!NewInfo = Me.ActiveControl.Value
            rs!CurrentUser = fOSUserName
            rs!ProjectID = Me.ProjectID
            rs!ID = Me.ID
            .Update
        End With
    
    Set db = Nothing
    Set rs = Nothing
End Function

[code/]
this is the error message i get:

"The expression Before Update as the event as gthe event property setting produced the following error:"
      The expression may not result in the name of a macro, the name of a user-defined   function, or [event Procedure]

Any ideas why?

 

[james_lankford]

I don't know if you can use CurrentDB with an sql database.
Maybe you can. Maybe you can't ?
I thought you had use adodb, but maybe I'm wrong

Dim cnn As New ADODB.Connection
Dim rs As new ADODB.Recordset
Dim strConnect As String, strSQL As String

strConnect = "Provider=SQLOLEDB;Data Source=ST01;Initial Catalog=MyDatabase;User Id=myuserid;Password=mypass;"

cnn.Open strConnect

strSQL = "SELECT Audit.* FROM Audit;"

rs.Open strSQL, cnn, adOpenStatic, adLockOptimistic

' do some stuff

rs.close
set rs = nothing

cnn.Close
set cnn = nothing

also, I'm pretty sure the rs.RecordCount thing only works with Access and not sql databases

I think you MIGHT be able to get it to work with a sql database, but it depends on whether you're driver supports it and what type of cursor you're using

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

http://www.connectionstrings.com/sql-server-2008

 

[xenou]

Another thought -- this also might be a problem:

rs!CurrentUser = fOSUserName

Try it with a literal and see if it works:

rs!CurrentUser = 'Test_UserName'

Post back the results of your experiment. I have found ADO to be inhospitable to user-defined functions (which is what your message suggests) - you can't use them in queries passed with ADO, so maybe not in recordset value assignments either.

 

[DOF2001]

Thanks for all sugestions, I tey them but now uy get the error the the Me. fucntion is not recognized?

 

[james_lankford]

the only reason
me
wouldn't work is if the code is not in a form but is in a standard module
but since you said the code worked before then that should be the case

unless you changed the code

 

[xenou]

James sounds right on here. Which "Me" does this refer to now? Is the code different or the same?

In your original post:

"The expression Before Update as the event as gthe event property setting produced the following error:"
The expression may not result in the name of a macro, the name of a user-defined function, or [event Procedure]

How is a Before Update related to this? I don't see anything in your code that fits that description.

 

[DOF2001]

The before update is the event in the main form that run the code. the only thing i change is the tables from the data is pulled. before tables were in the access DB now those are i na SQL DB. so i used data from the linked tables instead of directly from access.

 

[james_lankford]

have you put a debug breakpoint in the code and stepped through it to exactly where its messing up ?