Dennis please see my coments below:

"Dennis" wrote:

hollyyloh

I use the approach that Stephen suggested and it works pretty well.

I have a public variable for Name1 (line1), Name2 (line2), Address1,
Address2, City, St, Zip, and an Name Change Flag.

In the On Current event, I set the Name Change Flag to false and save the
orginal address values in the above variables. Then in each field's After
Update event, I check to see if he address had change. Thinking about it,
doing it in the form's Before Update event would probably be better only
because you have all of the logic in one place rather than spread out over
the entire form. In any case, if any of the fields change, I set the Name
Change Flag to true.

In the form's Before Update, I check the the Name Change Flag. If it is
true, then I write out the Address history record.


***What do you mean by "I write our the Address history record?"


The only issue with this is all of your reports will point to the current
address record (assuming you are storing just the customer number). Will
this be an issue?

***If I understand what you are saying, yes this is an issue, and is the
actual problem I am trying to address.


Dennis

"hollyylloh" wrote:

Stephen,

Thank you, that is an interesting idea. I will keep that as a possibility as
I make the decision on this.

"Stephen Raftery" wrote:

A way of maintaining an archive of old data might be to create a table that
has the same structure as your data table, with extra fields for timestamp
and userID.
Whenever the data in the table changes, you trigger a BeforeUpdate event
which saves the old data to the archive table, and then makes the change.
The user does not see this happen, but you have a complete archive of all
orevious changes to the table.

Stephen



"hollyylloh" wrote:

Just to be clear, I am not looking for an explanation of how relational
databases work in this question, I have been creating relational databases
for many years, please read closer.

I have a database that needs to store information as it was originally
entered. So, for example, normally I would enter client information (name,
address etc) in one table, and specific transaction information in another.
Reports would print out with the appropriate information for now. In the
future, let's say the clients address changes. When I go in and change the
clients address, I am actually changing it for historical purposed as well,
unintentionally of course, and this is usually acceptable. If for example I
need to keep the historical transaction intact as it was originally entered
(with the now old address), I would need to inactivate the old client
information and create basically a new client to store the new address. This
of course is difficult to get the user to do, they will just change the old
address to the new address.

One way to go about this is to force the user to create a new client by not
allowing edits or creating a routine that aids the user in the process.

Another way to go about this is to create what is really a flat file for all
the information that needs to be historically accurate. The client table thus
becomes more of an extended drop down menu for entering multiple values into
the main historical table. I really think this is the better way to go about
this. What do you think?

I am interested in additional thoughts on this, thank you in advance.