Audit Columns (1 Viewer)

Tupacmoche · Oct 30, 2018

Hi Table Masters,

I want to add an audit feature to a table. I was thinking of putting it in the after update event code of the field. I have put code there to turn a labels visibility to on when that filed Not IsNull. This is the code:

If Not IsNull(Me.txtWriter.Value) Then
Me.TxtW.Visible = True
Else
Me.TxtW.Visible = False
End If

Now, I want to add code that will update a column called AuditWrite with a time stamp something like this: Update AuditWriter = Now() but only for the current record. How do, I limit it to the current row?

pbaldy · Oct 30, 2018

Something like:

Me.AuditWriter = Now()

would only apply to the record with focus.

Tupacmoche · Oct 30, 2018

When, I use Me.AuditWriter = Now(), I get an error. Method or data member not found. AuditWriter is a field in the current Record Source and not a textbox.

pbaldy · Oct 30, 2018

I'd give it a textbox; you can hide it once it works the way you want.

Tupacmoche · Oct 30, 2018

It worked with the text box but oddly after making the first textbox I saw the other six table columns when I started typing Me. ect . Anyway it works. Thanks!

theDBguy · Oct 30, 2018

If the field is part of the record source of the form, you could also try using the bang (!) operator. For example,

Me!FieldName=Now()

Hope it helps...

pbaldy · Oct 30, 2018

Tupacmoche said:
Thanks!

Happy to help!

Tupacmoche · Oct 30, 2018

Follow-up question on this thread. This is the code now:

Private Sub txtWriter_AfterUpdate()
If Not IsNull(Me.txtWriter.Value) Then
Me.TxtW.Visible = True
Me.AuditWrite.Value = Now()
Else
Me.TxtW.Visible = False
End If
End Sub

Now the mssql 2008 R2 table gets stamped once the user enters data into the field. What, I additionally want to do is prevent the use from changing the dates once entered. I know that I can set the Enabled property to False but is there a more elegant way to do this?

pbaldy · Oct 30, 2018

If it's just certain fields you want to lock, that's probably how I'd do it. If it's the entire record, I'd probably toggle the AllowEdits property of the form (and probably AllowDeletions).

Cronk · Oct 31, 2018

Or

Code:

private sub form_current()
   me.auditWrite.locked  = not (isnull(me.auditWrite))
end sub

Audit Columns (1 Viewer)

Tupacmoche

Registered User.

pbaldy

Wino Moderator

Tupacmoche

Registered User.

pbaldy

Wino Moderator

Tupacmoche

Registered User.

theDBguy

I’m here to help

pbaldy

Wino Moderator

Tupacmoche

Registered User.

pbaldy

Wino Moderator

Cronk

Registered User.

Similar threads

Users who are viewing this thread