Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Easy way to update products quantity in Invoice to Stock with SQL command

Posted on 1998-10-23
4
Medium Priority
?
386 Views
Last Modified: 2013-12-25
Currently, I am developing a commercial app with VB5. I want to update the quantities in the stock table when the user confirms a sale in the invoice, but would like to know if there is a simple way to do this with the SQL command, not using Find or Seek command with Looping where the programmers usually do.

Please give me an example with coding.

Thanks.
0
Comment
Question by:poodle
4 Comments
 
LVL 1

Expert Comment

by:CarlosJac
ID: 1497169
Dim Sql as string
Dim DB as Database

Set DB = CurrentDatabase()
Sql = "UPDATE Stock SET Quantity = Quantity - 15 WHERE ProductID = 1"
DB.Execute Sql



0
 

Author Comment

by:poodle
ID: 1497170
Thanks for your answer, but I already knew the command that you've send it to me.

The example that you wrote will update only one product which productID = 1, but if the invoice had 3 different product IDs like 1,2 and 3, I have to make a looping 3 times to update each products...... because in real bussiness, one client can buy several DIFFERENT products in a single store at a time as below:

=============================================================
INVOICE:00001                                 DATE:10/25/1998
CLIENT:John Smith
-------------------------------------------------------------
ProductID    Description       Unit Price     Qty.      Total
-------------------------------------------------------------
       1     HB Pencil           1.00          3         3.00
       2     Notebook            3.50          1         3.50
       3     PC Disquete         1.50         10        10.50
-------------------------------------------------------------
                                              Subtotal: 17.00
=============================================================

The invoice example as above, the client John Smith bought 3 different products. There are three tables involved: "Invoice", "InvoiceProducts" and "Stock". The "Invoice" table has the one to many relation with the "InvoiceProducts" table.

How can I update these products to the stock table with single line SQL command? Is there a way to do it or it must use the looping command? Because by my logic, I think there must be a way to make a recordset combining the "InvoiceProducts" and "Stock" table then update the stock similar to the command you wrote.

Please Help!!!




0
 
LVL 3

Expert Comment

by:vbWhiz
ID: 1497171
It looks like you want to update the database's information all at one time. Have you thought about using database transactions? I have used transactions in situations like this so the update happens all at once, or is cancelled as a group. This allows you to loop through all the table updates before commiting the transaction.

Sample Code:

Dim WS As Workspace
Dim DB AS Database
Dim Rst As Recordset

Set WS = DBEngine.createWorkspace("", "Admin", "")
Set DB = WS.OpenDatabase("C:\mydata.mdb")
Set Rst = DB.openrecordset("tblStock")

Rst.index = "PrimaryKey"

WS.BeginTrans


for each line in orderlines
  Rst.Seek "=", LineStockID
  Rst.Edit
  Rst!fldQty = Rst!fldQty - 5
  Rst.update
next line

WS.commitTrans


This will allow you to perform data operations between the WS.Begintrans and the WS.commitTrans and execute them together as a group.

-Note- In the case of an error you can use WS.rollback to cancel a transaction.

I hope this works for you!
0
 
LVL 3

Accepted Solution

by:
TheAnswerMan earned 100 total points
ID: 1497172
why cant you do this>
Set DB = CurrentDatabase()
Sql = "UPDATE Stock SET Quantity = Quantity - 3 WHERE ProductID = 1" _
&  "UPDATE Stock SET Quantity = Quantity - 1 WHERE ProductID = 2" _
&  "UPDATE Stock SET Quantity = Quantity - 10 WHERE ProductID = 3"
DB.Execute Sql
 
DB.Execute Sql

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question