Show Products On hand only

[josros60 via AccessMonster.com]

Hi,

I have an inventory application use to keep track of products for guest in a
hotel.

I have a requisition form with combo field product, just want to know if
there's any way that this combo to show only products on hand when click the
the combo product field?

That mean 0 balance.

Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1

[KARL DEWEY]

Yes, query as combo source with criteria >0 would do it.

--
Build a little, test a little.

"josros60 via AccessMonster.com" wrote:

> .
>

[Steve]

You should have a product table that looks like:
TblProduct
ProductID
Product
ProductInventory (Number data type)
etc

Create a query based on this table. Include the three fields from
TblProduct. Sort ascending on Product to give you an alphabetical list of
products. Set the criteria for ProductInventory as >0. Use this query as the
rowsource of your combobox. With your requistion form in design view, select
the combobox and open Properties. Under the Data tab, set the Bound Column
property as 1. Go to the Format tab. Set Column Count to 3. Set Column Width
to 0;2;1. Your combobox will now display only the products you have onhand
and the available inventory of each product.

Steve
san...@penn.com

"josros60 via AccessMonster.com" <u56159@uwe> wrote in message
news:a0fd4ca7cd34c@uwe...

[KenSheridan via AccessMonster.com]

It depends on whether you are storing the current stock on hand quantity per
product in the Products table or not. If so then its simply a case of
restricting the combo box's RowSource to where the QtyOnHand (or whatever)
column is > 0.

A well designed stock control system will normally compute the current stock
positions from the stock movements, however, rather than storing them
redundantly, in which case you might have a StockMovements table or similar
in addition to a Products table, the former including, among others, columns
such as ProductID, QtyIn, QtyOut, the last two having DefaultValue properties
of zero in the table design (this is important). In this case the RowSource
for the combo box would be:

SELECT Products.ProductID, Product,
SUM(QtyIn - QtyOut)
FROM Products INNER JOIN StockMovements
ON Products.ProductID = StockMovements.ProductID
GROUP BY Products.ProductID, Product
HAVING SUM(QtyIn - QtyOut) > 0;

Requery this combo box in the form's AfterUpdate event procedure to take
account of the stock movement arising from the record entered in the form, e.
g.

Me.cboProduct.Requery

Ken Sheridan
Stafford, England

[josros60 via AccessMonster.com]

Hi,

Thanks all for your reply, but the Hand In Stock Units is based on modstock
module by Allen Browne, and the Requisition form the Unit In Stock is
calculated this way: =OnHand([fkProductID],Forms!FrmRequisitions!ReqDate).

By the way Allen Browne modstock module I pasted here and the tables I used
are below after the code, I just want to know a way in the requisition form
when I entering the goods delivered, just to show products on hand, and not
show "0" balance.

Function OnHand(vpkProductID As Variant, Optional vAsOfDate As Variant) As
Long


'Purpose: Return the quantity-on-hand for a product.
'Arguments: vpkProductID = the product to report on.
' vAsOfDate = the date at which quantity is to be calculated.
' If missing, all transactions are included.
'Return: Quantity on hand. Zero on error.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim lngProduct As Long 'vpkProductID as a long.
Dim strAsof As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last stocktake.
Dim lngQtyAcq As Long 'Quantity acquired since stocktake.
Dim lngQtyUsed As Long 'Quantity used since stocktake.

If Not IsNull(vpkProductID) Then
'Initialize: ValIDate and convert parameters.
Set db = CurrentDb()
lngProduct = vpkProductID
If IsDate(vAsOfDate) Then
strAsof = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If

'Get the last stocktake date and quantity for this product.
If Len(strAsof) > 0 Then
strDateClause = " AND (StockTakeDate <= " & strAsof & ")"
End If

strSQL = "SELECT TOP 1 " & _
"tblStockTakes.StockTakeDate, " & _
"tblStockTakeDetails.Quantity " & _
"FROM tblStockTakes LEFT JOIN tblStockTakeDetails ON " & _
"tblStockTakes.pkStockTakeID = tblStockTakeDetails.fkStockTakeID " &
_
"WHERE ((fkProductID = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"

Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy")
& "#"
lngQtyLast = Nz(!Quantity, 0)
End If
End With

rs.Close

If Len(strSTDateLast) > 0 Then
If Len(strAsof) > 0 Then
strDateClause = ">" & strSTDateLast & " and tblPurchaseOrders.
PODate<= " & strAsof

Else
strDateClause = " > " & strSTDateLast
End If
Else
If Len(strAsof) > 0 Then
strDateClause = " <= " & strAsof
Else
strDateClause = vbNullString
End If
End If

'Get the quantity acquired since then.
strSQL = "SELECT Sum(tblPurchaseOrderDetails.Quantity) AS QuantityAcq
" & _
"FROM tblPurchaseOrders INNER JOIN tblPurchaseOrderDetails ON " &
_
"tblPurchaseOrders.pkPurchaseOrderID = tblPurchaseOrderDetails.
fkPurchaseOrderID " & _
"WHERE ((tblPurchaseOrderDetails.fkProductID = " & lngProduct & ")
"

If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblPurchaseOrders.PODate " &
strDateClause & "));"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close

'Get the quantity used since then.
If Len(strSTDateLast) > 0 Then
If Len(strAsof) > 0 Then
' strDateClause = " Between " & strSTDateLast & " And " &
strAsOf
strDateClause = ">" & strSTDateLast & " and tblRequisitions.
ReqDate <= " & strAsof

Else
strDateClause = " > " & strSTDateLast
End If
Else
If Len(strAsof) > 0 Then
strDateClause = " <= " & strAsof
Else
strDateClause = vbNullString
End If
End If

strSQL = "SELECT Sum(tblRequisitionDetails.Quantity) AS QuantityUsed
" & _
"FROM tblRequisitions INNER JOIN tblRequisitionDetails ON " & _
"tblRequisitions.pkRequisitionID = tblRequisitionDetails.
fkRequisitionID " & _
"WHERE ((tblRequisitionDetails.fkProductID = " & lngProduct & ")
"

If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
strSQL = strSQL & " AND (tblRequisitions.ReqDate " &
strDateClause & "));"
End If

'Debug.Print strSQL

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)
End If
rs.Close

'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If

Set rs = Nothing
Set db = Nothing

' MsgBox lngQtyLast & " Last"
' MsgBox lngQtyAcq & " Acquired"
' MsgBox lngQtyUsed & " Used"

Exit Function
End Function

And the tables i have are:

TblProducts (By the way in this table there's a filed UnitInStock,but it's
blank.
TblCategories
TblRequsition
TblRequisitionDetails
TblPurchseOrders
TblPurchaseOrdersDetails
tblStockTakes
tblStockTakeDetails

Thank you so much again for your answers.

KenSheridan wrote:
>It depends on whether you are storing the current stock on hand quantity per
>product in the Products table or not. If so then its simply a case of
>restricting the combo box's RowSource to where the QtyOnHand (or whatever)
>column is > 0.
>
>A well designed stock control system will normally compute the current stock
>positions from the stock movements, however, rather than storing them
>redundantly, in which case you might have a StockMovements table or similar
>in addition to a Products table, the former including, among others, columns
>such as ProductID, QtyIn, QtyOut, the last two having DefaultValue properties
>of zero in the table design (this is important). In this case the RowSource
>for the combo box would be:
>
>SELECT Products.ProductID, Product,
>SUM(QtyIn - QtyOut)
>FROM Products INNER JOIN StockMovements
>ON Products.ProductID = StockMovements.ProductID
>GROUP BY Products.ProductID, Product
>HAVING SUM(QtyIn - QtyOut) > 0;
>
>Requery this combo box in the form's AfterUpdate event procedure to take
>account of the stock movement arising from the record entered in the form, e.
>g.
>
>Me.cboProduct.Requery
>
>Ken Sheridan
>Stafford, England
>

>>Hi,
>>
>[quoted text clipped - 8 lines]

[KenSheridan via AccessMonster.com]

You simply need to restrict the combo box's RowSource, e.g.

SELECT ProductID, Product
FROM TblProducts
WHERE OnHand(ProductID) > 0
ORDER BY Product;

Remember to requery the combo box after updating the form so that its list
takes account of the stock movement, i.e. the item will be removed from the
list if the stock movement has resulted in a zero balance.

Ken Sheridan
Stafford, England

>>It depends on whether you are storing the current stock on hand quantity per
>>product in the Products table or not. If so then its simply a case of

>[quoted text clipped - 30 lines]

[josros60 via AccessMonster.com]

Thank you,

But doesn't work because Units In Stock field is based on this calculation on
My Requisitin form:

=OnHand([fkProductID],Forms!FrmRequisitions!ReqDate)

I wonder if there's a code i can put on before update field combo?

thank you

KenSheridan wrote:
>You simply need to restrict the combo box's RowSource, e.g.
>
>SELECT ProductID, Product
>FROM TblProducts
>WHERE OnHand(ProductID) > 0
>ORDER BY Product;
>
>Remember to requery the combo box after updating the form so that its list
>takes account of the stock movement, i.e. the item will be removed from the
>list if the stock movement has resulted in a zero balance.
>
>Ken Sheridan
>Stafford, England
>

>>Hi,
>>
>[quoted text clipped - 174 lines]

[KenSheridan via AccessMonster.com]

That doesn't matter. You presumably want the combo box to list products in
stock as of the date of the current requisition. The second argument of
Allen's function determines at what date the stock on hand number is computed,
so when calling the function in the combo box's RowSource you can pass the
value of the form's ReqDate control into the function. As the RowSource is
presumably based on a Products table or similar, instead of passing the value
of the form's fkProductID field as the first argument you'd pass the value of
the Products table's primary key column as the argument.

You'd need to requery the combo box in two places:

1. In the form's Current event procedure so the list is updated to reflect
the date of the current requisition.

2. In the ReqDate control's AfterUpdate event procedure so its requeried to
reflect the date entered. Bear in mind, however, that this does assume that
requisitions are entered in their correct date order. If you entered a
requisition dated today for a product, and then entered another one for the
same product but dated yesterday, the list would not take account of the
requisition made today. I'd imagine that this is unlikely to happen, though.

If you can post the SQL for the combo box's RowSource as it currently is we
should be able to amend it for you.

Ken Sheridan
Stafford, England

josros60 wrote:
>Thank you,
>
>But doesn't work because Units In Stock field is based on this calculation on
>My Requisitin form:
>
>=OnHand([fkProductID],Forms!FrmRequisitions!ReqDate)
>
>I wonder if there's a code i can put on before update field combo?
>
>thank you
>

>>You simply need to restrict the combo box's RowSource, e.g.
>>

>[quoted text clipped - 15 lines]

[josros60 via AccessMonster.com]

Thank you very much.

Here it's the SQL of RowSource of combo:

SELECT tblProducts.pkProductID, tblProducts.ProductName FROM tblProducts
ORDER BY tblProducts.ProductName;

Thank you Again

KenSheridan wrote:
>That doesn't matter. You presumably want the combo box to list products in
>stock as of the date of the current requisition. The second argument of
>Allen's function determines at what date the stock on hand number is computed,
>so when calling the function in the combo box's RowSource you can pass the
>value of the form's ReqDate control into the function. As the RowSource is
>presumably based on a Products table or similar, instead of passing the value
>of the form's fkProductID field as the first argument you'd pass the value of
>the Products table's primary key column as the argument.
>
>You'd need to requery the combo box in two places:
>
>1. In the form's Current event procedure so the list is updated to reflect
>the date of the current requisition.
>
>2. In the ReqDate control's AfterUpdate event procedure so its requeried to
>reflect the date entered. Bear in mind, however, that this does assume that
>requisitions are entered in their correct date order. If you entered a
>requisition dated today for a product, and then entered another one for the
>same product but dated yesterday, the list would not take account of the
>requisition made today. I'd imagine that this is unlikely to happen, though.
>
>If you can post the SQL for the combo box's RowSource as it currently is we
>should be able to amend it for you.
>
>Ken Sheridan
>Stafford, England
>

>>Thank you,
>>
>[quoted text clipped - 12 lines]

[KenSheridan via AccessMonster.com]

This should work:

SELECT pkProductID, ProductName
FROM tblProducts
WHERE OnHand(pkProductID,Nz(Forms!FrmRequisitions!ReqDate, DATE()) > 0
ORDER BY ProductName;

Using the Nz function here to return the current date if the control is Null
will mean that the list shows the products in stock at the current date when
you navigate to a new record in the form. By requerying it in the ReqDate
control's AfterUpdate the list will be updated to show the products in stock
at that date should a different date be entered. Don't forget to requery it
in the form's Current event procedure as well.

I'm signing off now until after the holiday.

Ken Sheridan
Stafford, England

josros60 wrote:
>Thank you very much.
>
>Here it's the SQL of RowSource of combo:
>
>SELECT tblProducts.pkProductID, tblProducts.ProductName FROM tblProducts
>ORDER BY tblProducts.ProductName;
>
>Thank you Again
>

>>That doesn't matter. You presumably want the combo box to list products in
>>stock as of the date of the current requisition. The second argument of

>[quoted text clipped - 28 lines]