Create a report that highlights updates to records 1

[cdgeer]

After using a form to update or create records in a table... I would like to run a report that lists all the records AND highlights the newly added ones and any changes that were just made to any fields in any preexisting records. Does anyone know how to do this? I'm lost! Any help would be wonderfull!!

Example of Fields used are:

LastName, FirstName, JobTitle, Supervisor

 

[dhookom]

You can't do this without some sort of auditing of edits and additions. Are you storing any type of audit history?

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

You will need a method of indicating which records have been changed. I would add another field to your table, maybe ChangeDate. Highlight records based on the value in your ChangDate field.


Randy

 

[MichaelRed]

Follow Duanes advice.

There are procedures in rthese fora / faqs (see Transaction Log for Ms. Access" in the faq section.) illustrating the generation / creation of audit trails. They are not -particularly for MS Access- foolproof, but are workable for the general user community of MS Access users.

Given the appropiate audit trail of changes to records, does not, however, make the job of reporting the changes either easy or even practical. Since a true 'audit trail' will extend from it's inception to the time of use, you need to delimit the listing of the audits in some manner.

Maiintaining the audit trail itself is an issue, as in the typical active database, hte audit trail will be one of the most rapidly growing recordsets in the database. To keep track of who made what change - and when they made it requires that each change be recorded at the table and field level along with the username and date-time stamp of the change. Further, please note that in an MS Access application (with the Jet db engine). The only changes which can be captured are tose made via Forms. Queries and manual edits of the database do not generate events which permit the capture of data changes. thus a first step is to basically deny access to any objects except forms to the entire user community. Also, note that design changes are not subject to capture in any autonomus manner.

If you proceed with the development of the report (including its pre-requsits), I would be somewhat interested in following the development process and seeing the finished project.



MichaelRed

 

[cdgeer]

Thanks to All!! I had a feeling it would be an Audit trail thing. I have never ventured into that arena. If I do keep a log of all changes made since inception, maybe I could just run a report limited to changes made on the current date. That is really all that is needed for now.


Thanks again!!!

 

[cdgeer]

I just tried to implement the code from Allen Browne's "Creating an Audit Log" in FAQs but get an error that I can't seem to fix. How is NetworkUserName()picked up? Can anyone help?

sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser ) " & _
"SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " & sTable & ".* " & _
"FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
**THIS IS HIGHLIGHTED: db.Execute sSQL, dbFailOnError
End If
AuditEditBegin = True

Exit_AuditEditBegin:
Set db = Nothing
Exit Function


Resume Exit_AuditEditBegin
End Function

 

[dhookom]

NetworkUserName() is probably a user-defined function you must include in your modules in your MDB.

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Before I click debug, I get Syntax error msg. that says,

"missing operator in query expression (200901NonEmployee.RecordID = 141)" which is in the WHERE statement. I can't understand what operator is missing. Any ideas anyone?

 

[PHV]

Replace all:
" & sTable & "
with:
[" & sTable & "]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdgeer]

PHV,

Thanks!!! That's the ticket!