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

mvdwal
mvdwal used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
ste5anSenior Developer

Commented:
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)
Top Expert 2016

Commented:
yes that is one way to copy the form and subform details records

see this link for another way
mvdwal
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

Ensure you’re charging the right price for your IT

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

Find out now!
Top Expert 2016
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 []
mvdwal
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

mvdwal

Author

Commented:
Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial