Problems creating an Auditing facility

[DeanL]

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

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

Private Sub Form_BeforeUpda te(Cancel As Integer)

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

End Sub

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

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

iQA/AwUBQ958qYechKq OuFEgEQINHQCgjI Q5CxuI8Pon5k0TV jFPyyJNyNIAoIfZ
nUiOZkcnFMrIpgV cNEh2wFZk
=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.

[DeanL]

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

[steve.minnaar]

Use the AfterUpdate event of the control being audited

Private LocationAudit_A fterUpdate()
Me!LocationAudi t1 = Now
Me!LocationAudi t2 = CurrentUser
End Sub
*** Sent via Developersdex http://www.developersdex.com ***