Highlighting Audits (1 Viewer)

[Treason]

Hi,

I have a problem that "should" be solved easily, but I lack the knowledge. I have a Audit System in use. I wish to create a report that will either put an asterick in front of the record, bold, or italic the record that has a value in the [Audit_Field]. Something to the effect of, "If [audit_field] Is Not Null, then italic" or maybe "If [audit_field] Is Not Null, then insert "*" in [Textbox1]" I suppose I am looking for something to add in the criteria section of my query, but I will try anything.

I have no real VB experiance so excuse my syntax
Thanx

Treason

 

[The_Doc_Man]

If you have a field-level audit, this could become a bit messy very soon. Depending on how often data gets updated, you will eventually have all fields starred (maybe) unless you put a time limit on the age of the marks.

IF all entries in your forms and reports came directly from tables, not from queries, then it would be possible to write a generic function to do this - a function that you could call in the form On_Current event and in the report On_Format event.

The event code would have to include a loop stepping through each bound control in the form or report section to determine if there is an entry for the corresponding field in your audit history table. It would do so by calling your generic function that would have as its arguments the name of the table and the name of the field AS THEY WOULD APPEAR IN THE AUDIT TABLE ENTRIES! Plus you would need the record identifier or discriminator, whatever it is, to see if that particular record was ever modified.

You would use a DLookup call in your function to search your audit table for an entry for the source table, source field, and indicated record identifier; or maybe just a DCount and see if the count is greater than 1. You would return True or False, which would be all you need to support the feature you described.

My problems with this suggestion are

(a) you claim to be relatively unfamiliar with VBA and

(b) the assumption I made - that all forms and reports come directly from tables - is a very bad assumption. I would bet that MANY forms and tables involve a query rather than a direct table source. Particularly if JOINs are involved.

Condition (b) wouldn't stop me from checking for the audit entries, but tracing back fields to their recordsources in tables underlying queries is not a task for a novice or even for a timid journeyman VBA'er.

Please understand, I offer no disrespect, but even with my own extensive programming background I would hesitate to do this one without a lot of design work. For those not comfortable in the VBA environment, the task might be truly overwhelming.

 

[Treason]

I can't see why this would be so difficult. My reports are based on queries, so I don't know how much is relevent to my project. Each record in my main table, (on which the report is not based) has an audit field. Lets say Table1 has fields [ID],[Audit] and [asterick] for example. All I would like to do is insert an asterick or anything into the [asterick] field, when the [Audit] field is not null.

Maybe it is more complicated than I think it is.

 

[David R]

Let me see if I understand correctly..

You don't have field-level auditing, but instead a single [audit_field] within your main record? The [audit_field] is either Null or has something in it - presumably the name of the field changed?

If that's the case, then yes, you should be able to put a field in your report's underlying query: IIF(IsNull([audit_field]),"","*"), and then put a space for that newly created field on your report at the edge. You could also do this in the report itself with an unbound field, I'm not sure which way is 'more efficient'.

If you have a separate [audit_table] which stores a record every time your record changes in any field, and stores what field is changed, then the problem is a little more complicated.

 

[Treason]

you are the man

David it worked like a charm, and I really should've figgured that out myself. =/
thanx alot...
Now i have bigger fish to fry