Access Help

[suarve]

Hi,

Say i have a simple products table with a quantity attribute and an orders table. I'm adding some sort of form for place a new order.

How would you suggest i automatically update the quantity in the products table when a new order is placed (E.g. user selects a product in the form, need to decrease that quantity by one in the products table).

Thanks

 

[RockLobster]

This is a very simple example. You will need to put error checking in like checking there is enough stock, etc. I have knocked up a very simple access db, if you want to see it in action click here - http://upload2.net/page/download/AbYEj6sD9M8B0km/Test.mdb.html

Dim conDatabase As ADODB.Connection
    Dim strSQL As String

    Set conDatabase = CurrentProject.Connection
    
    strSQL = "UPDATE Products SET Quantity = Quantity - " & txtQuantity.Value & " WHERE ProductID=" & txtProductID.Value
    conDatabase.Execute strSQL
    
    MsgBox "Quantity Updated"
    
    conDatabase.Close
    Set conDatabase = Nothing

 

[suarve]

This is a very simple example. You will need to put error checking in like checking there is enough stock, etc. I have knocked up a very simple access db, if you want to see it in action click here - http://upload2.net/page/download/AbYEj6sD9M8B0km/Test.mdb.html

Dim conDatabase As ADODB.Connection
    Dim strSQL As String

    Set conDatabase = CurrentProject.Connection
    
    strSQL = "UPDATE Products SET Quantity = Quantity - " & txtQuantity.Value & " WHERE ProductID=" & txtProductID.Value
    conDatabase.Execute strSQL
    
    MsgBox "Quantity Updated"
    
    conDatabase.Close
    Set conDatabase = Nothing

Hi,

Thanks for the reply. That's exactly what i needed, except, how would I get say a drop down where i could choose the product and have it update the right record?


E.g. say i selected a product and chose a quantity, i would like it to update that record.
Thanks


EDIT:

Have actually tried doing this so i can select the product (changed the Description attribute to Product and added a combo box called cboProduct). I get an error relating to the conDatabase.Execute strSQL part, saying 'No value given for one or parameters, any idea why it doesn't work:

Private Sub Command0_Click()
    Dim conDatabase As ADODB.Connection
    Dim strSQL As String

    Set conDatabase = CurrentProject.Connection
    
    strSQL = "UPDATE Products SET Quantity = Quantity - " & txtQuantity.Value & " WHERE Product=" & Me.cboProduct.Value
    
    conDatabase.Execute strSQL

    MsgBox "Quantity Updated"
    
    conDatabase.Close
    Set conDatabase = Nothing

End Sub