SQL Update stock Query HELP!!!

[jordanturner]

Hi all,

I am using an update query to update the stock quantity in the stock table. I know this is bad practice and calculated fields should not be stored, but I have had advice and I did try to do it the correct way but just kept running into problems.

I am using an update query behind a "Confirm" button on both a Purchase Order form and a Dispatch form. Basically the query should + or - the number in the Qty dispatched or Qty Received depending on which form is being used.

The code i am using is:
UPDATE tblStock SET tblStock.QuantityInStock = tblStock.QuantityInStock-Forms!frmDispatch!frmDispatchLine.Form.QuantityDis patched
WHERE [tblStock].[StockID]=Forms!frmDispatch!frmDispatchLine.Form.StockID;

This code does seem to work but only updates 1 row(it always seems to be the last row on the subform) no matter how many rows are in the purchase order or dispatch.

Any help would be greatly appreciated as I thought I had solved the problem a while ago, but only tested the query on 1 row until now.

Thanks
Jordan T

[SoftwareMatters]

It will be updating whichever record you are on at the time or the first in the recordsource.

If you want to use a query you will need do a group by and sum the quantities before you update them.

To be honest I would always do something like this in VBA but it depends on your skill level.

[jordanturner]

Thanks for the reply JD, I don't really understand why I would have to sum all the values when they are different lines referencing different stock items. My VBA skills are not brilliant and I am relatively new to both VBA and SQL. But maybe if you had time you could give me an example of the VBA code or the SQL.
Thanks again

Jordan T

[SoftwareMatters]

You are right summing them won't work - I misinterpreted the situation. The problem you have is that the query will only be able to update a which ever row is currently selected.

This is the way I would do it:

Add a Yes/No field to despatches called StockUpdated?

Add a button that when pressed loops through all records that do not have stock updated ticked and updates the stock accordingly and also ticks StockUpdated?

The code will need to open the stock recordset and despatches recordset, loop through the despatches and update each item as necessary.

Something like this:

dim rsDespatches as recordset, rsStock as recordset
set rsDespatches = CurrentDb.OpenRecordset("SELECT Despatches.* FROM Despatches WHERE StockUpdate? = 0;")
set rsStock = CurrentDb.OpenRecordset("SELECT Stock.* FROM Stock;")

with rsDespatches
.movefirst
do until .eof
rsStock.FindFirst "PartNo = '" & !PartNo& "'"
rsStock.edit
rsStock!Qty = rsStock!Qty + !QtyDespatched
rsStock.update
.movenext
loop
end with

This is a very rough example without any error traps etc and I am using generic names but hopefully it gives you an idea.

If you don't think you can follow this then post back in the forum and maybe someone else can help with a different solution.

[pbaldy]

You beginning to see why trying to keep quantity in stock updated isn't so easy? You could loop through the recordset clone of your form to make sure you hit every item.

[jordanturner]

Thanks for the replies JD and Pbaldy. After all investigatins into storing a value for quantity in hand I have decided that it will be both easier and safer to calculate the values when need as Allen Brown states in his article. I have now updated the database in this way and it all works fine in working out the quantity on hand. The only thing is that over time I know the system will slow in looking back on all transactions made. I think a periodic stock take should ammend this but I don't really know were to start. I think a stock take table may be needed. Any pointers into how to implement that would be much appreciated.
Thanks again for the replies, this forum has been an amazing help.
Jordan T

[pbaldy]

You sort of have the same question going on two threads. I note Allan has responded on the other, and I'd recommend keeping the discussion in one place:

https://www.accessforums.net/queries...html#post30772