Change data in subform from form ComboBox

[ShostyFan]

a simplified version of my problem is this:


I have a form called Receipts and a sub form called Receipt Details. If a receipt and its details are saved I would like to be able to go back to the receipt and change a comboBox from Sale to Return and have the Qty's in the receipt details autoChange to a negative qty. So 1 would become -1 and so on.
Any help on this would great!

[ketbdnetbp]

ShostyFan -

I used the following code to change quantities from positive to negative, in a subform, a while back. Unfortunately, I don’t have the time to re-write using your specifications so, it is provided, "As Is". You should be able to adapt it to your table/field/form names and situation. This code assumes there is a 1/M relationship between SalesTBL and SalesDetailsTBL using the SalesID.

'In the AfterUpdate Event of the Combo, you could try…

If Not IsNull(Me![NameofCombo]) And Me![NameOfCombo] = "Return" then

Dim mySQL As String
mySQL = "UPDATE SalesTBL INNER JOIN SalesDetailsTBL ON SalesTBL.SalesID = SalesDetailsTBL.SalesID" & _
" SET SalesDetailsTBL.CSQTY = ([CSQTY]-([CSQTY]*2))" & _
" WHERE (((SalesDetailsTBL.SalesID)=[Forms]![SalesTBLFRM]![SalesID]));"
DoCmd.SetWarnings False
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True
[Forms]![SalesTBLFRM]![qrySalesDetailsTBL1Subform].Form.Requery

Else
'?????

End if

The expression ([CSQTY]-([CSQTY]*2)) should produce a negative number.

A note of caution, anytime data is changed in such a profound way, you may need a way to check if the person doing the change has the authority/permission. Lastly, you should also have a method set-up that can reverse the change. You may be able to use the Abs() function in this regard.

Hope this helps,

Jim