Member Avatar for masterfact18 
Private Sub cmdDeduct_Click()
Dim sConn As String, xQuantity As Integer, xStockTotal As Integer, xPre As Integer
Set oConn = New ADODB.Connection
Set rsDeduct = New ADODB.Recordset
    
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\inventory.mdb;Persist Security Info=False"
    
oConn.Open sConn
    
If LenB(cmbItemName.Text) = 0 Then
    MsgBox "You have to select an item from the drop down list.", vbOKOnly + vbExclamation, "No Selection"
    
    cmbItemName.SetFocus
    Exit Sub
ElseIf LenB(txtQuantity.Text) = 0 Then
    MsgBox "You have not set a quantity amount to withdraw.", vbOKOnly + vbExclamation, "No Quantity"
    
    txtQuantity.SetFocus
    Exit Sub
        Else
    rsDeduct.Open "SELECT Description, [Current Stock] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
    xStockTotal = rsDeduct![Current Stock]
    
    xQuantity = xStockTotal - xQuantity
     
    If xQuantity < 0 Then
        If MsgBox("You do not have so much stock available. You only have " & rsDeduct![Current Stock] & " amount to withdraw.", vbYesNo + vbQuestion, "Not Enough STock") = vbYes Then
            rsDeduct![Current Stock] = "0"
            rsDeduct.Update
    rsPre.Open "SELECT Description, [Previous Stocks] FROM storage WHERE Description = '" & cmbItemName.Text & "'", oConn, adOpenKeyset, adLockOptimistic
    
    xQuantity = txtQuantity.Text
    xStockTotal = rsPre![Previous Stocks]
    
    xQuantity = xPre + xQuantity

        rsPre![Previous Stocks] = xQuantity
        rsPre.Update
            rsDeduct.Close
            oConn.Close
    
            Unload Me
            Form5.Show
                Else
            Unload Me
            Form5.Show
        End If
            Else
        rsDeduct![Current Stock] = xQuantity
        rsDeduct.Update

        
        rsPre.Close
        rsDeduct.Close
        oConn.Close
        Unload Me
        Form5.Adodc1.Refresh
        Form5.DataGrid1.Refresh
        Form5.Show
    End If
End If
End Sub

my logic here is if i deduct 2 to 10 (where 10 is the current stock) then 2 will be add to database fields(Previous Stocks/Last Week Stocks)

note for Mr. AndreRet : I rename my Database Field Last Week Stocks to Previous Stocks

Please help...

  • 2 Contributors
  • 1 Reply
  • 90 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by AndreRet
Member Avatar for AndreRet

@Master, sorry being weekend and all, I will not be spending a lot on this. Maybe tomorrow if I have a huge hangover.;)

Your logic is not as good. Zip and load your app again with the database, let me change some code. I will not do the entire project though. only up to with what we are busy with.:)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.