By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,314 Members | 1,751 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,314 IT Pros & Developers. It's quick & easy.

Problems creating an Auditing facility

P: n/a
Hi All,

I need some help regarding the creation of an auditing facility with
Access 97. First off, let me say that the following design is not my
idea but I'm in the unfortunate position of trying to implement it
somehow.

First, the user(s) require that certain fields be audited to see who
the data was last changed by and when it was changed. They initially
wanted this data to be in the ControlTip Text so they could see it by
simply hovering over the relevant control. After a large amount of
"heated" debates on the matter, they decided that they wanted some
hidden controls (a couple of text boxes and labels) that would appear
when the control had focus and then vanish when the next control is
selected. This in itself is easy enough but the text boxes where the
data is to appear is supposed to be disabled to stop anyone updating
the text with other data (although why anyone would do this escapes
me). With my limited knowledge of Access, I've set the controls to
appear when the control has focus and using the After Update event I've
set the text boxes to receive data from code that runs once the
original control is updated. One of the 2 text boxes is set to receive
the output from the Now() function and the other from the CurrentUser()
function (both built in functions). The text boxes are attached to
relevant columns in the table/query that feeds the form but I continue
to get an error that says the control can't be updated unless it has
focus.

I've tried to set focus to the relevant control before the update but
to no avail. I've even tried leaving the text box visible, enabled and
everything else but I'm currently banging my head against the
proverbial brick wall so any help you can offer would be most welcome.

If there is anyone who has another idea to record the person/time who
last updated a record then I'd be very grateful.

Many thanks in advance.

Dean...

Jan 30 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I usually use the Form's BeforeUpdate event. E.g.:

Private Sub Form_BeforeUpdate(Cancel As Integer)

' User & Updated have to be part of the form's recordset.
Me.User = CurrentUser()
Me.Updated = Now()

End Sub

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ958qYechKqOuFEgEQINHQCgjIQ5CxuI8Pon5k0TVjFPyy JNyNIAoIfZ
nUiOZkcnFMrIpgVcNEh2wFZk
=Ibqr
-----END PGP SIGNATURE-----
DeanL wrote:
Hi All,

I need some help regarding the creation of an auditing facility with
Access 97. First off, let me say that the following design is not my
idea but I'm in the unfortunate position of trying to implement it
somehow.

First, the user(s) require that certain fields be audited to see who
the data was last changed by and when it was changed. They initially
wanted this data to be in the ControlTip Text so they could see it by
simply hovering over the relevant control. After a large amount of
"heated" debates on the matter, they decided that they wanted some
hidden controls (a couple of text boxes and labels) that would appear
when the control had focus and then vanish when the next control is
selected. This in itself is easy enough but the text boxes where the
data is to appear is supposed to be disabled to stop anyone updating
the text with other data (although why anyone would do this escapes
me). With my limited knowledge of Access, I've set the controls to
appear when the control has focus and using the After Update event I've
set the text boxes to receive data from code that runs once the
original control is updated. One of the 2 text boxes is set to receive
the output from the Now() function and the other from the CurrentUser()
function (both built in functions). The text boxes are attached to
relevant columns in the table/query that feeds the form but I continue
to get an error that says the control can't be updated unless it has
focus.

I've tried to set focus to the relevant control before the update but
to no avail. I've even tried leaving the text box visible, enabled and
everything else but I'm currently banging my head against the
proverbial brick wall so any help you can offer would be most welcome.

If there is anyone who has another idea to record the person/time who
last updated a record then I'd be very grateful.

Jan 30 '06 #2

P: n/a
Thanks MG,

Unfortunately, there are several fields on the form that require
auditing with the date changed and the username. I've created new
fields in the tables that feed the form (e.g. the Location field has
two other fields that I need updated called LocationAudit1 and
LocationAudit2 where the first records the date and the second records
the username when someone changes the Location field for that
particular record). The before update event would be fine if there was
only one item that needed auditing.

Dean...

Jan 30 '06 #3

P: n/a
Use the AfterUpdate event of the control being audited

Private LocationAudit_AfterUpdate()
Me!LocationAudit1 = Now
Me!LocationAudit2 = CurrentUser
End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Jan 31 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.