Whats the code to Update current stock level (1 Viewer)

[colepale]

Hello Guys,

Please i would require your assistance on an inventory stock management application i am creating using MS Eexcel and Access.

I would require a VBA code to update current stock level whenever sales is made.

An Example if i have

50 items of Pencils and 30 was sold how do i update my stock level immediately to reflect the balance of 20

I will appreciate a swift response either using MS access or excel

 

[arnelgp]

the normal thing is, you Don't save it.
you run a query and perform the arithmetic there:

supposed you have a table (tblTransaction)
ID (autonumber)
TransDate (date)
TransType (short text, "in" or "out") ("in" if you bought it and added to inventory, "out" if you sell it)
ItemCode (short text)
Quantity (Numeric)


to get the Remaining balance of an Item using Query:

SELECT ItemCode, SUM(IIF(TransType="in", Quantity, -Quantity)) As RemainingBalance
FROM tblTransaction
GROUP BY ItemCode

 

[Gasman]

I would say that the method would be the same in either app.?
You subtract Out from In?

In Excel, the calculation would likely be immediate. In Access you would run a query/formula to calculate the same way.

You would NOT store the 'balance'

 

[colepale]

the normal thing is, you Don't save it.
you run a query and perform the arithmetic there:

supposed you have a table (tblTransaction)
ID (autonumber)
TransDate (date)
TransType (short text, "in" or "out") ("in" if you bought it and added to inventory, "out" if you sell it)
ItemCode (short text)
Quantity (Numeric)


to get the Remaining balance of an Item using Query:

SELECT ItemCode, SUM(IIF(TransType="in", Quantity, -Quantity)) As RemainingBalance
FROM tblTransaction
GROUP BY ItemCode

Thank you this information was useful

 

[colepale]

I would say that the method would be the same in either app.?
You subtract Out from In?

In Excel, the calculation would likely be immediate. In Access you would run a query/formula to calculate the same way.

You would NOT store the 'balance'

Thank you for your quick response, In excel whats the formular for immediate update on each item?

 

[June7]

For Access review http://allenbrowne.com/AppInventory.html

For Excel, maybe cell formula that subtracts Out column from In column and a Sum row at bottom. Why consider Excel at all?

 

[Gasman]

Thank you for your quick response, In excel whats the formular for immediate update on each item?

I would use a SUMIF on each of the items. Outgoings should be be a negative, then just Sum the cells. If you have a separate column for outgoing then subtract that total from the incoming total.

Also try Googling. There must be plenty of links to such workbooks?