Problem with AuditTrail (1 Viewer)

[daikaio94]

Hello,

I tried to follow Martin Greens example to implement an AuditTrail to my database. (I cannot post the link but you can find it easy using google/bing/whatever.)

At first sight everything went well, I tested the code using a test database and it worked just fine. But when I tried to use it in my existing project I encountered a problem. It seems like two of the comboboxes I use do not have an OldValue and I have no idea why. The third combobox I use works as intended.

The form I'm using is bound the a query and every control is bound aswell.

I created an example of my problem attached to this post, this is exactly the structure I'm using in my project.

I hope you're able to understand my problem, if there is anything unclear just ask.

Regards
daikaio

 

[Uncle Gizmo]

Are they unbound combo boxes?

Sent from my SM-G925F using Tapatalk

 

[daikaio94]

No. All controls, including the comboboxes, are bound.

 

[Uncle Gizmo]

Use MsgBox to return the offending controls oldvalue, you will then see what it is.

Sent from my SM-G925F using Tapatalk

 

[arnelgp]

compact and repair.

 

[daikaio94]

Use MsgBox to return the offending controls oldvalue, you will then see what it is.

Sorry :banghead: I forgot to mention that it throws an error message when I try to get the OldValue:
"Run-time error ‘3251’: Operation is not supported for this type of object"

compact and repair.

Tried it, still doesn't work.


And btw: thank you for the fast responses and the help provided!

 

[Uncle Gizmo]

Sounds you like you are interrogating a command button or some other control that does not have an old value property.

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

I didn't think command buttons had an oldvalue property, but it appears they do....

https://msdn.microsoft.com/en-us/library/office/aa172955(v=office.11).aspx

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

So it could be a label?

Sent from my SM-G925F using Tapatalk

 

[Uncle Gizmo]

By the way:-

From the ms link, this piece of code is what stops the code from interrogating controls "without" an oldvalue property.

If ctlTextbox.ControlType = acTextBox Then

So check to see if your code has something similar.

Sent from my SM-G925F using Tapatalk

 

[daikaio94]

No, the error occurs only with the two comboboxes. Also I'm checking if the control is tagged to be audited, so I can add specific controls only and not Audit the entire form. It works fine on other controls and even another combobox I added works......

Just to be sure I deleted everything and rebuild the complete form testing only the two comboboxes but I still get the same error.

Maybe you can see something I'm missing in the code:
I'm calling it in the before_update event of the form.

 [COLOR=#000080]Sub[/COLOR] AuditChanges(IDField [COLOR=#000080]As String[/COLOR])
[COLOR=#000080]    On Error GoTo[/COLOR] AuditChanges_Err
[COLOR=#000080]    Dim[/COLOR] cnn [COLOR=#000080]As[/COLOR] ADODB.Connection
[COLOR=#000080]    Dim[/COLOR] rst [COLOR=#000080]As[/COLOR] ADODB.Recordset
[COLOR=#000080]    Dim[/COLOR] ctl [COLOR=#000080]As[/COLOR] Control
[COLOR=#000080]    Dim[/COLOR] datTimeCheck [COLOR=#000080]As Date
    Dim [/COLOR]strUserID[COLOR=#000080] As String[/COLOR]
[COLOR=#000080]    Set[/COLOR] cnn = CurrentProject.Connection
[COLOR=#000080]    Set[/COLOR] rst = [COLOR=#000080]New[/COLOR] ADODB.Recordset
[COLOR=#000080]    [/COLOR]rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
[COLOR=#000080]    [/COLOR]datTimeCheck = Now()
    strUserID = Environ("USERNAME")
[COLOR=#000080]    For Each[/COLOR] ctl [COLOR=#000080]In[/COLOR] Screen.ActiveForm.Controls
[COLOR=#000080]        If[/COLOR] ctl.Tag = "Audit" [COLOR=#000080]Then[/COLOR]
[COLOR=#000080]            If[/COLOR] Nz(ctl.Value) <> Nz(ctl.OldValue) [COLOR=#000080]Then[/COLOR]
[COLOR=#000080]                With[/COLOR] rst
[COLOR=#000080]                    [/COLOR].AddNew
[COLOR=#000080]                    [/COLOR]![DateTime] = datTimeCheck
[COLOR=#000080]                    [/COLOR]![UserName] = strUserID
[COLOR=#000080]                    [/COLOR]![FormName] = Screen.ActiveForm.Name
[COLOR=#000080]                    [/COLOR]![RecordID] = Screen.ActiveForm.Controls(IDField).Value
[COLOR=#000080]                    [/COLOR]![FieldName] = ctl.ControlSource
[COLOR=#000080]                    [/COLOR]![OldValue] = ctl.OldValue
[COLOR=#000080]                    [/COLOR]![NewValue] = ctl.Value
[COLOR=#000080]                    [/COLOR].Update
[COLOR=#000080]                End With[/COLOR]
[COLOR=#000080]            End If[/COLOR]
[COLOR=#000080]        End If[/COLOR]
[COLOR=#000080]    Next[/COLOR] ctl
AuditChanges_Exit:
[COLOR=#000080]    On Error Resume Next[/COLOR]
[COLOR=#000080]    [/COLOR]rst.Close
[COLOR=#000080]    [/COLOR]cnn.Close
[COLOR=#000080]    Set[/COLOR] rst = [COLOR=#000080]Nothing[/COLOR]
[COLOR=#000080]    Set[/COLOR] cnn = [COLOR=#000080]Nothing[/COLOR]
[COLOR=#000080]    Exit Sub[/COLOR]
AuditChanges_Err:
[COLOR=#000080]    [/COLOR]MsgBox Err.Description, vbCritical, "ERROR!"
[COLOR=#000080]    Resume[/COLOR] AuditChanges_Exit
[COLOR=#000080]End Sub[/COLOR]

 

[Uncle Gizmo]

Are the combo boxes on a subform?

Sent from my SM-G925F using Tapatalk

 

[daikaio94]

nope, everything is in the same form.

 

[Uncle Gizmo]

This is Wrong, and possibly the source of corruption hence I suggest you create a new dB and form from scratch...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call AuditChanges("RechnerID")
End Sub

Should be:-

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call AuditChanges("Rechner_ID")
End Sub

 

[Uncle Gizmo]

Actually hold the bus I'm not sure about that! Let me do some more tests...

 

[Uncle Gizmo]

Actually hold the bus I'm not sure about that! Let me do some more tests...

When I created a new form based on the query "qryUser_Rechner" it automatically gave the field that other name, so when I ran the code it threw up an error, making me think that was the problem, but it's just a red herring! I'm still looking will get back to you shortly...

 

[Uncle Gizmo]

Right I think I found the problem there is something wrong with your SQL statements in those offending combo boxes. If you try and add a new record, you can't!

You get error message:-
"cannot add records - join key of table "tblUser_Rechner" not in recordset

That's where your problem lies, it is making the combo box look like it is not bound. (I think) that's my guess anyway!!!

 

[Uncle Gizmo]

It might not be the SQL statements in the combobox(s) it could be your query "qryUser_Rechner" check to see if that's updateable...

 

[jdraw]

For anybody following the thread:

I found a link to Martin Green's Audit Trail.

 

[daikaio94]

I will try to update it but I don't have the time today. I'll test it tomorrow and let you know the results!

@jdraw: thanks, that is the link i couldn't use in post#1