Addnew with codes

[Gordon]

I have 2 tables: tblOrders and tblOrdersDetail.
tblOrders has the following fields: OrderID, OrderDate
tbltOrdersDetail has these fields: OrdersDetailID, OrderID, ProductID, Quantit

What I am trying to do is, when an order is returned, I can click a button o
my frmOrder to automatically enter a new record in tblOrder, then ne
records in tbltOrdersDetail, whose ProductIDs are the same as the origina
order, with each Quantity is a negative value of the original Quantity

Any solutions? Thanks a lot.

 

[solex]

Gordon,

One suggestions is to execute a series of SQL statement such as:

(1) Insert the new order
(2) Retrieve the OrderID of the new Order
(3) Insert the new Order Detail Lines with a statement like this:
INSERT INTO tblOrdersDetail (OrderID, ProductID, QuantyID)
SELECT NewOrderID, ProductID, -1*Quantity
FROM tblOrdersDetail
WHERE OrderID=OldOrderID

 

[Gordon]

Now I am trying to use the following code to get the first part done. But I go
the following error message
Run-time Error 326
Item not found in this collectio

Can anyone tell me what is wrong with my code? thanks

Private Sub Return_Click(
Dim db As DAO.Database, rs As DAO.Recordse
Dim RtnOrderID As Strin
Dim RtnOrderDate As Dat
Dim stgMsg As Strin

Set db = CurrentD
Set rs = db.OpenRecordset("tblOrders", dbOpenDynaset

RtnOrder = Forms![Orders]![SelectOrder] & "Rtn" ' WHEN I CLICK "DEBUG", THIS LINE IS HIGHLIGHTED.

stgMsg = "Do you really want to return this order?

If Msgbox(stgMsg, vbYesNo, "Warning!") = vbYes The
RtnOrderDate = Format(InputBox("Enter the return date."), "mm/dd/yy"
Els
Exit Su
End I

With r
.AddNe
!OrderID = RtnOrderI
!OrderDate = RtnOrderDat
.Updat
End Wit

End Sub

 

[Gordon]

Sorry, the highlight was on the "!OrderID = RtnOrderID" line. Thanks

----- Gordon wrote: ----

Now I am trying to use the following code to get the first part done. But I go
the following error message
Run-time Error 326
Item not found in this collectio

Can anyone tell me what is wrong with my code? thanks


Private Sub Return_Click(
Dim db As DAO.Database, rs As DAO.Recordse
Dim RtnOrderID As Strin
Dim RtnOrderDate As Dat
Dim stgMsg As Strin

Set db = CurrentD
Set rs = db.OpenRecordset("tblOrders", dbOpenDynaset

RtnOrder = Forms![Orders]![SelectOrder] & "Rtn" ' WHEN I CLICK "DEBUG", THIS LINE IS HIGHLIGHTED.

stgMsg = "Do you really want to return this order?

If Msgbox(stgMsg, vbYesNo, "Warning!") = vbYes The
RtnOrderDate = Format(InputBox("Enter the return date."), "mm/dd/yy"
Els
Exit Su
End I

With r
.AddNe
!OrderID = RtnOrderI
!OrderDate = RtnOrderDat
.Updat
End Wit

End Sub

 

[solex]

It does not look like you are initializing RtnOrderID anywhere in your code.
Also I am assuming that the OrderID is an autonumber field if so you cannot
assign it a value it will automatically be assigned. You can query its
value after the Update.

Regards,
Dan

Sorry, the highlight was on the "!OrderID = RtnOrderID" line. Thanks.

----- Gordon wrote: -----

Now I am trying to use the following code to get the first part done. But I got
the following error message:
Run-time Error 3265
Item not found in this collection

Can anyone tell me what is wrong with my code? thanks.


Private Sub Return_Click()
Dim db As DAO.Database, rs As DAO.Recordset
Dim RtnOrderID As String
Dim RtnOrderDate As Date
Dim stgMsg As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tblOrders", dbOpenDynaset)

RtnOrder = Forms![Orders]![SelectOrder] & "Rtn" ' WHEN I CLICK

"DEBUG", THIS LINE IS HIGHLIGHTED.