CODEWITHSUNDEEP
vbams-access
Miguel Santiago
Miguel Santiago

Reputation: 5

How to check if quantity is more or less on a table?

I need to check if I have enough quantity of a specific product. How do I make it so the quantity that it checks is the one of the product in a textbox(txtIdProd).

This is my code:

Private Sub Cantidad_LostFocus()
Set myDatabase = CurrentDb()
Set myRs = myDatabase.OpenRecordset("TblProductos", dbOpenDynaset)
Dim cant As Integer
cant = txtCantidad
myRs.FindFirst "IDProducto=" & Me.txtIdProd
If myRs.NoMatch = False Then
    If cant > myRs("CantidadDisponible") Then
        Me.Label27.Visible = True
    End If
End If
Exit Sub
Me.Label27.Visible = False

End Sub

Upvotes: 0

Views: 43

Answers (1)

June7
June7

Reputation: 21370

The FindFirst code should do exactly what you want. However, probably more efficient to filter the recordset. 

Private Sub Cantidad_LostFocus()
    Dim cant As Integer
    If Not IsNull(Me.txtIDProd) Then
        Set myDatabase = CurrentDb()
        Set myRs = myDatabase.OpenRecordset("SELECT CantidadDisponible FROM TblProductos WHERE IDProducto=" & Me.txtIdProd, dbOpenDynaset)
        cant = rs!CantidadDisponible
    End If
    Me.Label27.Visible = Me.txtCantidad <= cant
End Sub

Another approach that doesn't use recordset:

Me.Label27.Visible = Me.txtCantidad <= Nz(DLookup("CantidadDisponible", "TblProductos", "IDproducto=" & Nz(Me.txtIDProd,0)), 0)

Note use of <= and assumes txtCantidad will not have 0.

Upvotes: 1

Related Questions