Hi... wondering if anyone can help me here...

I'm building an order entry system for a client and I'm stumped!

I have a form(Company bill to and ship to addresses, sales rep name, PO number etc.) with a subform that contains the products ordered (Product code, description, quantity, price etc.) - many of them

What my client wants is for the sales rep to fill out an order, move to the next order and then have the ability to clone all the data from the previous order to the new order... I can get the main form to clone ok, but not the subform. The client wants this b/c many of their customers have multiple retail outlets, and they will order the same products for each retail outlet (many duplicate orders - only difference would be the ship to addresses).

Right now I have a command button using the onclick event to clone the main form component, but it doesn't work on the subform... here's the code for that

Private Sub btnClonePreviousOrder_Click()
DoCmd.GoToRecord , , acPrevious
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNext
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdPaste
End Sub

Is there a way to do the same with the subform... I keep getting an error that the previous order doesn't exist (i.e. acPrevious = 0)

Help!!

Many thanks,
Collin

'Here's what I did

Public Function cmdMakeCloneWithChildren_Click()

'Routine associated with a Click on a command button to clone the CURRENT record on the 'one side' on a form
'(not necessarily the 'previous' one!), and then clone the matching records on the 'many' side:
'You'll want to add error handling!

'Relevant fragment:

Dim dbs As Database
Dim rstmain As Recordset, rstSub1 As Recordset, rstSub2 As Recordset
Dim strSQL As String
Dim lngThisRecord As Long, lngNewRecord As Long
Dim lngTemp1 As Long, lngTemp2 As Long 'temp variables to hold 'required to clone' fields from Current 'Main' record


lngThisRecord = Me.[txtRecordID].Value

Set dbs = CurrentDb

Set rstmain = dbs.OpenRecordset("tblMain", dbOpenDynaset)

strSQL = "SELECT * from tblSub WHERE RecordFK = " & Str(lngThisRecord)
'Note that RecordFK is the foreign key in tblSub for the 1:M relationship

'get existing 'many'-side records
Set rstSub1 = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

'get a recordset we can add to but not edit
Set rstSub2 = dbs.OpenRecordset("tblSub", dbOpenDynaset, dbAppendOnly)

With rstmain 'bit to add record on 'one' side
strSQL = "[RecordID = " & lngThisRecord
.FindFirst strSQL
lngTemp1 = !Field1 '{replace with your first field in Main}
lngTemp2 = !Field2 '{replace with your second field in Main}
'etc... You will need to store each of your fields you want to clone from record in Main.
'Do NOT attempt to clone RecordID or any other autonumbers. Access will populate autonumbers automatically.

.AddNew
!Field1 = lngTemp1
!Field2 = lngTemp2
'etc
.Update
lngNewRecord = !RecordID
End With

With rstSub1
.MoveLast
.MoveFirst
Do While (Not .EOF) 'bit to add record(s) on 'many side:
rstSub2.AddNew
rstSub2!RecordFK = lngNewRecord
rstSub2!SubField1 = !SubField1
rstSub2!SubField2 = !SubField2
'etc... repeat for all fields in Sub table. If this table has any autonumbers, do NOT add them here. Access will populate autonumbers automatically.
rstSub2.Update
.MoveNext
Loop 'for Do While (Not .EOF)
End With

rstSub2.Close
rstSub1.Close
rstmain.Close

End Function



[This message has been edited by KKilfoil (edited 03-27-2002).]

An append query would be easier. You'll need two parameters for it. The OrderId for the "from" order to use as selection criteria and the OrderId for the "to" order to use to supply the OrderId to be appended.

Wow!!

As usual, Pat has identified a concise solution.

Pat, where were you when I posted the same problem: http://www.access-programmers.co.uk/ubb/wink.gif http://www.access-programmers.co.uk/ubb/Forum1/HTML/004319.html

Las Vegas http://www.access-programmers.co.uk/ubb/smile.gif

Thanks guys... You'll have to forgive my ignorance here.

The info on the form is using tblCustomers(1) linked to tblOrders(many) by CustomerID and tblOrders(1) linked to tblOrderDetails(many) by OrderID. I'm assuming that I build the Append query based on these 3 Tables?

Where do I store the append query to? A seperate table? Does this table require all the fileds from these 3 Tables (tblCustomers, tblOrders and tblOrderDetails) or just the ID fields? Are there any relationships required for this new Table?

Thanks,
Collin

The append query only needs to append to the order details table. You already have code to add the new record to the order table. You can build the append query in code so that it is easy to include the two parameters.

Thanks All... I am still not getting this. I am not used to doing this through Access/VBA but rather through ASP/VBScript. On my code below I am getting an error of 'Too few parameters - Expected 1'

If someone could help me with my code, I would be greatly appreciative...
Thanks!!

Dim strRST As Recordset

Set strRST = CurrentDb.Openrecordset("SELECT OrderID, InternalItemCode, Quantity, DiscountID, AdditionalDiscountID FROM OrderDetailsTable WHERE OrderID = Me!OrderID -1;")
Do While Not strRST.EOF
Me![NewOrders_Subform].strOrderID = Me![OrderID]
Me![NewOrders_Subform].strInternalItemCode = strRST("InternalItemCode")
Me![NewOrders_Subform].strQuantity = strRST("Quantity")
Me![NewOrders_Subform].strDiscountID = strRST("DiscountID")
Me![NewOrders_Subform].strAdditionalDiscountID = strRST("AdditionalDiscountID")
Loop

strRST.Close

Please ignore my previous note... I am now trying as Pat has suggested, but getting an error on my INSERT statement... is there a syntax error I am missing?

DoCmd.RunSQL "INSERT INTO (OrderDetailsTable.OrderID, InternalItemCode, Quantity, DiscountID, AdditionalDiscountID) SELECT (OrderDetailsTable.OrderID, InternalItemCode, Quantity, DiscountID, AdditionalDiscountID) FROM (OrderDetailsTable) WHERE (OrderDetailsTable.OrderID = OrderDetailsTable.OrderID - 1);"

I think you want to reference the form field:

DoCmd.RunSQL "INSERT INTO (OrderDetailsTable.OrderID, InternalItemCode, Quantity, DiscountID, AdditionalDiscountID) SELECT (OrderDetailsTable.OrderID, InternalItemCode, Quantity, DiscountID, AdditionalDiscountID) FROM (OrderDetailsTable) WHERE (OrderDetailsTable.OrderID = " & Me.OrderID - 1 & ");"

Thanks Pat... I actually had to play with the syntax a bit more and it is working and I am getting the Append Query warning, which when I select OK then gives me a Key Violations error... I know this is happening on the OrderID (it wants to insert the previous OrderID number)(Me.OrderID -1), I thought that for AutoNumber fields the number would automatically increment by 1??