Help Me Please...

[DManAxe]

Hi, I've a table with the following columns

RecdQty SStock PStock


What I need to do is something like this

let's say PStock = 10,000 , SStock =6,000

and let say RecdQty value = 100 for the first record
" = 300 for the second record
" = 200 for the third record etc.

Start:

PStock - SStock = 4,000 <-- once ive got this value then


4,000 - RecdQty = 3,900 then

<-----NextRecord------>

3,900 - RecdQty = 3,600 then

<-----NextRecord------>

3,600 - RecdQty = 3,400

....
..
.

Until EOF

Please HelpMe I'm Lost.....

 

[CJBIRKIN]

Hi

Something like this (access2000 you'll need the tools>references>microsoft dao3.6 object library)

Not sure where you are running this from, if called from a query as a function in a module then.


public function StockCalc (PStock as long, SStock as long) as Long

Dim dao.rst as recordset
Dim Dao.db as database
Dim IntStockVal as Integer

Set db = currentdb()
Set rst = db.openrecordset ("Select Tbl_Stock.* from Tbl_Stock;")


If rst.recordcount = 0 Then

Msgbox "No Records",VBokonly,"Error"
exit function
else

Rst.movefirst


IntStockVal = PStock - SStock
Rst.movefirst

Do until rst.eof = true
IntStockVal = IntStockVal-rst![RecdQty]
rst.movenext
loop

end if

StockCount = IntStockVal

end function

Should give you some ideas

Chris

 

[Fizzio]

Rather than just subtracting the value, why not create an audit trail of stock increases and reductions (don't know the correct terminology)
ie
have tables for the following:

Item to be tracked
Stock purchases (for each Item)
Stock Sales (for each Item)

You can then track when you ordered stock, sold or despached items by date and have a more accurate analysis of your stock movements. Yo can then simply calculate your currext stock level dynamically which will respond immediately to purchases or despaches/sales

 

[Rich]

What about shrinkage?

 

[Fizzio]

What's that? (I only know the type I often experience by my poor washing/tumble drying combinations)

 

[Rich]

"Losses", pilferage, damaged goods etc.
There has to be some means of adjusting stock levels
In any event in this case a simple RunningSum in a report will probably suffice

 

[Fizzio]

Rich, how would you structure the Db to account for this. Can you not just have a 'despatch' code to shrinkage or is it better to program it some other way?
Working in the NHS, nothing gets lost, 'borrowed' etc (heh )

 

[Rich]

http://users.bigpond.net.au/abrowne1/AppInventory.html

 

[DManAxe]

CJBIRKIN I try your code and it's give error "94" Invalid use of null


any suggestions...


This is for a Low Inventory report...

 

[Fizzio]

Cheers.

 

[DManAxe]

CJBIRKIN I try your code but I never get it to work, but thanks for your help.... Thank a lot...

well I finally did this,


Public LastResult As Long


Function GetResult(PStock As Long, SStock As Long, RecdQty As Long) As Long

If LastResult=-1 Then
GetResult = PStock - SStock
LastResult = GetResult
Else
GetResult = LastResult - RecRecdQty
LastResult = GetResult
End If

End Function


I Initialize LastResult to -1 before first calling the function.



props to Mangro.

Cheers