History Table and combo boxes

[EdA]

I am creating a history table to track field level changes made on a form.
My form has some combo boxes which store an ID field as the value but display
a "nice value" on the form (e.g. store OccupationID but show Occupation
Description).

In my history table, I want to store the nice name and not the ID. I also
need to be able to get the OLD "nice name" and the NEW "nice name". I am
thinking I will need to somehow create a SQL query to do a lookup for the
"nice name" in order to do this but that seems complicated. I am looking for
any ideas how to implement this with or without the SQL query idea.

 

[Jeanette Cunningham]

Hi EdA,
the easy way is to keep the history table as is and use a history query to
show the "nice values".
Unless you have a particularly strong reason for changing the history table,
the query gives you all the advantages without all the extra work.
In the query designer, you use the history table and the lookup tables for
occupation id etc.
You may find that you need outer joins between the history table and the
lookup tables.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[EdA]

Thanks Jeanette. The problem I have with such a solution is that my history
table is not a copy of the main tables structure. I am only storing the
following in the history table:

ID
FieldName
OldValue
NewValue
DateChanged
UserID
ChangeReason
MainTablePrimaryKey

So, I have a link back to the record that was changed, but I cannot produce
a query that will allow me to lookup the appropriate information. My
solution works great for text fields or comboboxes that use value lists.

My end goal with this history table is to run monthly reports that show all
the changes to a particular record (for reporting back to our customers).