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

 

[Pat Hartman]

Do not under any condition run an update query in the code behind your form that updates the bound record! Access will give you an error message that someone is making an update that conflicts with the one you are making. That someone is YOU. You will be conflicting with yourself.

Change the RecordSource query of the form to include the inventory table. You can then update the OnHand count based on whether the current transaction is a sale or a return.

In the BeforeUpdate event of the Sale Form:
Me.OnHandCount = Me.OnHandCount - 1

In the BeforeUpdate event of the Return Form:
Me.OnHandCount = Me.OnHandCount + 1