Append or Insert into Main Form and Subform

[Hulm1]

Once a project is "won" I need to create an Order Confirmation. I want to do this by duplicating the information contained in the "frmEstimate" and it's subform "frmEstimateProductServices" which is a continuous form with many records (items).

I have seen Allen Browne's code for duplicating a form and subform record, however, my need is subtly different in that I am passing all the details over to a different Main form and subform. I am not sure what I would need to change that.

Could someone advise?

 

[MajP]

Are these bound or unbound forms? Since it is forms and subforms I assume this is bound.

I like highly respect Allen Browne, but I think I have seen the code you are referring to and it seems overly complicated to me to do a relatively straight forward task. It was meant to be generic for setting defaults.

If these forms are bound or the data is persistent the term "Passing records to a form" has no real meaning in database applications. Your are not "passing" records but basing your recordsource/recordset on the same records.

You will need to provide a lot more information
1)Bound or unbound?
2)Table and queries to use
3) What are the other forms

My most likely solution is to simply open the form subform to a filter of the same records, or possibly base it on the same recordset.

 

[Hulm1]

These are bound forms.
Five key tables are:
Enquiry
Estimate
EstimateVersion
EstimateProductService
zmtProductService

Relationship:
one Enquiry to many Estimate
one Estimate to many EstimateVersion
One EstimateVersion to Many EstimateProductService
One EstimateProductService to Many zmtProductService

I have nested forms as follows:
frmEnquiry
frmEstimate (subform of frmEnquiry)
frmEstimateVersion (subform of frmEstimate)
Within frmEstimateVersion I have several subforms specific to elements of the quotation. All are based on slightly different queries on table EstimateProductService, hooked by the field "quotecategory"
The queries include for example qrySubformMobilisation, qrySubformDemobilisation, qrySubformMedia...etc.

When we "win" a project, I planned to stamp the details of the above tables to two new tables: OrderConfirmation and OrderConfirmationDetails (one Order Confirmation to many OrderConfirmationDetails.

The key details would be:
EnquiryID
EstimateID (plus some form details from this tbale including site address, CompanyID (the customer))
EstimateVersionID (plus some details from this table including rental period)
EstimateProductService (all the details grouped together representing what has actually been ordered)

The other complication is that I need to create a new record for table "Project" and then stamp the Order Confirmation and the Estimate with the ProjectID. The relationship of Project is as follows

One Enquiry to Many Project
One Project to Many Estimate
One Project to Many Order Confirmation

(in other words, one enquiry can lead to several projects. One Project can have several variations (Estimates and order confirmations stamped as "variation".

I have struggled a bit with this structure!

I have attached a zipped version of the file. It has moved on a little since you last saw it. Though not that much!


Form frmEstimateVersion is a Subform of