Loop problem

[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 tblOrdersDetail, whose ProductIDs are the same as the origina
order, with each Quantity is a negative value of the original Quantity

I tried with the following code to complete the 2nd part of the work

Private Sub Return_Click(

Dim db As DAO.Database, rs As DAO.Recordse
Dim RtnOrderID As String, RtnProductID As String, strCriteria As Strin
Dim intCriteria As Integer, RtnQuantity As Intege

Set db = CurrentD
Set rs = db.OpenRecordset("tblOrdersDetail", dbOpenDynaset
strCriteria = Forms![frmOrders]![SelectOrder
With r
.FindFirst "OrderID = '" & strCriteria & "'
Do While Not .NoMatc
intCriteria = DLookup("OrdersDetailID", "tblOrdersDetail", "OrderID = '" & strCriteria & "'"
RtnOrderID = strCriteria & "R
RtnProductID = DLookup("ProductID", "tblOrdersDetail", "OrdersDetailID =" & intCriteria
RtnQuantiry = -1 * DLookup("Quantity", "tblOrdersDetail", "OrdersDetailID =" & intCriteria
.AddNe
!OrderID = RtnOrderI
!ProductID = RtnProductI
!Quantity = RtnQuantit
.Updat
.FindNext "OrderID = '" & strCriteria & "'
Loo
End Wit

End Su

When there is only one product under an order, the code works fine. Bu
when there are more than two products under an order, the code can
add as many rows as the original order to the tblOrdersDetail table. Bu
each row is the same: copy the first ProductID, enter a negative value o
the first Quantity. Can anyone tell what is wrong in my code? Thanks

 

[tina]

it would be easier to run an append query, or a SQL statement, from your
command button, as

Dim lngID As Long

lngID = Me!OrderID

DoCmd.RunCommand acCmdRecordsGoToNew
Me!OrderDate = Date
DoCmd.RunCommand acCmdSaveRecord

DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblOrdersDetail (OrderID, ProductID, Quantity)
" _
& "SELECT " & Me!OrderID & ", [ProductID]," _
& "[Quantity]-([Quantity]*2) FROM tblOrdersDetail WHERE
tblOrdersDetail.OrderID=" _
& lngID
DoCmd.SetWarnings True

Me!MySubformControlName.Requery

i used the table and field names you provided in my test. but you need to
substitute the correct name in the requery action there at the end. i
believe you can also run the SQL inside a transaction, for more control of
the update - but i can't give you details on that. i learned about it here
in the newsgroups, but haven't tried it for myself yet.

hth

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

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

I tried with the following code to complete the 2nd part of the work:

Private Sub Return_Click()

Dim db As DAO.Database, rs As DAO.Recordset
Dim RtnOrderID As String, RtnProductID As String, strCriteria As String
Dim intCriteria As Integer, RtnQuantity As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblOrdersDetail", dbOpenDynaset)
strCriteria = Forms![frmOrders]![SelectOrder]
With rs
.FindFirst "OrderID = '" & strCriteria & "'"
Do While Not .NoMatch
intCriteria = DLookup("OrdersDetailID", "tblOrdersDetail",

"OrderID = '" & strCriteria & "'")

RtnOrderID = strCriteria & "R"
RtnProductID = DLookup("ProductID", "tblOrdersDetail",

"OrdersDetailID =" & intCriteria)

RtnQuantiry = -1 * DLookup("Quantity", "tblOrdersDetail",

"OrdersDetailID =" & intCriteria)

 

[Gordon]

Thanks Tina. But I can not figure out how to fit in your code
Now my solution is like this
1. create a query named [RtnOrderQuery]
INSERT INTO tblOrdersDetail ( OrderID, ProductID, Quantity
SELECT [OrderID] & "R" AS RtnOrder, tblOrdersDetail.ProductID, -1*[Quantity] AS RtnQua
FROM tblOrdersDetai
WHERE (((tblOrdersDetail.OrderID)=[forms]![frmOrders]![SelectOrder]))
2. then a simple code to run the query
Dim strSQL As Strin
strSQL = "RtnOrderQuery
Docmd.OpenQuery strSQL, acNormal, acEdi

It works just perfect. It was based on your suggestion. But I just could no
figure out how to code that query so that it does not physically exit. Can yo
tell me how to make that strSQL = "INCERT INTO ... SELECT ... FROM ... WHERE..."
It was awefully difficult for me to quote those stuff, especially with some criterias
Thanks.

 

[tina]

it's a text string. when you have a multiple line text string in VBA, you
have to enclose each text line in quotes, put a continuation character at
the end of each line (space then underscore), and concatenate each line with
the ampersand. references to a form have to be outside of the quotes.
i'm guessing you got confused looking at my previous post because of the
word wrap. here it is again:

DoCmd.RunSQL "INSERT INTO tblOrdersDetail " _
& "(OrderID, ProductID, Quantity) SELECT " _
& Me!OrderID & ", [ProductID], " _
& "[Quantity]-([Quantity]*2) FROM tblOrdersDetail " _
& "WHERE tblOrdersDetail.OrderID=" & lngID

as long as your solution is working, might as well stick with it. my
understanding is that a query object in the database has the advantage of
being already compiled at runtime.

hth

Thanks Tina. But I can not figure out how to fit in your code.
Now my solution is like this:
1. create a query named [RtnOrderQuery]:
INSERT INTO tblOrdersDetail ( OrderID, ProductID, Quantity )
SELECT [OrderID] & "R" AS RtnOrder,

tblOrdersDetail.ProductID, -1*[Quantity] AS RtnQuan

FROM tblOrdersDetail
WHERE (((tblOrdersDetail.OrderID)=[forms]![frmOrders]![SelectOrder]));
2. then a simple code to run the query:
Dim strSQL As String
strSQL = "RtnOrderQuery"
Docmd.OpenQuery strSQL, acNormal, acEdit

It works just perfect. It was based on your suggestion. But I just could not
figure out how to code that query so that it does not physically exit. Can you
tell me how to make that strSQL = "INCERT INTO ... SELECT ... FROM ... WHERE..."?
It was awefully difficult for me to quote those stuff, especially with some criterias.
Thanks.

 

[Gordon]

Thanks again, Tina. Now I am more clearer on that.