Relationships

[GailFialho]

Hello Anyone . . .
Thank You in advance for any time you might spend helping me with this problem.
Here it is:

First set of tables:
ContractTable
KeyField = ContractID and 30 or so other fields.
ContractHistoryTable
Keyfield = ContractHistoryID
ForeignKey = ContractID and same 30 or so other fields
Contract form.BeforeUpdate
Runs query to append all data in that form, if record is changed, to the ContractHistory table, thereby tracking all changes to every record.

ContractTable related to ContractHistoryTable one to many with Referential Integrity, Cascade Update and Delete, ContractTable LEFT JOIN ContractHistoryTable ON ContractTable.ContractID = ContractHistoryTable.ContractHistoryID;

This setup works perfectly.
Here's my problem:
I have another set of tables for "Contract Opportunities" which is setup EXACTLY the same but the word "Contract" is changed to "Watch" (as in Watch List). All changes to records in WatchTable need to be tracked in the WatchHistoryTable.
Everything is THE SAME!!!!
The append query for the Watch table changes will not run unless I REMOVE the relationship between the WatchTable and the WatchHistoryTable
Message: Records in WatchHistoryTable are required to have a related record in WatchTable.
I've tracked the process, using code breaks, and checked that all the fields contained data.
I have checked over everything I can think of. It's all the same.
I'M TOTALLY STUMPED!

 

[Jack Cowley]

Let me see if I understand what you are doing... If you have a record in your Contract Table and you edit a field in that record you copy the entire original record to the history table leaving the modified record in the Contract table. If you change that record again an hour later you send the previous record to the history table leaving the modified record. Now you have two records in the History table for the two changes you made. Have I got this right?

If I have it right I wonder why can't have a memo field in the record and keep a record of all the changes made to the record? The memo field would have the date the record was changed, the field changed with the before and after values. This would eliminate the History table as well as all the code associated with maintaining it. If this would work for you then I would suggest you look at the article below:

How to create an Audit Trail

As to why your append query is not working I am sorry to say I am as stumped as you...

hth,
Jack

 

[GailFialho]

Thank You

Your understanding of the situation is correct. These records are "Published" as a report every month. Eventually I want to be able to request this report for any published date. Without a complete record of every field as of that date, I don't see how I would be able to do this. I think parsing it out of a memo field would be quite challenging. Ialso would like to create a DELTA report showing change differences from last month.
However, I kept at it and seem to have solved the problem.
The contract form had some of the table fields in a subform which contained the BeforeUpdate code. The Watch form had all of the table fields in the parent form. I'm not sure why but I believe the actual record must become "established" (for lack of a better description) when I move to the subform and in the case of the Watch for, where I never move to a suform, the record isn't actually established yet. I placed some of the fields in the parent Watch form and the rest in a subform and the append query works. HOWEVER!!!!!!!
I seem to have created a new problem that has me tearing my hair out, but since I don't want to impose any further on your generosity, I'll post it as another question under the title "You can't perform that action right now."
Thank You again

 

[Jack Cowley]

You are absolutely correct in assuming that parsing a memo field would be a drag and your solution to the problem seems to be the best one given your requirements. Now all you have to do is sort out your current problem!

Good luck!

Jack