Question Stock Update

[Diksha1310]

Hi! can someone help..very urgent.
I'm computerising a bookshop. i have a stock table/form, sales and purchase table/form. When a customer buys a book, in my stock form QuantityLeft is obtained by a query which subtracts QuantitySold from QuantityInStock.

I need a code or any method by which i can automatically update QuantityLeft to the current QuantityInStock in the stock form(i want the value for QuantityLeft to replace that of QuantityInStock after a sale transaction). Same for a purchase....

 

[pr2-eugin]

How are you making the sale? I mean, do you use the form on which you have a button ? If so in the On_Click() method use a UPDATE statement. Think that should work.

 

[Diksha1310]

Yeah, on my form i add a new sale transaction. But do u hav any code for an update statement?

 

[pr2-eugin]

This is just a simple code, you have to tailor it according to your needs..

Private Sub buttonName_OnClick()
    Dim cmd As String
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    cmd = "UPDATE your_table_name SET QuantityLeft= " & QuantityInStock & " WHERE use_a_Criteria"    [COLOR=SeaGreen] 'something like [ISBN] = " & ISBNTxtField.Value & "[/COLOR]
    dbs.Execute cmd
End Sub