Copy Form Record and Subform Records Problem

[LJtechnical]

Hi All,

I have a form "Quotes" and Subfom "Quote Content" I am trying to set up a method of revising the quotation i.e. copying the record to a new rec and changing the unique quote number(suffixing with R1 etc). The latter is quite simple, its the copying record bit thats causing me a headache, I have somthing that kind of works for the main form but comes up with paste errors, see below code:


Case 1 'Revise Current Quote
Dim temp1 As Integer
temp1 = Me!QNum
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Me!QNum = temp1
Me!Revision = Me!Revision + 1
Me![Status Indicator] = 3
If QuoteType = 1 Then
Me!QuotationNo = "UK" & Me!QNum & "R" & Me!Revision
ElseIf QuoteType = 2 Then
Me!QuotationNo = "EX" & Me!QNum & "R" & Me!Revision
End If
End Select

I think the fact that i have calculated fields on both form and subform is causing a problem.

I have looked at using INSERT INTO sql from other threads but to no joy. Is this the better way to go, because I would also like to copy the contents of the subform.

Any tips welcomed.

 

[PhilH123]

Hi LJ,
How about setting up some temporary variables, setting them to the values in the current record, moving to the new record and then setting the fields to equal the variables you have stored the values in ?

There are plenty of ways around this, but I think the one above is the most simple if there aren't too many fields to cope with, otherwise it could be a bit long winded!

Regards,
Phil.

 

[LJtechnical]

Thanks Phil,

Unfortunately there is a mass of fields, which as you say makes the procedure slightly long winded. In the end I simply created two append queries with the quote number as the criteria.

One query for the main form and another for the subform, works like a charm, I always overlook the use of queries for obvious applications.

Thanks

LJT