2 record sets

[tezza]

I have 2 tables, namely Despatches and Stock.
On the click of the Despatch button on the form, I want to read each
transaction line on the despatch and write them out to the stock table.
Can anyone help me with the coding for this or point me to a good example of
code that does something similar.
I want to find all the transactions for the order being despatched and then
write them out to the stock file. I know this has to be done within a loop
but I am not proficient enough with VB.
Do I use FindFirst to position myself on the first record that I want and
then do a FindNext within a loop until the Order Number changes?
Any help would be most welcome.

 

[Tim Ferguson]

I want to find all the transactions for the order being despatched and
then write them out to the stock file. I know this has to be done
within a loop but I am not proficient enough with VB.

don't know what this has to do with VB: it's surely a SQL problem. Without
testing, it should be something like

UPDATE Stocks
SET StockedLevel = Stocks.StockedLevel - Despatches.QuantitySent
FROM Stocks LEFT JOIN Despatches
ON Stocks.ProductID = Despatches.ProductID
WHERE Despatches.OrderID = [EnterYourOrderNumberHere:]



Hope that helps


Tim F

 

[tezza]

Thanks Tim,
I see what you are doing.
The difference is that I want to write out my despatch lines to an Inventory
file (almost like a stock audit file).
The code you have sent would be ok if all I wanted to do was decrease the
stock levels for each of the despatches.
Cheers

I want to find all the transactions for the order being despatched and
then write them out to the stock file. I know this has to be done
within a loop but I am not proficient enough with VB.

don't know what this has to do with VB: it's surely a SQL problem. Without
testing, it should be something like

UPDATE Stocks
SET StockedLevel = Stocks.StockedLevel - Despatches.QuantitySent
FROM Stocks LEFT JOIN Despatches
ON Stocks.ProductID = Despatches.ProductID
WHERE Despatches.OrderID = [EnterYourOrderNumberHere:]



Hope that helps


Tim F

 

[Tim Ferguson]

The difference is that I want to write out my despatch lines to an
Inventory file (almost like a stock audit file).

In that case, just export the query..?


Tim F