Re: Audit Trail Problem

The problem with that approach is that the old values are not available in
Form_AfterUpdate.

You could use Form_BeforeUpdate, but there is no guarantee when that event
fires that the write will succeed. It could fail due to validation failure,
duplicate indexes, or a write conflict, for example. Therefore the solution
requires stashing the values somewhere (temp table, array, ...) in
Form_BeforeUpdate , and then reading them in Form_AfterUpdate.

SQL Server is a big topic on its own. There are heaps of resources on that:
books, courses, knowledgebase and msdn articles, and separate newsgroups for
questions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"KatK" <KatK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:825F09A7-746D-403F-A301-4EF9B44ED2F4@xxxxxxxxxxxxxxxx
> Thanks for your reply. How can I go about doing this? Do you have any
> resources you recommend I read to get me started?
>
> I'm guessing since you suggested SQL Sever tables, this is the best
> direction to go. However, is there another route to get the new and
> original
> number values from a form passed to the audit trail table? Below is the
> code
> I'm using to get the user type, user id, table changed, and time changed
> inserted into the audit table. As I mentioned before, I tried using
> ctl.oldvalue & ctl.value properties but couldn't get them to work. Thanks
> for all your help!
>
>
> **********************************
> Public Function AuditTrail(FormName As String)
>
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
>
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecordset("tblAuditTrail")
> With rst
> .AddNew
> .Fields("User") = UserID
> .Fields("UserType") = UserType
> .Fields("TimeChanged") = Now()
> .Fields("TableChanged") = Form_MainMenu.cb_TableSelection
> .Update
> End With
>
> ExitPoint:
> On Error Resume Next
> rst.Close
> Set rst = Nothing
> Set dbs = Nothing
>
> End Function
> *************************************
>
> "Allen Browne" wrote:
>
>> KatK, have you considered using SQL Sever tables instead of JET tables?
>>
>> You can still use the Access interface (well, it will need
>> modifications),
>> but use the SQL Server engine to provide transaction logs, or use
>> triggers
>> to generate just the details you want.
>>
>>
>> "KatK" <KatK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:D563C765-0C63-4414-8B5C-C6D8DF467F69@xxxxxxxxxxxxxxxx
>> >I need to create an audit trail for my database. I have referred to
>> >Allen
>> > Browne's audit trail and read all posts regarding audit trails.
>> > However,
>> > my
>> > database has 200+ tables and forms; therefore, Allen Browne's audit
>> > trail
>> > example is not logical for me to use since I would have to create two
>> > tables
>> > for each table I already have (please correct me if I am wrong).
>> >
>> > For my database I have gotten part of my audit trail to work correctly.
>> > My
>> > username, time changed, table changed, and user type are working
>> > correctly;
>> > however, I can not get the previous value and new value to work. I
>> > have
>> > tried using the OldValue and Value properties, but I can't get them to
>> > be
>> > entered into my audit table.
>> >
>> > Any help would be wonderful! Thanks in advance!


.