Audit Trail with OldValue problem in Access 2010

[Emily]

Hi,

I am very new to VB and I am using Access 2010. I've copied the code from this forum on AuditTrail. I followed all the steps, imported the tblAudit and the two modules. However when I run it, i keep on getting 3251-Operation not supported for this type of object. Somehow it doesn't seem to like Oldvalue, I was able to display the ccnt.value but not the ccnt.Oldvalue. I will get the error even on just displaying ccnt.oldvalue.
It seems to work ok in older version of Access but just not in 2010.
I tried it on a single form as well as continous form in 2010, but both doesn't work either.

Appreicate if anyone can help, many thanks in advance.

 

[boblarson]

See if this applies (it did to something I was doing once):
http://support.microsoft.com/kb/184664

 

[Emily]

Thanks Bob,
I will give that a try. Do I just declare the Dim rs as Recordset in the Form and set rs = Me.RecordsetClone in the OnCurrent event in the Form as well?

Then how do I get the oldvalue to the AuditTrail function to do the compare? Do I have to modify the AuditTrail function?

 

[Emily]

Hi Bob,

I added the Dim rs as recordset in the Form declare section and added
set rs = me.recordsetClone and rs.bookmark = me.bookmark on the form OnCurrent event.

On the form Before Update event, I tried to pass rs to the AuditTrail function by Call Audit_Trail(Me, "id", Id.Value, rs)

But I keep on getting object variable not set error 91

Then I force the
Set rs = Me.RecordsetClone in my form before update event
just before my call function statement and I added rs as recordset in AuditTrail function as the 4th argument.

Public Function Audit_Trail(MyForm As Form, UniqID_Field As String, UniqID As String, rs As Recordset)

Then I get a compile error : constant expression required.

Can you tell me what I am doing wrong. I am using a continious form and its based on AutoLookup Query.

 

[boblarson]

The first thing I would think is to change

rs As Recordset

to

rs As DAO.Recordset

But I have to say that I am not sure about your stuff because I had to actually change the query to make it work. I didn't go to the lengths you are attempting to keep the query the same.

 

[Emily]

Hi I am getting the same error ! How did you change your query to work?
I did a Msgbox in the OnCurrent event, the rs value showed up ok. Just that in the before update, it didn't like passing the recordset to the function.
Do you think I can get around by using global variables, by setting rs values into the global variable and use the global variables to call the functions?

I am also willing to change my query if I can get the auditTrail working.

Many thanks.

 

[boblarson]

I'm not sure at this point. And I'm not even sure which one it was. This happened at least 2 years ago for me.

 

[Emily]

Thanks Bob,
Finally decided to go for a work around solution to change my query to based on one single table instead. Now the audit trail works fine.
Thank you so much for your help.

 

[boblarson]

Glad you got a solution worked out. Sorry I wasn't more help. I couldn't remember where that particular query was (we have 5 large Access applications here at work which I maintain and there are a lot of possible places it could have been) but I had saved the link to the article which I had found which explained the problem.

 

[Emily]

Oh yes BOB!! YOU DID HELP ME A LOT!! I was running around and couldn't figure out what was the problem. Knowing the cause, is problem half solved. If it wasn't for you, I wounldn't be able to come up with a work around solution and I probably be still looking at my syntax. Thank you again!