Copy Form and Subform Contents to another Form and Subform 1

[uncleG]

In short I am trying to convert an RFQ to a Purchase Order.
The forms involved are:
Source Form- frmRFQ
Source Subform- frmRFQLines
Destination Form- frmPO
Destination Subform- frmPOLines
The above 4 forms are based on 4 different tables.
Both Subforms are based on querys and located on a tab.
A2K Format

I have tried this based on the code in thread702-876420 by TheAceMan1.
Currently the Form (MainPairs) portioned is transfered and a new record created as expected however the

subform copy does not occur and an error message "can not find 'LNumber' refered to in your expression"

is returned. Any idea what I am doing wrong?

Code:
Public Sub XferData()
Dim Ary, srcFrm As Form, desFrm As Form, desName As String
Dim MainPairs As String, subPairs As String
Dim x As Integer, y As Integer

desName = "frmPO" 'this was "DestinationFormName"

If Not IsOpenForm(desName) Then
DoCmd.OpenForm desName
End If

Set desFrm = Forms(desName)
DoCmd.RunCommand acCmdRecordsGoToNew

'SFCN = SourceFormControlName
'DFCN = DestinationFormControlName
'Add as many pairs as desired in the format shown.
'MainPairs = "SFCN,DFCN,SFCN,DFCN" 'MainForm
'subPairs = "SFCN,DFCN,SFCN,DFCN" 'subForm

MainPairs ="LocId1,LocId1,quAtt,quAtt,lupFOB,lupFOB,quQuoteNo,quCustRef,quVendRef,quVendRef,quTerm,quTerm"

subPairs
="LNumber,LNumber,ItemID,ItemID,AKA,AKA,Combiv,Combiv,Quantity,Quantity,PriceEa,PriceEa,LotNo,LotN

o,Availability,Availability,LeadTime,LeadTime,Rev,Rev,Notes,Notes"

For x = 1 To 2 '1 for MainForm, 2 for subForm
If x = 1 Then
Set srcFrm = Me
Ary = Split(MainPairs, ",")
Else
Set srcFrm = Me!frmRFQLines.Form 'this was Me!subFormName.Form
Ary = Split(subPairs, ",")
End If

'Transfer here
For y = LBound(Ary) To UBound(Ary) - 1 Step 2
desFrm(Ary(y + 1)) = srcFrm(Ary)
Next
Next

Set desFrm = Nothing
Set srcFrm = Nothing

End Sub
Thanks, UncleG

 

[uncleG]

I have been watching this question slide back page by page for the last 12 days. I am still not able to get the subpairs to transfer. Does Any One have any thoughts on how to get this to Work? This is most certianly way over my head.
Thanks, Uncle G

 

[PHV]

When x=2 you should change desFrm too.

But, why not simply use 2 append queries ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[uncleG]

Thanks for your response PH. The append queries is the way I do it now but was trying to learn how this works. As I studied it I felt something was missing as I could not understand how the sub pairs were actually referenced and passsed. Thanks for confirming my suspicion.
UncleG