Audit Trail with Multivalued fields

[Accessrookie2013]

hello,


I have created a database for incident reports. I have been asked to track changes to each report. I have a "Report" table with multiple fields containing lookup vaules. I used Allen Browne's Audit trail and I keep getting an Run-time error 3825 telling me

"SELECT * cannot be used in an INSERT INTO query when the source or destination table contains a multi-valued field"

Is there a way to track changes to a form/table with multi-valued fields?

Please help!

[Missinglinq]

It's probably never wise to say 'never,' vis-à-vis the ability or non-ability to do something, in Access, but the very fact that Allen knows that his hack won't work with Multi-Valued Fields

Limitations of this solution include:
......
Does not work with Multi-Valued Fields (Access 2007.)

and has not addressed the issue, would make me tend to think that there is no reasonable way to do so. The majority of experienced developers still eschew the use of this new type of field, for a number of reasons. Also, per Allen:

The relational structure behind multi-valued fields is not accessible.

Developers will find it harder to handle complex data types. Any generic procedure you write must be capable of handling fields that contain fields. You cannot use a table with a multi-valued field in an IN clause (i.e. in another database.)

While a form is being edited, a control's OldValue should indicate the value that will be restored if you undo the edit. The OldValue of a combo bound to a multi-valued field yields the current value, not the old value.

Problems with filters based on the fields not working

Of course, Allen may have just felt that the functionality gained by doing this simply wasn't worth the effort involved to figure it out!

Linq ;0)>

[Accessrookie2013]

Thank you for responding, I now realize he knows, as after re-reading his instructions it is the last point to consider when using his code. But being new to access, I thought it referred to fields with multiple sets of information like a distribution list with multiple names. Now I know what they are. So if there is not a reasonable work around, what is the best way to replace the multi-valued fields and have the same or similar results? These fields are used as drop down options for the users for consistancy in the "Incident Report" form. Some of them consist of 3 options where others contain over 50 options.

[orange]

It may be better/easier to understand if you showed us a few examples. However, whatever your actual values are, you could put them into a separate table.

For example, let's suppose you have something (Call Frequency --dreamed up example) with 4 possible values
rarely, seldom, frequent, always

tblCallFrequency
CallFreqId autonumber PK
CallFreqDesc text

with records
1 rarely
2 seldom
3 frequently
4 always

You would use this table as the Rowsource of your combo.

One table for each "dimension" or "attribute" or whatever term you use.
For each combo box, use the relevant table as the Rowsource.

This set up follows Normalization rules.
I don't understand M$oft and the introduction of Multivalued fields.??? Causes more issues; runs against best practices.....

[Accessrookie2013]

Tables: Report wich has look ups for the following tables, Loctation, Incident type, employee, Incident code, Case Status and Status codes.

The Report table contains fields using the other tables as lookups.

My form needing to be audited is "Form1" where the employee enters their Incident Report. that form is based on the "report"table. So each new record has an auto ID. Each of those tables relates to a field in the main Report table and is on

[Accessrookie2013]

Form1 as combo boxes.

Over all your example is similar to what I am trying to do.

By linking directly to the other table as a row source will that create a multi-valued field?

[orange]

No. You create a multivalued field in the design of your table. The table(s) I'm proposing do not have multivalued fields -- there is an Id field and a Description /Name field.

[Accessrookie2013]

OK so in my table "report" my field "Incident Type" is a text box or a list Box?
So how do I link the Incident type table to the "report" table on the Form? How do I manipulate/set up the form to link the two? With the exception of my report table my other tables are set up as you indicate.

How to I set up my "report table" fields = text or listbox?

ONce that is complete, how do I link the tables on the form?

[orange]

(Text) Fields in tables are text box.
You do NOT want multivalued or Table Lookup at the the table field level.

Why do you call your table "Report"??
When you write to your audit log, do you store the ID of one of the comboboxes, or do you store the description??

Are you familiar at all with Lookup Tables? I am recommending simple lookup tables. I would not suggest that you use Hierarchical Lookup Tables (one major table with hierarchies within).
A simple lookup table is a means of storing commonly used terms, and referencing a number/ID to refer to that term instead of retyping the term. This can reduce data entry, and it greatly reduces the chance of typos/spelling errors. It also "forces" consistency in spelling and terminology.

[Accessrookie2013]

I call the table "report' as this is an "Incident Report" database for Security officers.

I am not sure what the audit log is. In my Audit tables from the A. Brown audit trail I have AudTble that basically is a copy of hte "report table".

I am not familiar with Lookup tables
OK so my form is what the Officer filles out.
The report table is just a place to collect all the various date input into the report. AM I going about that wrong? Will the "Form" hold all the data together on its own?

With exception of the "report"table, all other tables have Auto ID and description
My employee table and Faciltiy Table have auto ID and multiple fields

City, State, Area, Country

Employee has ID, Name, E-mail, City, State, Country, Active, Inactive Access level for logging in.