Duplicating a record automatically

[CMooreJr]

Hey all! I have 2 forms, "frmNewItemForm" and "frmChangeItemForm" They hold pretty much the exact same info. What the client needs is this...

They need to open the New Item form and input the data. The data is detailed info on the item, such as NetCost, ModelNumber, etc.

Now, if the cost, etc changes, they want to enter the Change Item form. Right now I have it prompting for the Model Number and it finds the record. They can then make whatever changes are necessary.

THE PROBLEM--They want original record to NOT CHANGE. They need to pull reports to see the changes on a particular model number monthly, yearly, etc. A model may change more than once, so I guess I need the change Item form to bring up a duplicate of the data so the prior data is not changed. What is the best way to do this, or is there a better way?

Thanks for all your help!

 

[WildHare]

Rather than think in terms of "duplicates", which send shivers up and down the spines of dedicated RDBMS fanatics, perhaps it might clear things a bit to think in terms of a "baseline" item, and a "versioned" item - keep your baseline items in a separate table, and keep the versioned (e.g., changed) versions of them in another table - using a sequencing number or something to differentiate them.

E.g., Item in Baseline Table : Model X-15-3.00
Item In Version table: Model X-15-3.01, 3.02, 3.03.....
and so on.

When you want to make a change, you will have an APPEND query that grabs the record from the BASELINE table, and APPENDS it to the Version table, after making necessary changes to the keying data to put the new version in.

It sounds complex, but with one query and about four lines of code, that's all you've got to do.

Jim




Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[CMooreJr]

cool...that's what I need Jim...I can handle the query just fine to pull over the needed data, but I'm a complete novice at VB, so can you help with the Code?

I Think it would work great to have what you have suggested. I would like to have 2 boxes however,
"Model Number" and "revision", then I won't have to use wildcards to pull the data for the reports.

So If I'm understanding correctly, I will have a select query to pull the data in the first place. Then after changing the needed fields, I can have a button that runs an APPEND query to append it to another table. My only prob, as I mentioned in para. 1, is how will I get the version to change from 1 to 2, 2 to 3, etc. when they need to make another change...

Thanks for everything!

 

[WildHare]

Unless someone else jumps in with your solution, I'll try to mock something up over the w/end and get it here - gotta run at the moment.

JMH

Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm

 

[CMooreJr]

Thanks Wild...I would appreciate it!

 

[WildHare]

My weekend ended up more social than I expected - plus the work environment is hectic at the moment - if I wasn't waiting for a 600 MB db to compact, I wouldn't have the time to check here..

I'll try to get something up for you if you still need it...

Jim

Don't be sexist - Broads hate that.
Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm