Quantity on hand calculation

P

paulu

I have some code which runs from the AfterUpdate event of a combo box in
which a product code is selected. The code is supposed to calculate the
stock on hand based on:
1. stock count at most recent stock take
2. qty of stock received since lattest stock count
3. qty of stock shipped out but not sold since lattest stock count
4. qty sold since lattest stock count

The code is as follows;

Private Sub cboProductSelect_AfterUpdate()
On Error GoTo Err_cboProductSelect_AfterUpdate



'Purpose: the purpose of this code is to display product details
including the quantity of the selected product on hand

' Find the record that matches the control.
Dim rs As Object

Dim Last44STIdent As Long ': Warehouse 44 last stock movement ID as
Long Integer
Dim Last44STDate As Date ': Warehouse 44 last stock date as date
Dim Last44STQty As Double ': Warehouse 44 quantity on hand at
last stock take as double
Dim Qty44Out As Double ': Warehouse 44 quantity transferred
out since last stock take as Double
Dim Qty44In As Double ': Warehouse 44 quantity transferred in
since last stock take date as double
Dim Qty44Sold As Double ': Warehouse 44 quantity sold since
last stock take as double
Dim OnHand44 As Double ': Warehouse 44 quantity on hand as
double
Dim strSQL As String ': SQL statement

Dim Last42STIdent As Double ': Warehouse 42 last stock movement ID
as double
Dim Last42STDate As Date ': Warehouse 42 last stock date as date
Dim Last42STQty As Double ': Warehouse 42 quantity on hand at
last stock take as double
Dim Qty42Out As Double ': Warehouse 42 quantity transferred
out since last stock take as Double
Dim Qty42In As Double ': Warehouse 42 quantity transferred in
since last stock take date as double
Dim Qty42Sold As Double ': Warehouse 42 quantity sold since
last stock take as double
Dim OnHand42 As Double ': Warehouse 42 quantity on hand as
double

Dim wareHouse44 As String
Dim wareHouse42 As String
Dim crtProduct As Integer



'Identify the product

crtProduct = Me![cboProductSelect]


'Find the record for the selected product

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(crtProduct, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
EnableControls Me, acDetail, True
Me.ProductCode.SetFocus


' Find the last stock take Stock Movement ID for each warehouse

Last44STIdent = Nz(DMax("StockMovementID", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & "AND
[StockMovementTypeID] = 1"), 0)

Last42STIdent = Nz(DMax("StockMovementID", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & "AND
[StockMovementTypeID] = 1"), 0)



' Find the last stock take date for each warehouse

Last44STDate = DLookup("StockMovementDate", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & "AND [StockMovementID]
= " & Last44STIdent)

Last42STDate = DMax("StockMovementDate", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & "AND [StockMovementID]
= " & Last42STIdent)


'Find the quantity held in stock by each warehouse at their last stock
take

Last44STQty = DLookup("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & _
"AND [StockMovementTypeID] = 1" & "AND [StockMovementID] = " &
Last44STIdent)

Last42STQty = DLookup("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & _
"AND [StockMovementTypeID] = 1" & "AND [StockMovementID] = " &
Last42STIdent)



'Find the non sale quantity that has left warehouse 44 since last stock
take

Qty44Out = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & _
"AND [StockMovementTypeID] = 3" & "AND [StockMovementID] = " &
Last44STIdent)

Qty42Out = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & _
"AND [StockMovementTypeID] = 3" & "AND [StockMovementID] = " &
Last42STIdent)


'Find the quantity that has come into warehouse 44 since last stoack take

Qty44In = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & "AND
[StockMovementTypeID] = 2" & _
"AND [StockMovementID] = " & Last44STIdent)

Qty42In = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & "AND
[StockMovementTypeID] = 2" & _
"AND [StockMovementID] = " & Last42STIdent)



'Find the quantity sold from warehouse 44 since last stock take

Qty44Sold = Nz(DSum("ShippedQuantity", "qryShippedQuantityByProduct",
"[ProductID]=" _
& crtProduct & "AND [Warehouse] = '44'" & "AND [ShippedDate] >#" &
Last44STDate & "#"), 0)

Qty42Sold = Nz(DSum("ShippedQuantity", "qryShippedQuantityByProduct",
"[ProductID]=" _
& crtProduct & "AND [Warehouse] = '42'" & "AND [ShippedDate] >#" &
Last42STDate & "#"), 0)


'Calculate the quantity on hand at warehouse 44

OnHand44 = Last44STQty + StockNumberIn44 - StockNumberOut44 - Qty44Sold

OnHand42 = Last42STQty + StockNumberIn42 - StockNumberOut42 - Qty42Sold


'Update the product info display

Me!txtCairnsStockCount = OnHand44

Me!txtTownsvilleStockCount = OnHand42

Me.ProductCode.SetFocus



Exit_cboProductSelect_AfterUpdate:
Exit Sub

Err_cboProductSelect_AfterUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cboProductSelect_AfterUpdate


End Sub

The problem I get an error "Invalid use of Null" calculating quantity into
the warehouse and quantity out of the warehose which seems to be related to
the "StockMovementTypeID" criteria but I can't figure it out. It is probably
something simple so I thought someone else may quickly see what I can't.

Any suggestions greatley appreciated

Cheers
 
D

DavidBoyle via AccessMonster.com

The best way to work out what is going on in this kind of situation is by
examining your code in debug mode. Most people who read your post will have
little idea why your variables are null. There are two ways to do this.
First is to put a "stop" statement in the code at the point that you want to
pause execution. I would suggest just below Me.ProductCode.SetFocus. Just put
the single line statement on a line of its own and program execution will
halt in debug mode. Use F8 to step through the code.

Alternatively in your code in the vb editor position your cursor in a
statement line right click and select "Toggle" and choose break point. This
has the advantage of being less persistent - you have to remember to remove
the stop statement after using it.

You can then use the immediate window (Press Control G when execution stops
in your code) and interrogate the value of the variables as the process runs.
type in ? myvar then return for example. This should let you identify the
null value and will allow you to make an appropriate amendment to your code
logic.
I have some code which runs from the AfterUpdate event of a combo box in
which a product code is selected. The code is supposed to calculate the
stock on hand based on:
1. stock count at most recent stock take
2. qty of stock received since lattest stock count
3. qty of stock shipped out but not sold since lattest stock count
4. qty sold since lattest stock count

The code is as follows;

Private Sub cboProductSelect_AfterUpdate()
On Error GoTo Err_cboProductSelect_AfterUpdate


'Purpose: the purpose of this code is to display product details
including the quantity of the selected product on hand

' Find the record that matches the control.
Dim rs As Object

Dim Last44STIdent As Long ': Warehouse 44 last stock movement ID as
Long Integer
Dim Last44STDate As Date ': Warehouse 44 last stock date as date
Dim Last44STQty As Double ': Warehouse 44 quantity on hand at
last stock take as double
Dim Qty44Out As Double ': Warehouse 44 quantity transferred
out since last stock take as Double
Dim Qty44In As Double ': Warehouse 44 quantity transferred in
since last stock take date as double
Dim Qty44Sold As Double ': Warehouse 44 quantity sold since
last stock take as double
Dim OnHand44 As Double ': Warehouse 44 quantity on hand as
double
Dim strSQL As String ': SQL statement

Dim Last42STIdent As Double ': Warehouse 42 last stock movement ID
as double
Dim Last42STDate As Date ': Warehouse 42 last stock date as date
Dim Last42STQty As Double ': Warehouse 42 quantity on hand at
last stock take as double
Dim Qty42Out As Double ': Warehouse 42 quantity transferred
out since last stock take as Double
Dim Qty42In As Double ': Warehouse 42 quantity transferred in
since last stock take date as double
Dim Qty42Sold As Double ': Warehouse 42 quantity sold since
last stock take as double
Dim OnHand42 As Double ': Warehouse 42 quantity on hand as
double

Dim wareHouse44 As String
Dim wareHouse42 As String
Dim crtProduct As Integer



'Identify the product

crtProduct = Me![cboProductSelect]


'Find the record for the selected product

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(crtProduct, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
EnableControls Me, acDetail, True
Me.ProductCode.SetFocus


' Find the last stock take Stock Movement ID for each warehouse

Last44STIdent = Nz(DMax("StockMovementID", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & "AND
[StockMovementTypeID] = 1"), 0)

Last42STIdent = Nz(DMax("StockMovementID", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & "AND
[StockMovementTypeID] = 1"), 0)



' Find the last stock take date for each warehouse

Last44STDate = DLookup("StockMovementDate", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & "AND [StockMovementID]
= " & Last44STIdent)

Last42STDate = DMax("StockMovementDate", "qryStockMovement",
"[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & "AND [StockMovementID]
= " & Last42STIdent)


'Find the quantity held in stock by each warehouse at their last stock
take

Last44STQty = DLookup("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & _
"AND [StockMovementTypeID] = 1" & "AND [StockMovementID] = " &
Last44STIdent)

Last42STQty = DLookup("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & _
"AND [StockMovementTypeID] = 1" & "AND [StockMovementID] = " &
Last42STIdent)



'Find the non sale quantity that has left warehouse 44 since last stock
take

Qty44Out = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & _
"AND [StockMovementTypeID] = 3" & "AND [StockMovementID] = " &
Last44STIdent)

Qty42Out = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & _
"AND [StockMovementTypeID] = 3" & "AND [StockMovementID] = " &
Last42STIdent)


'Find the quantity that has come into warehouse 44 since last stoack take

Qty44In = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '44'" & "AND
[StockMovementTypeID] = 2" & _
"AND [StockMovementID] = " & Last44STIdent)

Qty42In = DSum("Quantity", "qryStockMovement", "[ProductID]=" _
& crtProduct & "AND [BusinessCentreID] = '42'" & "AND
[StockMovementTypeID] = 2" & _
"AND [StockMovementID] = " & Last42STIdent)



'Find the quantity sold from warehouse 44 since last stock take

Qty44Sold = Nz(DSum("ShippedQuantity", "qryShippedQuantityByProduct",
"[ProductID]=" _
& crtProduct & "AND [Warehouse] = '44'" & "AND [ShippedDate] >#" &
Last44STDate & "#"), 0)

Qty42Sold = Nz(DSum("ShippedQuantity", "qryShippedQuantityByProduct",
"[ProductID]=" _
& crtProduct & "AND [Warehouse] = '42'" & "AND [ShippedDate] >#" &
Last42STDate & "#"), 0)


'Calculate the quantity on hand at warehouse 44

OnHand44 = Last44STQty + StockNumberIn44 - StockNumberOut44 - Qty44Sold

OnHand42 = Last42STQty + StockNumberIn42 - StockNumberOut42 - Qty42Sold


'Update the product info display

Me!txtCairnsStockCount = OnHand44

Me!txtTownsvilleStockCount = OnHand42

Me.ProductCode.SetFocus



Exit_cboProductSelect_AfterUpdate:
Exit Sub

Err_cboProductSelect_AfterUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cboProductSelect_AfterUpdate


End Sub

The problem I get an error "Invalid use of Null" calculating quantity into
the warehouse and quantity out of the warehose which seems to be related to
the "StockMovementTypeID" criteria but I can't figure it out. It is probably
something simple so I thought someone else may quickly see what I can't.

Any suggestions greatley appreciated

Cheers
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with creative thinking needed 7
Calc Sales based on Stock Count 3
Calculating Inventory Quantities 0
Condition and equation problems 4
Calculating Quantity On Hand 2
Stock Value Calculation 0
VB Help 12
Complex problem in calculating when to order an item 1

Top