Using an update query on a continuous form (1 Viewer)

[Furfaro2020]

Hi!

Really hoping someone can help me

I am working on an ordering system and have created an update query to deal with stock quantity changes.

My new order form has a continuous subform to allow the user to select multiple products in one order.

When I run my update query it only updates the data for the last product on the order. I have put my SQL code below, hoping there is an obvious solution to this problem somewhere!

UPDATE Batch SET Batch.QuantityInWarehouse = [QuantityInWarehouse]-forms!NewSale!SalesOrdersForm!salequantity
WHERE (((Batch.BatchID)=[Forms]![newsale]![SalesOrdersForm]![SalesCider]));


Thanks!

 

[CJ_London]

storing calculated values such as 'current stock' can cause problems particularly if it 'goes wrong' - which it will do at some point, much better to calculate current stock as and when required from your transactions. Don't confuse data with presentation.

But to answer your question, in a continuous form, only one record has the focus at a time - in this case the 'last' record to have the focus. So you need to run the code in the continuous form before or after update event - or click your button after every entry.

The alternative would be different code which loops through the subforms recordsetclone, running the update for each record.

I presume you have routines in place to prevent the user clicking the button more than once, or selecting a different row in the continuous form before clicking the button - both examples of what can go wrong since these events would deduct the salesquantity twice.

 

[arnelgp]

another approach is add another field (Balance) to your table. save the result of the calculation here.
first loop through the recordset, do it on the Form's After Update:

Private Sub Form_AfterUpdate()
    With Me.RecordsetClone
        If Not (.BOF And .EOF) Then .MoveFirst
        While Not .EOF
            Currentdb.Execute "Update Batch Set Balance=QuantityInWarehouse - " & _
                DSum("SalesQuantity", "SalesTable", "SalesCider = " & !SalesCider) & ", " & _
                "BatchID = " & !SalesCider)
            .MoveNext
        Wend
    End With
End Sub