Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 31st, 2003, 01:51 PM
Authorized User
 
Join Date: Dec 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sample History Database Needed

Does anyone have a good example of a history-tracking database? Old value, new value, when, and by whom? Thanks for any leads. Jim
 
Old December 31st, 2003, 02:01 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have an audit table and function as follows. You could modify to include the trapping of old value and new value:

tblAudit
ID [primary key]
AuditDate [Date]
UserName [text]
Action [text]
Item_ID [integer]

Create a query called qryAudit which is a direct view to tblAudit.

This would go on the form which is being tracked provided you add a new records through a button called btnAdd.

Private Sub btnAdd_Click()
Dim x as Integer
Dim ItemNumber As Long

On Error Resume Next
ItemNumber = Me![ID] 'this is provided your pk for the row is called ID
X = AuditUpdate ("Add New Record", ItemNumber)
End Sub

If you only need to know when they were changed and what was changed then use the following:

Private Sub Form_AfterUpdate()
Dim x
Dim ItemNumber As Long

On Error Resume Next
ItemNumber = Me![ID]
x = AuditUpdate ("Modify Record", ItemNumber)
End Sub

X calls AuditUpdate which would be something like the following in a new
module:

Function AuditUpdate((Flag As String, ItemNumber)
Dim db As Database
Dim SqlString As String

On Error Resume Next

Set db = CurrentDb()

SqlString = "INSERT INTO qryAudit (AuditDate, UserName, Action, Item_ID) values ('" & Date & "','" & CurrentUser() & "','" & Flag & "'," & ItemNumber & ")"
db.Execute (SqlString)

End Function

Watch out for word wrapping!

HTH,

Beth M
 
Old December 31st, 2003, 03:12 PM
Authorized User
 
Join Date: Dec 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Howdy, Beth. Thanks for the quick response. I'll see what I can do with your suggestion. Jim





Similar Threads
Thread Thread Starter Forum Replies Last Post
AdventureWorks Sample Database swmukesh BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 May 26th, 2010 02:27 PM
CourseManagement sample database VirtualShaman BOOK: Professional C# 2008 ISBN: 978-0-470-19137-8 1 February 10th, 2009 07:39 AM
Sample SQL Database gsforfree SQL Server 2000 1 June 29th, 2004 10:29 AM
Sample database needed smokin Access 12 March 1st, 2004 12:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.