VBA code for checking if quantity is greater than quantity in stock

[lisa09]

Hi,

Sorry I have another problem!

I am trying to create a simple database that records customer orders and checks stock levels.

I would like for when a user enters the quantity required for a particular product on my orders form, for a query to be run to see if the quantity (input) is greater than the quantityinstock field (tblproducts).

Currently I have added the following code to the quantity field on my orders form on the after update event:


Private Sub Quantity_AfterUpdate()

If QuantityinStock.Value < Quantity.Value Then
MsgBox "There is currently not enough stock to meet the quantity required. There is" & QuantityinStock & "available", vbInformation, "Stock"
Else
MsgBox "Product In Stock", vbInformation, "Stock"
End If

End Sub

It seems to be working ok, although on certain occasions i.e. when an order is modified it doesnt work correctly and displays the wrong message box i.e stock is available when it is not.

I do not know much about access or coding and was wondering if you could see anything wrong with the code.

Many thanks

 

[Uncle Gizmo]

This article provided by Allen Browne may provide you with some inspiration!

 

[godofhell]

The only thing I can think of is that you might be storing the values in a text field with settings set to variant. If that is the case the numbers will be treated as text and it will create this kind of issue. For example the number 1, 11, 2 will be treated as 11 being smaller than 2 because in the text world 1? comes before 2?. Try changing the values to Interger or Long before setting the QuantityinStock.Value < Quantity.Value line.

Private Sub Quantity_AfterUpdate()
Dim CurrentStock as Long
Dim SelectedStock as Long

CurrentStock = CLng(QuantityinStock.Value)
SelectedStock = CLng(Quantity.Value)

If CurrentStock < SelectedStock Then
MsgBox "There is currently not enough stock to meet the quantity required. There is" & CurrentStock & "available", vbInformation, "Stock"
Else
MsgBox "Product In Stock", vbInformation, "Stock"
End If
End Sub

 

[sonny]

Create this function and use it in your query.... I've never tested it but found the code some time ago.... If it doesnt work some may reply with a answer...

Function ZeroPad(pn As Variant) As Variant
    Const MAX_DIGITS = "0000"
    Dim i As Integer
    Dim c As String
    Dim Buf As String
    Dim PadNum As String
    'Dont convert null values
    If IsNull(pn) Then
        ZeroPad = Null
        Exit Function
    End If
    For i = 1 To Len(pn)
        c = Mid$(pn, i, 1)
        If InStr("0123456789", c) Then
            If PadNum = "" Then PadNum = MAX_DIGITS
            PadNum = Mid$(PadNum, 2) & c
        Else

            If PadNum <> "" Then
                Buf = Buf & PadNum
                PadNum = ""
            End If
            Buf = Buf & c
        End If
    Next i
    If PadNum <> "" Then Buf = Buf & PadNum
    ZeroPad = Buf
End Function

SELECT DISTINCTROW tblData.*
FROM tblData
ORDER BY ZeroPad([fldData]);