Button in ms access form copies all fields in a new record from a form and subform. It copies only the first row from a subform

Button in ms access form copies all fields in a new record from a form and subform. It copies only the first row from a subform.

How to solve?

v23 = Forms![Workorders]![Workorder Parts].Form![Combo14].value
v24 = Forms![Workorders]![Workorder Parts].Form![Quantity].value
v25 = Forms![Workorders]![Workorder Parts].Form![ArtNrKlant].value
v26 = Forms![Workorders]![Workorder Parts].Form![HE].value
v27 = Forms![Workorders]![Workorder Parts].Form![UnitPrice].value
v28 = Forms![Workorders]![Workorder Parts].Form![BTW].value
v34 = Forms![Workorders]![Workorder Parts].Form![ICHE].value
v35 = Forms![Workorders]![Workorder Parts].Form![OCVE].value

Open in new window


Full code:
Private Sub copy_fields_Click()
Dim v0 As Variant
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
Dim v5 As Variant
Dim v6 As Variant
Dim v7 As Variant
Dim v8 As Variant
Dim v9 As Variant
Dim v10 As Variant
Dim v11 As Variant
Dim v12 As Variant
Dim v13 As Variant
Dim v14 As Variant
Dim v15 As Variant
Dim v16 As Variant
Dim v17 As Variant
Dim v18 As Variant
Dim v19 As Variant
Dim v20 As Variant
Dim v21 As Variant
Dim v22 As Variant
Dim v23 As Variant
Dim v24 As Variant
Dim v25 As Variant
Dim v26 As Variant
Dim v27 As Variant
Dim v28 As Variant
Dim v29 As Variant
Dim v30 As Variant
Dim v31 As Variant
Dim v32 As Variant
Dim v33 As Variant
Dim v34 As Variant
Dim v35 As Variant


v0 = Me!Customer_EmployeeID.value
v1 = Me!DateReceived.value
v2 = Me!DateRequired.value
v3 = Me!MakeAndModel.value
v4 = Me!ShippingID.value
v5 = Me!EmployeeID.value
v6 = Me!DeliveryNote.value
v7 = Me!DeliveryNotes.value
v8 = Me!PurchaseOrderNumber.value
v9 = Me!Warehouse.value
v10 = Me!Barcode.value
v11 = Me!WGR.value
v12 = Me!Extra.value
v13 = Me!ProblemDescription.value
v14 = Me!SerialNumber.value
v15 = Me!DateFinished.value
v16 = Me!DatePickedUp.value
v17 = Me!SalesTaxRate.value
v18 = Me!DateReceived.value
v19 = Me!DateRequired.value
v20 = Me!DelTimeEarly.value
v21 = Me!Tekst98.value
v22 = Me!Combo58.value
v23 = Forms![Workorders]![Workorder Parts].Form![Combo14].value
v24 = Forms![Workorders]![Workorder Parts].Form![Quantity].value
v25 = Forms![Workorders]![Workorder Parts].Form![ArtNrKlant].value
v26 = Forms![Workorders]![Workorder Parts].Form![HE].value
v27 = Forms![Workorders]![Workorder Parts].Form![UnitPrice].value
v28 = Forms![Workorders]![Workorder Parts].Form![BTW].value
v34 = Forms![Workorders]![Workorder Parts].Form![ICHE].value
v35 = Forms![Workorders]![Workorder Parts].Form![OCVE].value
v29 = Forms![Workorders]![SubPallets].Form![PalletID].value
v30 = Forms![Workorders]![SubPallets].Form![TransactionDate].value
v31 = Forms![Workorders]![SubPallets].Form![DeliveredBy].value
v32 = Forms![Workorders]![SubPallets].Form![DeliveredTo].value
v33 = Forms![Workorders]![SubPallets].Form![Delivered].value

RunCommand acCmdRecordsGoToNew

Me!Customer_EmployeeID = v0
Me!DateReceived = v1
Me!DateRequired = v2
Me!MakeAndModel = v3
Me!ShippingID = v4
Me!EmployeeID = v5
Me!DeliveryNote = v6
Me!DeliveryNotes = v7
Me!PurchaseOrderNumber = v8
Me!Warehouse = v9
Me!Barcode = v10
Me!WGR = v11
Me!Extra = v12
Me!ProblemDescription = v3
Me!SerialNumber = v14
Me!DateFinished = v15
Me!DatePickedUp = v16
Me!SalesTaxRate = v17
Me!DateReceived = v18
Me!DateRequired = v19
Me!DelTimeEarly = v20
Me!Tekst98 = v21
'Me!Combo58 = v22
Forms![Workorders]![Workorder Parts].Form![Combo14] = v23
Forms![Workorders]![Workorder Parts].Form![Quantity] = v24
Forms![Workorders]![Workorder Parts].Form![ArtNrKlant] = v25
Forms![Workorders]![Workorder Parts].Form![HE] = v26
Forms![Workorders]![Workorder Parts].Form![UnitPrice] = v27
Forms![Workorders]![Workorder Parts].Form![BTW] = v28
Forms![Workorders]![Workorder Parts].Form![ICHE] = v34
Forms![Workorders]![Workorder Parts].Form![OCVE] = v35
Forms![Workorders]![SubPallets].Form![PalletID] = v29
Forms![Workorders]![SubPallets].Form![TransactionDate] = v30
Forms![Workorders]![SubPallets].Form![DeliveredBy] = v31
Forms![Workorders]![SubPallets].Form![DeliveredTo] = v32
Forms![Workorders]![SubPallets].Form![Delivered] = v33


Me!Combo58 = DMin("shipID", "ScheduleID", "shipID > " & v22)

Me.Repaint



End Sub

Open in new window

mvdwalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Your code is calling acCmdRecordsGoToNew only once. So it copies only the actual row.

Private Sub CopyAll()

  Dim CurrentData As DAO.Recordset
  
  Set CurrentData= Me.Form.RecordsetClone
  If Not CurrentData.Bof And Not CurrentData.Eof Then
    CurrentData.MoveFirst
    Do While Not CurrentData.Eof
      CopyRow CurrentData
      CurrentData.MoveNext      
    Loop
  End If

  CurrentData.Close
  Set CurrentData= Nothing

End Sub

Private Sub CopyRow(ASource as DAO.Recordset)

  Dim Destination As DAO.Recordset

  Set Destination= CurrentDb.OpenRecordset("yourTable")
  Destination.AddNew
  Destination![Customer_EmployeeID] = ASource ![Customer_EmployeeID]
  Destination![DateReceived] = ASource ![DateReceived]
  Destination![DateRequired] = ASource ![DateRequired]
  Destination.Update  
  Destination.Close
  Set Destination= Nothing

End Sub

Open in new window

Rey Obrero (Capricorn1)Commented:
yes that is one way to copy the form and subform details records

see this link for another way
mvdwalAuthor Commented:
Dear Rey

I tried this code i'll found it http://www.allenbrowne.com/ser-57.html but it did not copy the sub form

VBA Error 3134 Syntax error in INSERT INTO statement

Whats wrong?

On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    Dim strSql1 As String    'SQL statement.
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
            !CustomerID = Me!CustomerID
            !Customer_EmployeeID = Me!Customer_EmployeeID
            !DateReceived = Me!DateReceived
            !DateRequired = Me!DateRequired
            !MakeAndModel = Me!MakeAndModel
            !ShippingID = Me!ShippingID.value
            !EmployeeID = Me!EmployeeID
            !DeliveryNote = Me!DeliveryNote
            !DeliveryNotes = Me!DeliveryNotes
            !PurchaseOrderNumber = Me!PurchaseOrderNumber
            !Warehouse = Me!Warehouse
            !Barcode = Me!Barcode
            !WGR = Me!WGR
            !Extra = Me!Extra
            !ProblemDescription = Me!ProblemDescription
            !SerialNumber = Me!SerialNumber
            !DateFinished = Me!DateFinished
            !DatePickedUp = Me!DatePickedUp
            !SalesTaxRate = Me!SalesTaxRate
            !DateReceived = Me!DateReceived
            !DateRequired = Me!DateRequired
            !DelTimeEarly = Me!DelTimeEarly
            !DelTimeLate = Me!DelTimeLate
            !ShipID = Me!ShipID
                'etc for other fields.
.Update

            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !WorkorderID
            If Me.[SubPallets].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Pallet Inventory Transactions] ( TransactionDate, PalletID, Delivered, Delivered By, Delivered To ) " & _
                    "SELECT " & lngID & " As NewID, TransactionDate, PalletID, Delivered, Delivered By, Delivered To " & _
                    "FROM [Pallet Inventory Transactions] WHERE WorkorderID = " & Me.WorkorderID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records in the subform."
            End If
            
            'Duplicate the related records: append query.
            If Me.[Workorder Parts].Form.RecordsetClone.RecordCount > 0 Then
                strSql1 = "INSERT INTO [Workorder Parts] ( ProductID, Quantity, UnitPrice, ArtNrKlant, OCVE, ICHE, HE, BTW ) " & _
                    "SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, ArtNrKlant, OCVE, ICHE, HE, BTW " & _
                    "FROM [Workorder Parts] WHERE WorkorderID = " & Me.WorkorderID & ";"
                DBEngine(0)(0).Execute strSql1, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records in the subform."
            End If
            
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler

End Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Rey Obrero (Capricorn1)Commented:
replace line 50 with this


                strSql = "INSERT INTO [Pallet Inventory Transactions] ( TransactionDate, PalletID, Delivered, [Delivered By], [Delivered To] ) " & _
                    "SELECT " & lngID & " As NewID, TransactionDate, PalletID, Delivered, [Delivered By], [Delivered To] " & _
                    "FROM [Pallet Inventory Transactions] WHERE WorkorderID = " & Me.WorkorderID & ";"

Open in new window


you have enclosed field names with spaces,special characters in brackets []

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mvdwalAuthor Commented:
Dear Rey,

Thanks! It Worked!

I Also forgot WorkorderID in line 50

  strSql = "INSERT INTO [Pallet Inventory Transactions] ( WorkorderID, TransactionDate, PalletID, Delivered, [Delivered By], [Delivered To] ) " & _

Open in new window

mvdwalAuthor Commented:
Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.