Copying from Subform???

[gmatriix]

Hello All,

I am trying to copy an entire record into a new record. On the current form it works just fine however, the subform is where I am having the problem. Im trying to refer the code to the subform and it is not finding it. Here is a portion of it. I am really stuck on this......

Private Sub Copybutton_Click()
        'Private Sub btnDuplicate_Click()
        Dim dbs As Database, Rst As Recordset
        Dim F As Form
        Dim scompany As String
        Dim ssSeqNumber As Long
        Set dbs = CurrentDb
        Set Rst = Me.RecordsetClone

        On Error GoTo Err_btnDuplicate_Click

       
        Me.Tag = Me![ProductID]
              scompany = "" & Me!Company

'This is where I having the problem??
              ssSeqNumber = "" & Me![RoutingSubform].Form![SequenceNumber]

With Rst
           .AddNew
              !Company = scompany
              ![RoutingSubform].Form![SequenceNumber] = ssSeqNumber
              
            .Update                     ' Save changes.
           .Move 0, .LastModified
        End With
        Me.Bookmark = Rst.Bookmark

Any Ideas??

Thanks

 

[veraloopy]

'This is where I having the problem??
ssSeqNumber = "" & Me![RoutingSubform].Form![SequenceNumber]

Have you go the reference to the forms in the correct order?
Should be main form then subform i.e.

 ssSeqNumber = [forms]![MainFormName]![SubFormName]![SequenceNumber]

The only other thing is using a ! after 'Me' - should be a full stop 'Me.'

 

[gmatriix]

Thanks,

This is what I put in.

ssSeqNumber = "" & [Forms]![product]![RoutingSubform]![SequenceNumber]

![Forms]![product]![RoutingSubform]![SequenceNumber] = ssSeqNumber

I'm still getting "Item not found in this collection"

Any Ideas?

 

[liamfitz]

I'm not sure why you've provided two lines of code, where you've reversed where the object data and stored data ( variable ) should go ?
It's always variable first ( ssSeqNumber in your case ), then where to find that information, in this case a field within a recordset/subform. Have you tried retrieving the field's value from the subform itself i.e the textbox or whatever it is output to from the recordsource. Do you see where I'm 'going' with this idea ?
I think it would eb something like ssSeqNumber = Me.txtnameoftextbox.value. see if that works, and take it from there logically.

 

[gmatriix]

Hello liamfitz,

Thanks for your response. I do understand what you mean however, here is the entire code so that you can see what I'm talking about.

What I am trying to accomplish is:
Copy entire record, including the records in subform and paste them into a new record. So the new record is just a duplicate of the record copied

When I try to get and copy records from the subform is where I'm getting stuck.:banghead:

Here is the code (so far)

Private Sub Copybutton_Click()
       
        Dim dbs As Database, Rst As Recordset
        Dim F As Form
        Dim scompany As String
        Dim saddress As String
        Dim scity As String
        Dim sstate As String
        Dim szip As String
        Dim sattention As String
        Dim sinquiryno As String
        Dim smachinesize As String
        Dim sProductDescription As String
        Dim sCutoffFace As String
        Dim sGrade As String
        Dim sMetalType As String
        Dim sShape As String
        Dim sDiameter As Long
        Dim sSolidTube As String
        Dim sPartLength As Long
        Dim sOneTimeSetupCost As Long
        Dim sOneTimeToolingCost As Long
        Dim sNotes As String
        Dim sqty1 As Long
        Dim sqty2 As Long
        Dim sqty3 As Long
        Dim sqty4 As Long
        Dim sqty5 As Long
        Dim sqty6 As Long
Dim sOtherMaterialCost As Long
Dim sAmortizedToolingCost As Long
Dim sVendorCostpc As Long
Dim stool1 As Long
Dim stool2 As Long
Dim stool3 As Long
Dim stool4 As Long
Dim stool5 As Long
Dim stool6 As Long
Dim ssetup1 As Long
Dim ssetup2 As Long
Dim ssetup3 As Long
Dim ssetup4 As Long
Dim ssetup5 As Long
Dim ssetup6 As Long
Dim sAmortizedSetupCost As Long
Dim sprofit As Long
Dim ssubcontract1 As Long
Dim ssubcontract2 As Long
Dim ssubcontract3 As Long
Dim ssubcontract4 As Long
Dim ssubcontract5 As Long
Dim ssubcontract6 As Long
Dim smaterialcost As Long
Dim scommission1 As Long
Dim scommission2 As Long
Dim scommission3 As Long
Dim scommission4 As Long
Dim scommission5 As Long
Dim scommission6 As Long
Dim sprofit1 As Long
Dim sprofit2 As Long
Dim sprofit3 As Long
Dim sprofit4 As Long
Dim sprofit5 As Long
Dim sprofit6 As Long
Dim stotal1 As Long
Dim stotal2 As Long
Dim stotal3 As Long
Dim stotal4 As Long
Dim stotal5 As Long
Dim stotal6 As Long
Dim smaterialcostcwt As Long
Dim scommission As Long
Dim sfreight1 As Long
Dim sfreight2 As Long
Dim sfreight3 As Long
Dim sfreight4 As Long
Dim sfreight5 As Long
Dim sfreight6 As Long
Dim sfreightamort As Long
Dim sfreightcwtft As Long
Dim smachinecost As Long
Dim spcsbar As String
Dim spartlengthwithend As Long
Dim sweightperk As Long
Dim sRevisionLevel As String
Dim sQuoteamt1 As Long
Dim sQuoteamt2 As Long
Dim sQuoteamt3 As Long
Dim sQuoteamt4 As Long
Dim sQuoteamt5 As Long
Dim sQuoteamt6 As Long
Dim smaterialcost21 As Long
Dim smaterialcost31 As Long
Dim smaterialcost2desc As String
Dim smaterialcost3desc As String
Dim smaterialcost22 As Long
Dim smaterialcost23 As Long
Dim smaterialcost24 As Long
Dim smaterialcost25 As Long
Dim smaterialcost26 As Long
Dim smaterialcost32 As Long
Dim smaterialcost33 As Long
Dim smaterialcost34 As Long
Dim smaterialcost35 As Long
Dim smaterialcost36 As Long
Dim ssubcontract21 As Long
Dim ssubcontract22 As Long
Dim ssubcontract23 As Long
Dim ssubcontract24 As Long
Dim ssubcontract25 As Long
Dim ssubcontract26 As Long
Dim sVendorCostpc2 As Long
Dim ssubcontractdesc As String
Dim ssubcontract2desc As String
Dim sfeetperk As Long
Dim sInitials As String
Dim scomment As String
Dim slengthfeet As Long
Dim sBluePrint1 As String
Dim sBluePrint2 As String
Dim sBluePrint3 As String
Dim sBluePrint4 As String
Dim sBluePrint5 As String
Dim sBluePrint6 As String
Dim sBluePrint7 As String
Dim sBluePrint8 As String
Dim ssSeqNumber As String

        
        

        ' Return Database variable pointing to current database.
        Set dbs = CurrentDb
        Set Rst = Me.RecordsetClone

        On Error GoTo Err_btnDuplicate_Click

        ' Tag property to be used later by the append query.
        Me.Tag = Me![ProductID]
              scompany = "" & Me!Company
              saddress = "" & Me!Address
              scity = "" & Me!City
              sstate = "" & Me!State
              szip = "" & Me!Zip
              sattention = "" & Me!Attention
              sinquiryno = "" & Me!InquiryNo
              smachinesize = "" & Me!MachineSize
              sProductDescription = "" & Me![Product Description]
            
sCutoffFace = "" & Me!CutoffFace
sGrade = "" & Me!Grade
sMetalType = "" & Me!MetalType
sShape = "" & Me!Shape
sDiameter = "" & Me!Diameter
sSolidTube = "" & Me!SolidTube
sPartLength = "" & Me!PartLength
sOneTimeSetupCost = "" & Me!OneTimeSetupCost
sOneTimeToolingCost = "" & Me!OneTimeToolingCost
sNotes = "" & Me!Notes
sqty1 = "" & Me!qty1
sqty2 = "" & Me!qty2
sqty3 = "" & Me!qty3
sqty4 = "" & Me!qty4
sqty5 = "" & Me!qty5
sqty6 = "" & Me!qty6
sOtherMaterialCost = "" & Me!OtherMaterialCost
sAmortizedToolingCost = "" & Me!AmortizedToolingCost
sVendorCostpc = "" & Me!VendorCostpc
stool1 = "" & Me!tool1
stool2 = "" & Me!tool2
stool3 = "" & Me!tool3
stool4 = "" & Me!tool4
stool5 = "" & Me!tool5
stool6 = "" & Me!tool6
ssetup1 = "" & Me!setup1
ssetup2 = "" & Me!setup2
ssetup3 = "" & Me!setup3
ssetup4 = "" & Me!setup4
ssetup5 = "" & Me!setup5
ssetup6 = "" & Me!setup6
sAmortizedSetupCost = "" & Me!AmortizedSetupCost
sprofit = "" & Me!profit
ssubcontract1 = "" & Me!subcontract1
ssubcontract2 = "" & Me!subcontract2
ssubcontract3 = "" & Me!subcontract3
ssubcontract4 = "" & Me!subcontract4
ssubcontract5 = "" & Me!subcontract5
ssubcontract6 = "" & Me!subcontract6
smaterialcost = "" & Me!materialcost
scommission1 = "" & Me!commission1
scommission2 = "" & Me!commission2
scommission3 = "" & Me!commission3
scommission4 = "" & Me!commission4
scommission5 = "" & Me!commission5
scommission6 = "" & Me!commission6
sprofit1 = "" & Me!profit1
sprofit2 = "" & Me!profit2
sprofit3 = "" & Me!profit3
sprofit4 = "" & Me!profit4
sprofit5 = "" & Me!profit5
sprofit6 = "" & Me!profit6
stotal1 = "" & Me!total1
stotal2 = "" & Me!total2
stotal3 = "" & Me!total3
stotal4 = "" & Me!total4
stotal5 = "" & Me!total5
stotal6 = "" & Me!total6
smaterialcostcwt = "" & Me!materialcostcwt
scommission = "" & Me!commission
sfreight1 = "" & Me!freight1
sfreight2 = "" & Me!freight2
sfreight3 = "" & Me!freight3
sfreight4 = "" & Me!freight4
sfreight5 = "" & Me!freight5
sfreight6 = "" & Me!freight6
sfreightamort = "" & Me!freightamort
sfreightcwtft = "" & Me!freightcwtft
smachinecost = "" & Me!machinecost
spcsbar = "" & Me!pcsbar
spartlengthwithend = "" & Me!partlengthwithend
sweightperk = "" & Me!weightperk
sRevisionLevel = "" & Me!RevisionLevel
sQuoteamt1 = "" & Me!Quoteamt1
sQuoteamt2 = "" & Me!Quoteamt2
sQuoteamt3 = "" & Me!Quoteamt3
sQuoteamt4 = "" & Me!Quoteamt4
sQuoteamt5 = "" & Me!Quoteamt5
sQuoteamt6 = "" & Me!Quoteamt6
smaterialcost21 = "" & Me!materialcost21
smaterialcost31 = "" & Me!materialcost31
smaterialcost2desc = "" & Me!materialcost2desc
smaterialcost3desc = "" & Me!materialcost3desc
smaterialcost22 = "" & Me!materialcost22
smaterialcost23 = "" & Me!materialcost23
smaterialcost24 = "" & Me!materialcost24
smaterialcost25 = "" & Me!materialcost25
smaterialcost26 = "" & Me!materialcost26
smaterialcost32 = "" & Me!materialcost32
smaterialcost33 = "" & Me!materialcost33
smaterialcost34 = "" & Me!materialcost34
smaterialcost35 = "" & Me!materialcost35
smaterialcost36 = "" & Me!materialcost36
ssubcontract21 = "" & Me!subcontract21
ssubcontract22 = "" & Me!subcontract22
ssubcontract23 = "" & Me!subcontract23
ssubcontract24 = "" & Me!subcontract24
ssubcontract25 = "" & Me!subcontract25
ssubcontract26 = "" & Me!subcontract26
sVendorCostpc2 = "" & Me!VendorCostpc2
ssubcontractdesc = "" & Me!subcontractdesc
ssubcontract2desc = "" & Me!subcontract2desc
sfeetperk = "" & Me!Feetperk
sInitials = "" & Me!Initials
scomment = "" & Me!comment
slengthfeet = "" & Me!lengthfeet
sBluePrint1 = "" & Me!BluePrint1
sBluePrint2 = "" & Me!BluePrint2
sBluePrint3 = "" & Me!BluePrint3
sBluePrint4 = "" & Me!BluePrint4
sBluePrint5 = "" & Me!BluePrint5
sBluePrint6 = "" & Me!BluePrint6
sBluePrint7 = "" & Me!BluePrint7
sBluePrint8 = "" & Me!BluePrint8
ssSeqNumber = "" & Me![RoutingSubform].Form![SequenceDescription] ' [COLOR="Red"]Here is where I am having problem[/COLOR]



        'X = Me!company
        ' Add new record to end of Recordset object.
        With Rst
           .AddNew
              !Company = scompany
              !Address = saddress
              !City = scity
              !State = sstate
              !Zip = szip
              !Attention = sattention
              !InquiryNo = sinquiryno
              !MachineSize = smachinesize
              ![Product Description] = sProductDescription
            !CutoffFace = sCutoffFace
            !Grade = sGrade
            !MetalType = sMetalType
            !Shape = sShape
            !Diameter = sDiameter
            !SolidTube = sSolidTube
            !PartLength = sPartLength
            !OneTimeSetupCost = sOneTimeSetupCost
            !OneTimeToolingCost = sOneTimeToolingCost
            !Notes = sNotes
!qty1 = sqty1
!qty2 = sqty2
!qty3 = sqty3
!qty4 = sqty4
!qty5 = sqty5
!qty6 = sqty6
!OtherMaterialCost = sOtherMaterialCost
!AmortizedToolingCost = sAmortizedToolingCost
!VendorCostpc = sVendorCostpc
!tool1 = stool1
!tool2 = stool2
!tool3 = stool3
!tool4 = stool4
!tool5 = stool5
!tool6 = stool6
!setup1 = ssetup1
!setup2 = ssetup2
!setup3 = ssetup3
!setup4 = ssetup4
!setup5 = ssetup5
!setup6 = ssetup6
!AmortizedSetupCost = sAmortizedSetupCost
!profit = sprofit
!subcontract1 = ssubcontract1
!subcontract2 = ssubcontract2
!subcontract3 = ssubcontract3
!subcontract4 = ssubcontract4
!subcontract5 = ssubcontract5
!subcontract6 = ssubcontract6
!materialcost = smaterialcost
!commission1 = scommission1
!commission2 = scommission2
!commission3 = scommission3
!commission4 = scommission4
!commission5 = scommission5
!commission6 = scommission6
!profit1 = sprofit1
!profit2 = sprofit2
!profit3 = sprofit3
!profit4 = sprofit4
!profit5 = sprofit5
!profit6 = sprofit6
!total1 = stotal1
!total2 = stotal2
!total3 = stotal3
!total4 = stotal4
!total5 = stotal5
!total6 = stotal6
!materialcostcwt = smaterialcostcwt
!commission = scommission
!freight1 = sfreight1
!freight2 = sfreight2
!freight3 = sfreight3
!freight4 = sfreight4
!freight5 = sfreight5
!freight6 = sfreight6
!freightamort = sfreightamort
!freightcwtft = sfreightcwtft
!machinecost = smachinecost
!pcsbar = spcsbar
!partlengthwithend = spartlengthwithend
!weightperk = sweightperk
!RevisionLevel = sRevisionLevel
!Quoteamt1 = sQuoteamt1
!Quoteamt2 = sQuoteamt2
!Quoteamt3 = sQuoteamt3
!Quoteamt4 = sQuoteamt4
!Quoteamt5 = sQuoteamt5
!Quoteamt6 = sQuoteamt6
!materialcost21 = smaterialcost21
!materialcost31 = smaterialcost31
!materialcost2desc = smaterialcost2desc
!materialcost3desc = smaterialcost3desc
!materialcost22 = smaterialcost22
!materialcost23 = smaterialcost23
!materialcost24 = smaterialcost24
!materialcost25 = smaterialcost25
!materialcost26 = smaterialcost26
!materialcost32 = smaterialcost32
!materialcost33 = smaterialcost33
!materialcost34 = smaterialcost34
!materialcost35 = smaterialcost35
!materialcost36 = smaterialcost36
!subcontract21 = ssubcontract21
!subcontract22 = ssubcontract22
!subcontract23 = ssubcontract23
!subcontract24 = ssubcontract24
!subcontract25 = ssubcontract25
!subcontract26 = ssubcontract26
!VendorCostpc2 = sVendorCostpc2
!subcontractdesc = ssubcontractdesc
!subcontract2desc = ssubcontract2desc
!Feetperk = sfeetperk
!Initials = sInitials
!comment = scomment
!lengthfeet = slengthfeet
!BluePrint1 = sBluePrint1
!BluePrint2 = sBluePrint2
!BluePrint3 = sBluePrint3
!BluePrint4 = sBluePrint4
!BluePrint5 = sBluePrint5
!BluePrint6 = sBluePrint6
!BluePrint7 = sBluePrint7
!BluePrint8 = sBluePrint8
![RoutingSubform].Form![SequenceDescription] = ssSeqNumber  [COLOR="Red"]'here is where I'm having problem also [/COLOR]
              
            .Update                     ' Save changes.
           .Move 0, .LastModified
        End With
        Me.Bookmark = Rst.Bookmark

Exit_btnduplicate_Click:
        Me.Refresh
        Exit Sub

Err_btnDuplicate_Click:
        MsgBox Error$
        Resume Exit_btnduplicate_Click:
End Sub

Sorry for the long code....but this is where I am.

Any Ideas?

 

[liamfitz]

First of all - you've got some serious variable creating going-on there ! Why not use Arrays, where you're storing the same field value in variables which all relate to one another e.g. in your case, Dim myvaraiablename1 as String/Integer or whatever, why not Dim myvaraiablename(8) as Array. Then create lopp or conditional branching, to store your field values from recordset/subform. On the issue with retrieving field values from subform, try the following. Ignore subform for now - don't load it at run-time, or set recordsource etc.
Create the same recordset you would use for the subform, just programatically, and now try to store the field values THIS sql retrieves ( using loops etc. ) in your array variables. Now add one or two text boxes ( temporarily to form, and output the stored values. If this works, you know the issue is with the subform references, design. Do you follow ?

 

[gmatriix]

Well,

I believe I understand what you are saying but I am still a beginner. Still learning. I will keep trying though.

thanks