Identifying the Action from a Yes/No Field in a Table

[ndbennett]

I am poking around the Northwind demo for Access 2016, trying to understand how it works, and have a question about the Discontinued option on the Products table - basically I cannot see what it is intended to do. I assumed that by selecting it, it would drop the product from the list of those needing to be reordered on the dashboard, but I see no effect when I tick it.



I have gone through all the identified Object Dependencies and none seem to rely on this field - am I missing something?

Thanks.

[Micron]

look at the
- alphabetical list of products query
- current product list query
- products by category query

- discontinued are not returned. There may be others where it is pertinent.

[ndbennett]

look at the
- alphabetical list of products query
- current product list query
- products by category query

- discontinued are not returned. There may be others where it is pertinent.

Thanks for your response but I am afraid I don't follow you. I don't have the queries that you refer to above.
It seems that the product re-order is managed through an Inventory query which counts the Re-order quantity based upon the following expression:

Qty To Reorder: IIf([Qty Below Target Level]>0,IIf([Qty Below Target Level]<[Minimum ReOrder Quantity],[Minimum Reorder Quantity],[Qty Below Target Level]),0)

But there is also an Inventory Module that states the following:

-----------------------------------------------------------
Option Compare Database
Option Explicit


Public Enum InventoryTransactionTypeEnum
Purchase_TransactionType = 1
Sold_TransactionType = 2
Hold_TransactionType = 3
End Enum


Type InventoryTransaction
ProductID As Long 'Product being added or removed to inventory
TransactionType As InventoryTransactionTypeEnum '1=Purchase; 2=Sale; 3=Hold; 4=Waste;
Quantity As Long 'Quanitity specifed for purchase, sale, hold, etc.
QuantityGranted As Long 'Actual Quanity Granted; may be less than specfied
InventoryID As Long 'Inventory Transaction ID returned to the caller
AllOrNothing As Boolean 'All or nothing flag for product allocations
Comments As String
End Type


Public Enum OrderItemStatusEnum
None_OrderItemStatus = 0
OnHold_OrderItemStatus = 1
Invoiced_OrderItemStatus = 2
Shipped_OrderItemStatus = 3
OnOrder_OrderItemStatus = 4
NoStock_OrderItemStatus = 5
End Enum


Private Const m_cNew_InventoryID = -1




Public Property Get NewInventoryID() As Long
NewInventoryID = m_cNew_InventoryID
End Property




Function AddPurchase(PurchaseOrderID As Long, ProductID As Long, Qty As Long, ByRef InventoryID As Long) As Boolean
Dim IT As InventoryTransaction

IT.TransactionType = Purchase_TransactionType
IT.ProductID = ProductID
IT.Quantity = Qty
IT.InventoryID = m_cNew_InventoryID

If EditTransaction(IT, , PurchaseOrderID) Then
AddPurchase = True
InventoryID = IT.InventoryID
End If
End Function




Function RemovePurchase(lInventoryID As Long)
MsgBoxOKOnly CannotRemovePostedInventory
End Function




Function GetQtyAvailable(ProductID As Long) As Long
GetQtyAvailable = GetInventoryQuantity("[Qty Available]", ProductID)
End Function




Function GetQtyOnHand(ProductID As Long) As Long
GetQtyOnHand = GetInventoryQuantity("[Qty On Hand]", ProductID)
End Function




Function GetQtyToReorder(ProductID As Long) As Long
GetQtyToReorder = GetInventoryQuantity("[Qty To Reorder]", ProductID)
End Function




Function GetQtyOnBackOrder(ProductID As Long) As Long
GetQtyOnBackOrder = GetInventoryQuantity("[Qty On Back Order]", ProductID)
End Function




Private Function GetInventoryQuantity(FieldName As String, ProductID As Long) As Long
GetInventoryQuantity = DLookupNumberWrapper(FieldName, "Inventory", "[Product ID] = " & ProductID)
End Function




Function RequestHold(OrderID As Long, IT As InventoryTransaction) As Boolean


IT.TransactionType = Hold_TransactionType

If (IT.InventoryID = m_cNew_InventoryID) Then
RequestHold = AddHold(OrderID, IT)
Else
RequestHold = ModifyHold(IT)
End If


End Function




Function AddHold(OrderID As Long, IT As InventoryTransaction) As Boolean
Dim QtyAvailable As Long
Dim QtyToHold As Long
Dim QtyRequested As Long

' Intialize Inventory quantities
QtyAvailable = GetQtyAvailable(IT.ProductID)
QtyRequested = IT.Quantity
QtyToHold = 0

' Check if we have sufficient Inventory
If QtyRequested > QtyAvailable Then
If Not IT.AllOrNothing Then
QtyToHold = QtyAvailable
End If
Else
QtyToHold = QtyRequested
End If

' Execute the Hold
If QtyToHold > 0 Then
IT.TransactionType = Hold_TransactionType
IT.Quantity = QtyToHold
AddHold = EditTransaction(IT, OrderID)
IT.Quantity = QtyRequested
Else
IT.QuantityGranted = 0
End If

End Function




Function ModifyHold(IT As InventoryTransaction) As Boolean
Dim ChangeInQuantity As Long
Dim IT_Existing As InventoryTransaction

' Get Information on Previous Hold
IT_Existing.InventoryID = IT.InventoryID
If GetTransaction(IT_Existing) Then
ChangeInQuantity = IT.Quantity - IT_Existing.Quantity

' Determine if we have sufficient Inventory to increase Hold
If ChangeInQuantity < 0 Or ChangeInQuantity < GetQtyAvailable(IT.ProductID) Then
IT.Quantity = IT.Quantity
If EditTransaction(IT) Then
IT.QuantityGranted = IT.Quantity
ModifyHold = True
Else
IT.QuantityGranted = IT_Existing.Quantity
End If
End If
End If


End Function




Function HoldToSold(InventoryID As Long) As Boolean
Dim IT As InventoryTransaction

IT.InventoryID = InventoryID
If GetTransaction(IT) Then
IT.TransactionType = Sold_TransactionType
If EditTransaction(IT) Then
HoldToSold = True
End If
End If
End Function




Function RemoveHold(InventoryID As Long) As Boolean
RemoveHold = DeleteTransaction(InventoryID)
End Function




Function GetTransaction(IT As InventoryTransaction) As Boolean
Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Inventory Transactions", "[Transaction ID] = " & IT.InventoryID) Then
With rsw.Recordset
If Not .EOF Then
IT.ProductID = ![Product ID]
IT.Quantity = ![Quantity]
IT.TransactionType = ![Transaction Type]
IT.Comments = Nz(![Comments])
GetTransaction = True
End If
End With
End If
End Function




Function EditTransaction(IT As InventoryTransaction, Optional CustomerOrderID, Optional PurchaseOrderID) As Boolean
Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Inventory Transactions", "[Transaction ID] = " & IT.InventoryID) Then
With rsw.Recordset
If IT.TransactionType <= 0 Then
Exit Function
ElseIf IT.InventoryID = m_cNew_InventoryID Then
rsw.AddNew
ElseIf .EOF Then
Exit Function
Else
rsw.Edit
![Transaction Modified Date] = Now()
End If

![Product ID] = IT.ProductID
![Quantity] = IT.Quantity
![Transaction Type] = IT.TransactionType
![Comments] = IIf(IT.Comments = "", Null, IT.Comments)
If Not IsMissing(CustomerOrderID) Then ![Customer Order ID] = CustomerOrderID
If Not IsMissing(PurchaseOrderID) Then ![Purchase Order ID] = PurchaseOrderID
EditTransaction = rsw.Update

If IT.InventoryID = m_cNew_InventoryID Then
rsw.Recordset.Bookmark = rsw.Recordset.LastModified
IT.InventoryID = ![Transaction ID]
End If
End With
End If
End Function




Function DeleteTransaction(InventoryID As Long) As Boolean
Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Inventory Transactions", "[Transaction ID] = " & InventoryID) Then
DeleteTransaction = rsw.Delete
End If
End Function




Function RestockProduct(ProductID As Long) As Boolean
Dim SupplierID As Long
Dim QtyToOrder As Long
Dim PurchaseOrderID As Long
Dim UnitCost As Long

QtyToOrder = GetQtyToReorder(ProductID)

If QtyToOrder > 0 Then

SupplierID = FindProductSupplier(ProductID)

If SupplierID > 0 Then

' Generate new Purchase Order if necessary
If PurchaseOrderID = 0 Then
If Not PurchaseOrders.Create(SupplierID, GetCurrentUserID(), -1, PurchaseOrderID) Then
Exit Function
End If
End If

' Set unit cost to standard cost for product
UnitCost = GetStandardCost(Nz(ProductID, 0))

' Add product line item to Purchase Order
If Not PurchaseOrders.CreateLineItem(PurchaseOrderID, ProductID, UnitCost, QtyToOrder) Then
Exit Function
End If


Else
' Suggested Enhancement: Handle case where product does not have a supplier
End If

End If


RestockProduct = True
End Function




Function FindProductSupplier(ProductID As Long) As Long
FindProductSupplier = DLookupNumberWrapper("[Supplier IDs].Value", "Products", "[ID]=" & ProductID)
End Function




Function GetRestockingPurchaseOrder(SupplierID) As Long
GetRestockingPurchaseOrder = DLookupNumberWrapper("[Purchase Order ID]", "Purchase Orders", "[Supplier ID]=" & SupplierID & " AND [Status ID] < 2")
End Function




Function FillBackOrders(ProductID) As Boolean
Dim rsw As New RecordsetWrapper
If rsw.OpenRecordset("Order Details", "[Product ID] =" & ProductID & " AND [Status ID] = " & OnOrder_OrderItemStatus) Then
With rsw.Recordset
Dim IT As InventoryTransaction
While Not .EOF
' Back Order Products should not be associated with any Inventory at this point
If IsNull(![Inventory ID]) Then
IT.Quantity = ![Quantity]
IT.ProductID = ![Product ID]
IT.InventoryID = m_cNew_InventoryID

If FillBackOrder(![Order ID], IT) Then
.Edit
![Status ID] = OnHold_OrderItemStatus
![Inventory ID] = IT.InventoryID
.Update
MsgBoxOKOnly FilledBackOrderedProduct, ![Order ID]
End If
End If

rsw.MoveNext
Wend
End With
FillBackOrders = True
End If
End Function




Function FillBackOrder(OrderID As Long, IT As InventoryTransaction) As Boolean
IT.TransactionType = Hold_TransactionType
IT.Comments = InsertString(FillBackOrderedProduct, CStr(OrderID))

If GetQtyAvailable(IT.ProductID) >= IT.Quantity Then
FillBackOrder = EditTransaction(IT)
End If
End Function
--------------------------------------------------------------------------------

As far as I can see, the Discontinued option does not have any bearing here :/

[Micron]

looking at the db Properties > Custom tab, I have version9. Maybe it's much older than yours?
If you have a table with a Y/N field, there must be a query somewhere therein that makes use of it. You will have to look for it to find the answer to your question - unless someone with a different version chimes in.

Maybe what you're looking for is in GetInventoryQuantity which I don't see in your posted code.
BTW, I think I can say for most of us, when you post a lot of code, please use code tags and good indentation/spacing.
Thanks.