By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,903 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,903 IT Pros & Developers. It's quick & easy.

quantity update

P: 49
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:
Expand|Select|Wrap|Line Numbers
  1.   Dim rstquantityupdate As DAO.Recordset
  2.    Set rstquantityupdate = dbase.OpenRecordset("tblproduct")
  3.  
  4.     rstquantityupdate.Edit
  5.     rstquantityupdate("quantity") = rstquantityupdate("quantity") - (Me.cboquantity1.Value)
  6.     rstquantityupdate.Update
Any suggestions?
Jan 28 '08 #1
Share this Question
Share on Google+
5 Replies


P: 14
Where did you place this code? Doing the right thing at the wrong time will not yield good results.
Jan 28 '08 #2

P: 49
Where did you place this code? Doing the right thing at the wrong time will not yield good results.
I have a add button, so each time a customer places an order for an item, they would select the product id and this brings up the details and then from the quantity combo box they select the amount req, the they select ADD button which saves the record into a listbox and clears that line for another item to be selected.

I placed the code within the add button
Jan 28 '08 #3

NeoPa
Expert Mod 15k+
P: 31,492
For your first problem, you need to add some code before going into the .Edit phase to ensure the quantity selected is not greater than the quantity available (I've added [ CODE ] tags for you - please remember to do so in future).

For your second problem, you need to select the correct product record before adjusting the quantity in it ;)
I've included some example code which may help illustrate the ideas.
Expand|Select|Wrap|Line Numbers
  1.   Dim rstQuantityUpdate As DAO.Recordset
  2.  
  3.   Set rstQuantityUpdate = dbase.OpenRecordset("tblproduct")
  4.   With rstQuantityUpdate
  5.     Call .FindFirst("[Product]='" & Me.txtProduct & "'")
  6.     If .NoMatch Then
  7.       'Handle no record found
  8.       'Probably Exit Sub
  9.     End If
  10.     If Me.cboQuantity1 > !Quantity Then
  11.       'Handle this - maybe using MsgBox...
  12.       Me.cboQuantity1 = !Quantity
  13.     End If
  14.     Call .Edit
  15.     !Quantity = !Quantity - Me.cboQuantity1
  16.     Call .Update
  17.   End With
Jan 28 '08 #4

P: 49
For your first problem, you need to add some code before going into the .Edit phase to ensure the quantity selected is not greater than the quantity available (I've added [ CODE ] tags for you - please remember to do so in future).

For your second problem, you need to select the correct product record before adjusting the quantity in it ;)
I've included some example code which may help illustrate the ideas.
Expand|Select|Wrap|Line Numbers
  1.   Dim rstQuantityUpdate As DAO.Recordset
  2.  
  3.   Set rstQuantityUpdate = dbase.OpenRecordset("tblproduct")
  4.   With rstQuantityUpdate
  5.     Call .FindFirst("[Product]='" & Me.txtProduct & "'")
  6.     If .NoMatch Then
  7.       'Handle no record found
  8.       'Probably Exit Sub
  9.     End If
  10.     If Me.cboQuantity1 > !Quantity Then
  11.       'Handle this - maybe using MsgBox...
  12.       Me.cboQuantity1 = !Quantity
  13.     End If
  14.     Call .Edit
  15.     !Quantity = !Quantity - Me.cboQuantity1
  16.     Call .Update
  17.   End With
i am getting the quantity to update but only for my first record. if i select lets say the 10th record and then add a quantity and then add, this would minus the quantity selected from the first record when infact it should be doing it for the 10th.. any ideas
Jan 28 '08 #5

NeoPa
Expert Mod 15k+
P: 31,492
If you post your whole procedure then I can tell you which bits of my suggestion you haven't added yet. With the code I posted there should be enough to lead you to a valid solution. I don't want to give a full code solution, but I can point you in the right direction.
Jan 28 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.