copying data on a subform

[vanny]

HI all,

i have a form THE OREDER FORM and on it there is a subform that stores a list of codes and corresponding products for a particular order.

Now what happens is that sometimes ORDERS have the same product codes, so what I wish is to see if it is possible in same way or another to copy the data on the subform to create the next record as the previous one, and thus avoiding re-choosing all the products.

any help and ideas will be appreciated thanks

 

[llkhoutx]

One way is to store the order entries in the tag property of the various subform controls on the subform BeforeUpdate event, then with a button on your form or on a user toolbar an OnClick event stores a control's tag property value into the (relevant) subform control, e.g.

BeforeUpdate
dim ctl as control
for each ctl in me
if ctl.datatype = actextbox then me(ctl.name).tag = ctl.name
next

On the OnClick event
dim ctl as control
for each ctl in me
if ctl.datatype = actextbox then me(ctl.name)= ctl.name.tag
next

You have to check the control datatype, because labels, sub-subforms, etc. (other non-data controls) will cause you a problem.

 

[Krij]

Hi!

Here's a way to copy an existing order with all its subforms data. From a button on mainform, paste and adjust the following code:

Private Sub cmdCopy_Click()
On Error GoTo Err_SENDCOPY
Dim MyRs As DAO.Recordset
Dim sqlNew As String
Dim lngNewOID As Long
Dim intONr As Integer
Dim My2Msg As String, My2Ttl As String
Dim My2Btn As Integer, My2RetVal As Integer

My2Msg = "DO YOU REALLY WANT TO MAKE A COPY?"
My2Msg = Chr(13)
My2Btn = 51
My2RetVal = MsgBox(My2Msg, My2Btn, "COPY ORDER")

Me.Refresh

If My2RetVal = 2 Then
DoCmd.CancelEvent
Me!CustomerID.SetFocus
End
ElseIf My2RetVal = 7 Then
DoCmd.CancelEvent
Me!CustomerID.SetFocus
End
ElseIf My2RetVal = 6 Then
Set MyRs = Me.RecordsetClone
intONr = Nz(DMax("[Ordernr]", "tblOrders"),1) + 1

With MyRs
.AddNew
!Ordernr = intONr
!MyDate = Now
!MyTime = Time
!CustomerID = Me!CustomerID
!EmployeeID = Me!EmployeeID
.Update
End With
MyRs.Bookmark = MyRs.LastModified
lngNewOID = MyRs!OrderID

sqlNy = "INSERT INTO tblOrderdetails(ItemID,Price,Amount,Discount,Taxes,OrderID) " _
& "SELECT ItemID,Price,Amount,Discount,Taxes," & lngNewOID _
'Now pay attention to the space in the following line:
& " FROM tblOrderdetails " _
& "WHERE OrderID = " & Me!OrderID

CurrentDb.Execute sqlNew, dbFailOnError

Me!CustomerID.SetFocus
End If

DoCmd.GoToRecord Record:=acLast

Exit_SENDCOPY:
Exit Sub

Err_SENDCOPY:
MsgBox Err.Source & Chr(13) _
& "Error # " & " " & Err.Number & " ORDERS/SENDCOPY" & Chr(13) _
& Err.Description & Chr(13), vbCritical + vbOKOnly
Resume Exit_SENDCOPY
End Sub

HI all,

i have a form THE OREDER FORM and on it there is a subform that stores a list of codes and corresponding products for a particular order.

Now what happens is that sometimes ORDERS have the same product codes, so what I wish is to see if it is possible in same way or another to copy the data on the subform to create the next record as the previous one, and thus avoiding re-choosing all the products.

any help and ideas will be appreciated thanks