Avatar of mvdwal
mvdwal
Flag for Netherlands asked 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

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

Microsoft Access

Avatar of undefined
Last Comment
mvdwal

8/22/2022 - Mon
ste5an

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)

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

see this link for another way
SOLUTION
mvdwal

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
mvdwal

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
mvdwal

ASKER
Thanks!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23