quantity update

[goldstar1982]

hello all.

Another problem, since i have joined all i have been doing is asking for help. i need to repay all these favours!!!

I currently have an order form, and a product form,
Scenario: if a customer was to place an order for an item with a quantity of 5 and there are only 4 left in stock, i want a message to appeat to alert that only four remaing and to fill the box with 4.?
Second problem is with the same code, when a quantity is selected, it minus it off from ther fine but only for the first record, if i was to select the 10th item for example and select quantity 5, this would remove 5 from the first product in the list and not the 10th.

Code used so far:

Code: ( text )
Dim rstquantityupdate As DAO.Recordset
Set rstquantityupdate = dbase.OpenRecordset("tblproduct")

rstquantityupdate.Edit
rstquantityupdate("quantity") = rstquantityupdate("quantity") - (Me.cboquantity1.Value)
rstquantityupdate.Update

Any suggestions?

 

[pbaldy]

For starters, I would caution against trying to store the quantity on hand. Good discussion here:

http://allenbrowne.com/AppInventory.html

As to your immediate problem, you're just opening the table. Use an SQL statement instead that pulls only the record you want to update.

 

[goldstar1982]

hi ive been trying to do an sql query with no luck, would you have an example of how to do this?

 

[pbaldy]

    strSQL = "SELECT confirmation_no, auth_person, lname, fname " & _
             " FROM operator " & _
             " WHERE confirmation_no = '" & Me.Reservation_num & "';"

    Set rslookup = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)