Duplicate Record and subform

[thewesties]

I have a database that tracks progress payment billings for jobs. Here is the setup of the database in a nutshell...
I'll only include a few (relevant) fields from each table

tbl_Job.JobID (Autonumber)
tbl_Job.Revision_Num (Integer)
tbl_Job.CustName
tbl_Job.JobName

tbl_LineItem.LineItemID (Autonumber)
tbl_LineItem.JobID (Relationship w/ table above)
tbl_LineItem.LineNumber
tbl_LineItem.LineDescr
tbl_LineItem.CurrentAmtRequested....

Now...Here's the deal. Some of the jobs we are now running will span several months. The purpose of this DB is to bill Phases of the job as they complete. Now, there may be 40+ tbl_LineItem.LineItemID (tbl_LineItem.LineNumbers) for each tbl_Job.JobID

We want to have all of the line items (LineNumber) included on the first request for Billing. Next time we want to submit a bill, we don't want to have to re-type all 40+ Lines to create a new record

The key to the whole thing is that we will have 1 JobName with several Revision_Num's . Most of the data should be the same except Revision_Num

How do I duplicate records in the main table (tbl_Job) as well as all associated records in the linked table (tbl_LineItem)?

Now, I hope it doesn't sound like I have my head up my... donkey

Thanks for any help you can provide!

 

[SydneyGeek]

Usually with Access you need to copy all the field values, create a new record and re-write those values. There is no built-in "duplicate record" feature.
For the initial duplication, go to this page and download the zip file for Article 51, Duplicating Records. For the subform records, you may need to go into a Recordset, open the subform records, adn create new records with the ID from the latest form main form record.

Denis

 

[mdmilner]

wrong approach?

I think this may be the wrong approach for you. You don't really need to duplicate records, but a selection query will probably give you your information.

First - it's generally always bad to duplicate records in a primary data table. If you're creating an audit trail - that's ok sometimes.

Second - have you looked at queries?

Select * from tblName WHERE JobID = xxxx

This would return all entries matching the same jobID
If you need to get more specific, you can throw a second parameter into the equation such as:

Select * from tblName WHERE JobID = xxxx AND dteAction<#1/1/2005#

You can then use the query results to supply data for use elsewhere, such as generating a bill.

Mike