Difficult one :) - Logging changes in my database made

Hi,

I have a database where different users would be able to change the value of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my "tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
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.

"ant1983" wrote in message
...

I have a database where different users would be able to change the value
of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above
that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in
table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my
"tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted
for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

As Allen points out, you can build a audit function into your Access
database.

Another approach would be to use a more robust backend (e.g., SQL-Server),
which provides this kind of auditing trail.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"ant1983" wrote in message
...
Hi,

I have a database where different users would be able to change the value
of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above
that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in
table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my
"tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted
for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

Hi Allen,

I followed your steps exactly as you pointed out but am getting an error
when i close my form (after i have made the change)

The error reads:

"Ambiguous name detected: AuditEditBegin" and it stops at the BeforeUpdate
Line...



"Allen Browne" wrote:

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
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.

"ant1983" wrote in message
...

I have a database where different users would be able to change the value
of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above
that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in
table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my
"tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted
for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

On Wed, 19 Mar 2008 08:42:03 -0700, ant1983
wrote:

"Ambiguous name detected: AuditEditBegin" and it stops at the BeforeUpdate
Line...


That suggests that you have two instances of

Sub AuditEditBegin

or two labels

AuditEditBegin:

somewhere in your code. Use the binoculars to search for them. Edit the code
so that there isn't an ambiguous subroutine name or label and you should be
fine.
--

John W. Vinson [MVP]

Allen,

I tried this fix with my own db and while performing the debug/compile
function I get an error "user defined type not defined" and it highlights the
section of code that reads:

Dim db As DAO.database ' Current database
Dim sSQL As String ' Append query.

is DAO.database supposed to be where I put the name of my database? If so, I
tried that and my database file name has an apostrophe in it ... is that
causing the problem and should I rename my db so that isn't an issue?

Thank you,

Joseph

"Allen Browne" wrote:

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
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.

"ant1983" wrote in message
...

I have a database where different users would be able to change the value
of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above
that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in
table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my
"tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted
for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

More than likely, you don't have a reference to DAO set in your database.
You do that by going to Tools References and scroll down to Microsoft DAO
3.6 Object Library. Check the box and click OK.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"joseph" wrote in message
...
Allen,

I tried this fix with my own db and while performing the debug/compile
function I get an error "user defined type not defined" and it highlights
the
section of code that reads:

Dim db As DAO.database ' Current database
Dim sSQL As String ' Append query.

is DAO.database supposed to be where I put the name of my database? If so,
I
tried that and my database file name has an apostrophe in it ... is that
causing the problem and should I rename my db so that isn't an issue?

Thank you,

Joseph

"Allen Browne" wrote:

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
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.

"ant1983" wrote in message
...

I have a database where different users would be able to change the
value
of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this.
(For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have
to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above
that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the
record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in
table
"tblBooking" the value of the field "dateBookingDate" was changed from
"1
March 2008" to "8 March 2008" - how would i capture this in my
"tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be
prompted
for
their UserName and Password and the database would have to remember
this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is
quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne

Hi Allen
I coppied your code and followed instructions on creating an audit trail
when Itry to run it I get the error message

"Compile error Su or Function not defined"
When I clcik ok it is taking me to call AuditEnd in the BeforeUpdate Sub

What am I doing wrong

Linda

"Allen Browne" wrote:

See:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
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.

"ant1983" wrote in message
...

I have a database where different users would be able to change the value
of
certain fields.

I would like to capture the name of that user, the date and time of the
change and what was changed in a table so that i can report on this. (For
auditing purposes)

I have no idea how to go about this. I would imagine that i would have to
create the following tables:

tblUser
- autUserID
- txtUserName
- txtUserPassword

tblChanges
- autChangeID
- txtUserName (Linked with above table)
- dateChangeDate
- txtTableNameChanged (this would probably be the table that where the
change happened)
- txtFieldNameChanged (this would prob be the field in the table above
that
changed)
- txtRecordIDChanged (thsi would probably be the PRIMARY KEY of the record
in the table that was changed)

My questions a

1 - Am i on the right track in terms of the structure as above?
2 - If so, how would i capture the old vs the new data - e.g. if in
table
"tblBooking" the value of the field "dateBookingDate" was changed from "1
March 2008" to "8 March 2008" - how would i capture this in my
"tblChanges"?
Would i have to add two extra fields; "txtOldValue" and "txtNewValue"?
3 - Lastly, how would my database know which user made the change? I
imagine that if the user start's their FE they would have to be prompted
for
their UserName and Password and the database would have to remember this
throughout the session? - How would i implement this?

Many thanks for this. I know ask a lot of questions and that it is quite
in-dept but i knwo this post will help a lot of users.

Thanks,

Wayne