Duplicate Record in table plus all related tables.

[CFP - Russell]

Hello everyone. I've just ran into a major stumbling block in building my database.

I have a table JobInfo with 5 tables linked to it via the JobInfoID (autonumber). The 5 tables are control sources for the 5 subforms on the JobInfo form.

I create workorders off of the JobInfo table. Right now I have a button called "create wo" which runs an append query that appends the current record in JobInfo into the WorkOrder table.

I chose to use an append query because my boss wants to keep the JobInfo information to serve as a template to create the workorders. We also need workorder history, so having a new record for each workorder is ideal.

The problem I am having is that all 5 tables/subforms are related to table JobInfo via the JobInfoID, if information in the subform for one of our 100 workorders is changed, its changed in the JobInfo form as well.

I need a way to append not only
JobInfo --> Workorders

but I also need to append:

table1 --> WOtable1
table2 --> WOtable2
table3 --> WOtable3
.......

and the relationships that exist via the JobInfoID.

I am stuck big time and once this is completed the project will be over. I am willing to compensate someone monitarily for a solution to this problem.

Any kind of reply would be greatly appreciated. I thank you in advance for your time.

Thanks,
Russell

 

[mission2java_78]

If I get you correctly you want to have a history of all your subform data as well?

Thanks,
Jon

 

[CFP - Russell]

Yes that is correct.

 

[CFP - Russell]

I forgot to add, the history has to be unique to the appended record so that it does not change in JobInfo

 

[mission2java_78]

I forgot to add, the history has to be unique to the appended record so that it dos not change in JobInfo

Hi Russell,

Your problem is NOT unique...although implementation wise it is not so simple. There are many ways to do what you are trying to accomplish. Pat H. suggested a method a while back...however, and I'm sorry Pat, I did not like it. The only reason I did not like it was because it involved way too many steps as well as tables to complete history that I needed. Don't get me wrong her method was not incorrect but was not suitable for what I was looking for.

Approach your problem from a different perspective...you sound like me a few months back till it hit me . Your boss wanted history...so did mine...your boss wants sub history so did mine...my boss wanted even deeper than that (sub sub history)..yours probably doesn't need or care for that. Anyhow..this idea of all these seperate tables on your end is going to do one thing to you...frustrate you...and kick your a** inside out . There is way too much maintenance involved with this since this may mean you will need to purge your data using queries during a given amount of time. And what about accessing the history...thats going to be a pain since the history is located in several tables.

So...I propose the following...which I have used and has been very effective. You ONLY need 2 tables to accomplish this. One table holds your job info data the other table can be called JobInfoVersion. JobInfoVersion will have two fields:
JobInfoVersionID (auto) AND JobInfoVersion (long / small / integer).

Now this is what happens your main table JobInfo will need to have a JobInfoVersionID (as a foreign key). When you first create a JobInfo record, you MUST create a JobInfoVersion record and grab that JobInfoVersionID number and store it in the foreign key field of your jobinfoversion table. You also want to set the active revision level to 1 in the beginning.

You ONLY do this on NEW JobInfo records...not existing ones...so
whenever you need the current JobInfo records (the ones you are interested in you SQL out what you want and set your Forms RecordSource propoperty. i.e:

SELECT * FROM JobInfo INNER JOIN JobInfoVersion
ON JobInfoVersion.JobInfoVersionID = JobInfo.JobInfoVersionID
AND JobInfoVersion.ActiveRevLevel =JobInfo.RevLevel ORDER BY JobInfo.ID

So you ask what the hell does this SQL Stmnt do..well it ensures that at all times you are only viewing the ACTIVE jobinfo's...you or your boss does not care about history UNLESS they want to revert back.

Now what's all this about reverting back? Well what I mean by this is EVERY record whether history or not goes in your ONE main table of JobInfo. Tell your boss you DONT need two tables and you're asking for trouble if you do. That main table will be easily SQLed rather than opening recordsets from various other tables. Now...your boss says I want to go back to a previous job information record...say revision level one. Well using SQL you will be able to snatch that record...because it sits in your main table with the SAME JobInfoVersionID and the ActiveRevLevel.

It seems like a lot but its a very effective approach when it comes to history. Your subrecords you dont have to worry about..since you only write code to recreate for the new JobInfo ID. As long as you have enforced referential integrity they link back to the parent.

Peace,

Jon

 

[CFP - Jason]

Hey, I work with Russell. First off thank you for the response. I had a few questions about that code section you sent. How does this method tackle list boxes, combo boxs, etc. The way we use it is that the work order goes out with all the info that is standard , then they crew writes in the variables on the report, they come back and fill it in. this also accounts for changes in the standard data that might have been changed for that one job. Now how would this work with the proposed method? also I am not to sure of the use or properties of foriegn keys. does this create a "oneway" relationship?
Thanks
Jason

 

[mission2java_78]

Hey, I work with Russell. First off thank you for the response. I had a few questions about that code section you sent. How does this method tackle list boxes, combo boxs, etc. The way we use it is that the work order goes out with all the info that is standard , then they crew writes in the variables on the report, they come back and fill it in. this also accounts for changes in the standard data that might have been changed for that one job. Now how would this work with the proposed method? also I am not to sure of the use or properties of foriegn keys. does this create a "oneway" relationship?
Thanks
Jason

 

[mission2java_78]

Hey, I work with Russell. First off thank you for the response. I had a few questions about that code section you sent. How does this method tackle list boxes, combo boxs, etc. The way we use it is that the work order goes out with all the info that is standard , then they crew writes in the variables on the report, they come back and fill it in. this also accounts for changes in the standard data that might have been changed for that one job. Now how would this work with the proposed method? also I am not to sure of the use or properties of foriegn keys. does this create a "oneway" relationship?
Thanks
Jason

I just wrote 10 paragraphs and the browser ate it all.

 

[mission2java_78]

Hey, I work with Russell. First off thank you for the response. I had a few questions about that code section you sent. How does this method tackle list boxes, combo boxs, etc. The way we use it is that the work order goes out with all the info that is standard , then they crew writes in the variables on the report, they come back and fill it in. this also accounts for changes in the standard data that might have been changed for that one job. Now how would this work with the proposed method? also I am not to sure of the use or properties of foriegn keys. does this create a "oneway" relationship?
Thanks
Jason

Im seriously not going to re-state what I posted...the mod or whomever responsible for this site..took me to a page where it says I did not follow a valid link..so I click the back button and my entire message is gone.

Sorry guys...if you want contract help i have after hours and we can discuss rates. Your problem as I've stated earlier is something I've had in the past.

Jon

 

[CFP - Russell]

errrhhhhhhhh, thats happend to me before. Now I always copy the text I write to the clipboard first.