Getting Audit Trail info through code

[gmazza via AccessMonster.com]

Hey there,
I have an Audit Trail working for my system right now but I now have more
complex data, and am having trouble where to find what dataset the change was
made on. I need to find this out through code.
For example, I am writing a field name and the value it was changed to and
from to my Audit table.
To find the exact table it came from, etc, is easy as I was only dealing with
1 table and 1 key.
Now I have datasets with multiple tables and multiple keys so even though I
can still Audit the changes to specific fields, how can I tell where the
changes have been made.

In other words, a field on a form has a control source, how do I find out
where that field is coming from and what they key is. I can do it by looking,
but can I get that information through code.
Any and all help is appreciated.

 

[David H]

That gets extremely tricky, if not impossible, when you're dealing with
joined tables as there's no way to tell from which table the field that was
changed was pulled from. For example, if for some reason you have two tables
that are joined together and each has a field with the same name - NAME - but
only one field is pulled from in the form's recordsource, you probably can't
identify from which table it was pulled from. You could try explicitly
providing the table name in the control's field source as in
[tableName].[fieldName], however I don't know if Access will remove the
[tableName] on the grounds that its not neccessary. (Why should I show you
the table name, if there's only one field with that name?).

Personally, if I were implementing this. I would go with code that cycles
through the controls on the form (checking for those that have a ControlTypes
that can be databound (http://msdn.microsoft.com/en-us/library/bb216739.aspx)
and then capture the control name and value in demited pairs and then write
the result to a text file along with date/time information and other what not
as in...

DateTime=2009-4-22 4:00:12
PM,Form=frmPersonnelChange,Type=OldValue,FirstName=John,LastName=Smith,Status=Hired

DateTime=2009-4-22 4:00:12
PM,Form=frmPersonnelChange,Type=NewValue,FirstName=John,LastName=Smith,Status=Fired

By going with a text file, you can capture the information without having to
deal with database bloat. By using nested delimiters, you can then use the
Split() function to break apart the string into the component pairs and then
again to get to the values. That way you can work with the data if needed in
Access or Excel. You'll want to explicitly name the value since the order in
which the values appear in the string may change if you add or delete
controls from the form.

 

[Allen Browne]

Examine:

a) The ControlSource of the control to find out the field name.
(If it starts with = it's an expression, not a field. If the control is
unbound, it will be a zero-length string.)

b) Now you know the field name, examine that field of the form's Recordset.
You can then ask for the SourceTable property of the field.
The SourceTable will be blank if the field is not from a table (e.g. if it
is a calculated field in a query.)

c) It's a bit more confusing if the source query is aliased. (JET itself
even gets confused here sometimes, with queries stacked on other queries,
where the table occurs more than once.)

Here's a basic example, looking where a text box named txtCity gets its data
from:

Dim strField As string
Dim strTable As String
strField = Me.txtCity.ControlSource
If strField = "" Then
debug.print "unbound"
ElseIf strField Like "=*" Then
debug.Print "Bound to an expression"
Else
strTable = Me.Recordset.Fields(strField).SourceTable
If strTable = "" Then
debug.print "Bound to a calculated field"
Else
debug.print "Bound to [" & strTable & "].[" & strField & "]"
End If
End If

 

[gmazza via AccessMonster.com]

Thanks Allen and David.
I see through your example Allen, that is the way to see where a control gets
its data from, but to dig more, as in the control coming from a query with
multiple tables, is impossible like David said I take it.
Let me know if you can add anything more to what I said, otherwise that gives
me the start I need.
Thanks again gentlemen.

Examine:

a) The ControlSource of the control to find out the field name.
(If it starts with = it's an expression, not a field. If the control is
unbound, it will be a zero-length string.)

b) Now you know the field name, examine that field of the form's Recordset.
You can then ask for the SourceTable property of the field.
The SourceTable will be blank if the field is not from a table (e.g. if it
is a calculated field in a query.)

c) It's a bit more confusing if the source query is aliased. (JET itself
even gets confused here sometimes, with queries stacked on other queries,
where the table occurs more than once.)

Here's a basic example, looking where a text box named txtCity gets its data
from:

Dim strField As string
Dim strTable As String
strField = Me.txtCity.ControlSource
If strField = "" Then
debug.print "unbound"
ElseIf strField Like "=*" Then
debug.Print "Bound to an expression"
Else
strTable = Me.Recordset.Fields(strField).SourceTable
If strTable = "" Then
debug.print "Bound to a calculated field"
Else
debug.print "Bound to [" & strTable & "].[" & strField & "]"
End If
End If

Hey there,
I have an Audit Trail working for my system right now but I now have more

[quoted text clipped - 16 lines]

but can I get that information through code.
Any and all help is appreciated.

 

[David H]

..SourceTable property ... hmmm ... learn something new everyday.

Examine:

a) The ControlSource of the control to find out the field name.
(If it starts with = it's an expression, not a field. If the control is
unbound, it will be a zero-length string.)

b) Now you know the field name, examine that field of the form's Recordset.
You can then ask for the SourceTable property of the field.
The SourceTable will be blank if the field is not from a table (e.g. if it
is a calculated field in a query.)

c) It's a bit more confusing if the source query is aliased. (JET itself
even gets confused here sometimes, with queries stacked on other queries,
where the table occurs more than once.)

Here's a basic example, looking where a text box named txtCity gets its data
from:

Dim strField As string
Dim strTable As String
strField = Me.txtCity.ControlSource
If strField = "" Then
debug.print "unbound"
ElseIf strField Like "=*" Then
debug.Print "Bound to an expression"
Else
strTable = Me.Recordset.Fields(strField).SourceTable
If strTable = "" Then
debug.print "Bound to a calculated field"
Else
debug.print "Bound to [" & strTable & "].[" & strField & "]"
End If
End If