Member Avatar for sackymatt

Hi all, I want to know how i can decrement the value of quantity in stock in my data base. This would be based on how times an item is purchased. So every time an item is clicked, i want to show the quantity has decreased in the database. I am using vb6 and i have my database in msaccess..

I also want to know how can i create a payslip invoice in vb6..when an item to be bought is entered, the information desplays at that area that will be printed out as an invoice..
pls help

  • 2 Contributors
  • 3 Replies
  • 238 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by vb5prgrmr

Dani AI

Generated

Quick, practical path for both problems (stock decrement + printable payslip), building on 's point about recording purchases: treat a sale as one atomic operation that both records the sale and updates stock. That prevents mismatches, prevents negative stock and gives you a SaleID to use when printing the invoice.

Suggested minimal schema (example):

  • Products: ProductID (AutoNumber PK), Name, Quantity (Integer), UnitPrice.
  • Sales: SaleID (AutoNumber PK), SaleDate, CustomerID (optional), Total.
  • SaleLines: LineID, SaleID (FK), ProductID (FK), Qty, UnitPrice, LineTotal.

Workflow (high level):

  1. Open one DB connection and BeginTrans.
  2. Insert Sale header, read the new SaleID (same connection).
  3. Insert SaleLines for each item.
  4. Run an atomic update that decrements stock only if enough quantity remains; check RecordsAffected.
  5. Commit if all affected rows > 0; otherwise Rollback and inform the user.

Example VB6/ADO pattern (trimmed — requires a reference to Microsoft ActiveX Data Objects):

' open connection, begin transaction
cn.BeginTrans
' insert sale header
cn.Execute "INSERT INTO Sales (SaleDate) VALUES (Now())"
Set rs = cn.Execute("SELECT @@IDENTITY")
saleID = CLng(rs(0)): rs.Close

' insert sale line (parametrized command)
' then atomic decrement:
' UPDATE Products SET Quantity = Quantity - ? WHERE ProductID = ? AND Quantity >= ?
' check "affected" after Execute; if 0 then Rollback

Notes and troubleshooting:

  • Use parameterized commands (shown above) and transactions so the insert + decrement are atomic.
  • If you get affected=0, that means insufficient stock; rollback and notify.
  • For .mdb use Provider=Microsoft.Jet.OLEDB.4.0; for .accdb use Provider=Microsoft.ACE.OLEDB.12.0.
  • After commit use the SaleID to query SaleLines + Products to format the payslip; you can render it with a simple print routine, an Access report, or export/print HTML/PDF.
  • If multiple users will access the DB often, test locking behavior carefully—Access (Jet/ACE) is not as robust as a client/server RDBMS.

Recommended Answers

All 3 Replies

Member Avatar for vb5prgrmr

UPDATE Table1 SET Field2=Table1.Field2-1 WHERE Field1=Value

Where Field1 = unique value to identify record and Field2 = the value you want to decrement by 1

As for creating an invoice there are several ways. Datareport, Crystal Report, or one of your own design via the Printer object. But to be able to do this you will need a purchase table to record the items purchased with some unique ID that ID's the customer so when you go to print the reciept, the table can be queried against.


Good Luck

Member Avatar for sackymatt

Hi,vb5prgrmr, with this one, do i do it in the database or in vb?
"UPDATE Table1 SET Field2=Table1.Field2-1 WHERE Field1=Value

Where Field1 = unique value to identify record and Field2 = the value you want to decrement by 1"

and for the crystal report, do i need to install it?

thanx

Member Avatar for vb5prgrmr

Either, if from vb use the command object to execute it.

As for crystal reports, yes you will need to install it. if you have pro or enterprise you should find on the disk a folder named crystal, inside is the installable. Run it and you will have crystal reports 4.5.


Good Luck

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.