Copy Continuous Form Subform

[Polariman]

I have a main form (job form) with a materials list (continous form subfrom). Each record in the subform record is an item in the materials list including item, quantity, color, cost, etc. The user has a combo box to call that materials list an "estimate" or "order"

I want to have a button to copy that same materials list (all of the items in the continuos form under that job number in the main form). For instance, the list might be an "estimate" and I want the user to easily copy that materials list and call it an "order".

If this is done with an append query, I can't quite figure it out. What do you think.

 

[seaport]

This is more than just copying subform records. I figure that the following steps are needed.
1. Create a new record in the job table (say, tblJobs) and retrieve the primary key (say, JobID) and store it in a variable NewJobID.
2. Copy the subform records from the existing estimate Job to the new job. The append query in vb code will be like

"insert into tblMaterials(JobID, MaterialName) " & _
"select " & NewJobID & ", MaterialName from " & _
"tblMaterials where JobID=" & ExistingJobID

3. Requery the main form so the new job record is in the form recordset.
4. Move to the new job record with the code

me.recordset.findfirst "JobID=" & NewJobID

Hope this helps.

Seaport

 

[Polariman]

How do you define that new variable?

Also

Would that code go into the append query definition or the code of the button?

Thanks.

 

[seaport]

For Q1:How do you define that new variable?

If the job ID is an autonumber, you can get it in VB code using DMax function, like

NewJobID=dmax("JobID","tblJobs")

For Q2: Would that code go into the append query definition or the code of the button?

the code for the button_click() event.

seaport