Optional Variant in Inventory Control code

C

ChrisMore

Member
Local time
Today, 06:52
Joined
Jan 28, 2020
Messages
187
Hi everyone,

I'm trying to add an optional variant to the below inventory control code. I would like the code to output the current inventory for a particular zone of the warehouse if specified when calling the code. If a zone isn't specified, the code will return the current inventory for all zones. The zones are A, B, C and D.

I'm currently working on the stocktake part of the code and I've started by adding vZone as the variant and declaring strZone. However, I am currently getting a type mismatch error if I don't specify the zone when calling the code.

I would like 'lngQtyLast' to look at either ZoneA_Quantity, ZoneB_Quantity, ZoneC_Quantity or ZoneD_Quantity, depending on which zone has been specified. I would like it to look at Product_Quantity if no zone is specified.

Using the below stocktake data as an example, the inventory for product code 250 should be 11437 when calling the code using the expression OnHand([Product_Code],#05/01/2024#,"A").

Stocktake_Date Product_CodeProduct_Quantity ZoneA_Quantity ZoneB_Quantity ZoneC_Quantity ZoneD_Quantity
05/01/20242504143711437100001000010000


Any help would be much appreciated.

Thanks, Chris

Code: 
  Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant, Optional vZone As Variant) As Double
  Dim DB As DAO.Database          'CurrentDb()
    Dim rs As DAO.Recordset         'Various recordsets.
    Dim lngProduct As Long          'vProduct_Code as a long.
    Dim strAsOf As String           'vAsOfDate as a string.
    Dim strZone As String           'vZone 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 Single        'Quantity at last stocktake.
    Dim lngQtyAcq As Single         'Quantity acquired since stocktake.
    Dim lngQtyUsed As Single        'Quantity used since stocktake.
    Dim lngNegAdjust As Single      'Quantity Negative Adjusted since stocktake.
    Dim lngPosAdjust As Single      'Quantity Positive Adjusted since stocktake.
   
    If Not IsNull(vProduct_Code) Then
        'Initialize: Validate and convert parameters.
        Set DB = CurrentDb()
        lngProduct = vProduct_Code
        strZone = vZone
        If IsDate(vAsOfDate) Then
            strAsOf = "#" & Format$(vAsOfDate, "dd\/mm\/yyyy") & "#"
        End If
       
       

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTake_Date <= " & strAsOf & ")"
        End If
        strSql = "SELECT TOP 1 Stocktake_Date, Product_Quantity, ZoneA_Quantity, ZoneB_Quantity, ZoneC_Quantity, ZoneD_Quantity FROM Stocktake " & _
            "WHERE ((Product_Code = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY Stocktake_Date DESC;"
       
       
        Set rs = DB.OpenRecordset(strSql)
        With rs
            If .RecordCount > 0 Then
                strSTDateLast = "#" & Format$(!Stocktake_Date, "mm\/dd\/yyyy") & "#"
                lngQtyLast = Nz(!Product_Quantity, 0)
            End If
        End With
        rs.Close

        'Build the Date clause
        If Len(strSTDateLast) > 0 Then
            If Len(strAsOf) > 0 Then
                strDateClause = " Between " & strSTDateLast & " And " & 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(Purchase_Orders_Deliveries.Quantity_Delivered) AS Amount_of_Goods_Received " & _
            "FROM Purchase_Orders_Items INNER JOIN Purchase_Orders_Deliveries ON Purchase_Orders_Items.Purchase_Order_Items_ID = Purchase_Orders_Deliveries.Purchase_Order_Items_ID " & _
            "WHERE ((Purchase_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSql = strSql & ");"
        Else
            strSql = strSql & " AND (Purchase_Orders_Deliveries.Goods_Delivery_Date " & strDateClause & "));"
        End If

        Set rs = DB.OpenRecordset(strSql)
        If rs.RecordCount > 0 Then
            lngQtyAcq = Nz(rs!Amount_of_Goods_Received, 0)
        End If
        rs.Close

          'Get the quantity used since then.
        strSql = "SELECT Sum(Customer_Orders_Items.Order_Quantity) AS Order_Quantity " & _
            "FROM Shipments INNER JOIN Customer_Orders_Items ON " & _
            "Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number " & _
            "WHERE ((Customer_Orders_Items.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSql = strSql & ");"
        Else
            strSql = strSql & " AND (Shipments.Production_Complete_Date " & strDateClause & "));"
        End If

        Set rs = DB.OpenRecordset(strSql)
        If rs.RecordCount > 0 Then
            lngQtyUsed = Nz(rs!Order_Quantity, 0)
        End If
        rs.Close
       
            'Get the quantity negatively adjusted since then.
        strSql = "SELECT Sum(Negative_Adjustments.Negative_Adjustment_Quantity) AS Negative_Quantity " & _
            "FROM Negative_Adjustments " & _
            "WHERE ((Negative_Adjustments.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSql = strSql & ");"
        Else
            strSql = strSql & " AND (Negative_Adjustments.Negative_Adjustment_Date " & strDateClause & "));"
        End If

        Set rs = DB.OpenRecordset(strSql)
        If rs.RecordCount > 0 Then
            lngNegAdjust = Nz(rs!Negative_Quantity, 0)
        End If
        rs.Close
       
            'Get the quantity positively adjusted since then.
        strSql = "SELECT Sum(Positive_Adjustments.Positive_Adjustment_Quantity) AS Positive_Quantity " & _
            "FROM Positive_Adjustments " & _
            "WHERE ((Positive_Adjustments.Product_Code = " & lngProduct & ")"
        If Len(strDateClause) = 0 Then
            strSql = strSql & ");"
        Else
            strSql = strSql & " AND (Positive_Adjustments.Positive_Adjustment_Date " & strDateClause & "));"
        End If

        Set rs = DB.OpenRecordset(strSql)
        If rs.RecordCount > 0 Then
            lngPosAdjust = Nz(rs!Positive_Quantity, 0)
        End If
        rs.Close

        'Assign the return value
        onhand = lngQtyLast + lngQtyAcq - lngQtyUsed - lngNegAdjust + lngPosAdjust
    End If

    Set rs = Nothing
    Set DB = Nothing
    Exit Function
End Function
 
Code: 
....,Optional vZone As Variant = "None"

strZone = vZone
If strZone = "None"
  code
else

end if
 
Thanks!

That removed the error and I got the code working using the following:
Code: 
                If strZone = "None" Then
                lngQtyLast = Nz(!Product_Quantity, 0)
                Else
                If strZone = "A" Then
                lngQtyLast = Nz(!ZoneA_Quantity, 0)
                Else
                If strZone = "B" Then
                lngQtyLast = Nz(!ZoneB_Quantity, 0)
                Else
                If strZone = "C" Then
                lngQtyLast = Nz(!ZoneC_Quantity, 0)
                Else
                If strZone = "D" Then
                lngQtyLast = Nz(!ZoneD_Quantity, 0)
                Else
                End If
                End If
                End If
                End If
                End If
 
A few thoughts:
You can also check explicitly with IsMissing.
Code: 
Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant, Optional vZone As Variant) As Double
   ...
   If IsMissing(vZone) Then
      lngQtyLast = Nz(!Product_Quantity, 0)
   ElseIf vZone Like "[ABCD]" Then
      lngQtyLast = Nz(.Controls("Zone" & vZone & "_Quantity"), 0)
   Else
      Err.Raise ...
   End If
   ...
End Function

In database applications, Null as standard could perhaps also be useful if, for example, Null could also be passed.
Code: 
Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant, Optional vZone As Variant = Null) As Double
   ...
   If IsNull(vZone) Then
      lngQtyLast = Nz(!Product_Quantity, 0)
   ElseIf vZone Like "[ABCD]" Then
      lngQtyLast = Nz(.Controls("Zone" & vZone & "_Quantity"), 0)
   Else
       Err.Raise ...
   End If
   ...
End Function
 
ChrisMore said:
Thanks!

That removed the error and I got the code working using the following:
Code: 
                If strZone = "None" Then
                lngQtyLast = Nz(!Product_Quantity, 0)
                Else
                If strZone = "A" Then
                lngQtyLast = Nz(!ZoneA_Quantity, 0)
                Else
                If strZone = "B" Then
                lngQtyLast = Nz(!ZoneB_Quantity, 0)
                Else
                If strZone = "C" Then
                lngQtyLast = Nz(!ZoneC_Quantity, 0)
                Else
                If strZone = "D" Then
                lngQtyLast = Nz(!ZoneD_Quantity, 0)
                Else
                End If
                End If
                End If
                End If
                End If
Click to expand...
Perhaps review Select Case ?
 
Josef P. said:
A few thoughts:
You can also check explicitly with IsMissing.
Code: 
Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant, Optional vZone As Variant) As Double
   ...
   If IsMissing(vZone) Then
      lngQtyLast = Nz(!Product_Quantity, 0)
   ElseIf vZone Like "[ABCD]" Then
      lngQtyLast = Nz(.Controls("Zone" & vZone & "_Quantity"), 0)
   Else
      Err.Raise ...
   End If
   ...
End Function

In database applications, Null as standard could perhaps also be useful if, for example, Null could also be passed.
Code: 
Public Function onhand(vProduct_Code As Variant, Optional vAsOfDate As Variant, Optional vZone As Variant = Null) As Double
   ...
   If IsNull(vZone) Then
      lngQtyLast = Nz(!Product_Quantity, 0)
   ElseIf vZone Like "[ABCD]" Then
      lngQtyLast = Nz(.Controls("Zone" & vZone & "_Quantity"), 0)
   Else
       Err.Raise ...
   End If
   ...
End Function
Click to expand...
Thanks for the suggestion, I've tried to implement the code and I'm getting a Compile error for .Controls in lngQtyLast = Nz(.Controls("Zone" & vZone & "_Quantity"), 0).

Any thoughts?
 
Change Controls to Fields ... I overlooked the fact that you take the values from a recordset.
lngQtyLast = Nz(.Fields("Zone" & vZone & "_Quantity"), 0)
 
Ideally you don't want to store the zone quantities in separate columns. It would probably be easier to use a table with multiple columns. Sort of zone = A and qty = number. That way you aren't limited to a set number of zones, and you can filter and sum much more easily.
 
You must log in or register to reply here.

Similar threads

C
Solved Modifying Allen Browne's Inventory Control to display decimal numbers
Replies
4
Views
982
Pat Hartman
P
R
  • Question Question
Using vba to select a particular record in a combo box is not working properly
Replies
4
Views
286
cheekybuddha
cheekybuddha
zelarra821
Solved Loop through records in a table that gives an error
Replies
4
Views
147
zelarra821
zelarra821
T
Can someone please assist with the VBA code Below - Error Message 3075
Replies
16
Views
389
CJ_London
C
N
Solved How to grab the last updated record in a table
Replies
10
Views
335
June7
June7

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom