frmcustomersitem.frm

Inventory control system

    RSDetails.Open "SELECT ReceiptDetailID, Qty FROM Receipts_Detail WHERE ReceiptID=" & lvList.SelectedItem.Tag & " AND StockID =" & intStockID & " ORDER BY ReceiptDetailID ASC", CN, adOpenStatic, adLockOptimistic
    
    RSDetails![Qty] = toNumber(txtQty.Text)
    
    RSDetails.Update
    
    'Add to grid
    With Grid
        .Row = CurrRow
        
        'Restore back the invoice amount and discount
        cIGross = cIGross - toNumber(Grid.TextMatrix(.RowSel, 6))
        txtGross(2).Text = Format$(cIGross, "#,##0.00")
        cIAmount = cIAmount - toNumber(Grid.TextMatrix(.RowSel, 8))
        txtNet.Text = Format$(cIAmount, "#,##0.00")
        cDAmount = cDAmount - toNumber(toNumber(txtDisc.Text) / 100) * (toNumber(toNumber(Grid.TextMatrix(.RowSel, 3)) * toNumber(txtPrice.Text)))
        txtDesc.Text = Format$(cDAmount, "#,##0.00")
        
        .TextMatrix(CurrRow, 1) = nsdStock.getSelValueAt(1)
        .TextMatrix(CurrRow, 2) = nsdStock.Text
        .TextMatrix(CurrRow, 3) = toNumber(txtQty.Text) 'txtQty.Text
        .TextMatrix(CurrRow, 4) = dcUnit.Text
        .TextMatrix(CurrRow, 5) = toMoney(txtPrice.Text)
        .TextMatrix(CurrRow, 6) = toMoney(txtExtPrice.Text)
        .TextMatrix(CurrRow, 7) = toMoney(txtGross(1).Text)
        .TextMatrix(CurrRow, 8) = toNumber(txtDisc.Text)
        .TextMatrix(CurrRow, 9) = toMoney(toNumber(txtNetAmount.Text))
        .TextMatrix(CurrRow, 10) = intStockID

        'Add the amount to current load amount
        cIGross = cIGross + toNumber(txtGross(1).Text)
        txtGross(2).Text = Format$(cIGross, "#,##0.00")
        cIAmount = cIAmount + toNumber(txtNetAmount.Text)
        cDAmount = cDAmount + toNumber(toNumber(txtDisc.Text) / 100) * (toNumber(toNumber(txtQty.Text) * toNumber(txtPrice.Text)))
        txtDesc.Text = Format$(cDAmount, "#,##0.00")
        txtNet.Text = Format$(cIAmount, "#,##0.00")
        txtTaxBase.Text = toMoney(txtNet.Text / 1.12)
        txtVat.Text = toMoney(txtNet.Text - txtTaxBase.Text)
        'Highlight the current row's column
        .ColSel = 10
        'Display a remove button
        Grid_Click
        'Reset the entry fields
        ResetEntry
    End With
        
'    CN.CommitTrans
    Exit Sub
err:
    CN.RollbackTrans
    prompt_err err, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

Private Sub cmdCancel_Click()
    blnCancel = True
    
    Unload Me
End Sub

Private Sub cmdSave_Click()
On Error GoTo err

    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM Receipts_Detail WHERE ReceiptID=" & lvList.SelectedItem.Tag & " ORDER BY ReceiptDetailID ASC, CN, adOpenStatic, adLockOptimistic"
    
    Dim c As Integer
    
    With Grid
        'Save the details of the records
        For c = 1 To cIRowCount
            .Row = c

            RSDetails.Filter = "StockID = " & toNumber(.TextMatrix(c, 10))
        
            If RSDetails.RecordCount = 0 Then MsgBox "Error! Current record has been deleted.", vbExclamation: Exit Sub
            
            RSDetails![Qty] = toNumber(.TextMatrix(c, 3))
            
            RSDetails.Update
        Next c
    End With
    
    If cIRowCount > 0 Then _
        ChangeValue CN, "Receipts", "Deducted", "True", False, "ReceiptID=" & lvList.SelectedItem.Tag & ""
        
    Set RSDetails = Nothing
    
    blnCancel = False
    Unload Me
    
    Exit Sub

err:
    MsgBox err.Description, vbInformation
    blnCancel = False
End Sub

Private Sub cmdOK_Click()
    blnCancel = False
    Unload Me
End Sub

Private Sub Form_Load()
    If rs.State = adStateOpen Then rs.Close
    
    InitGrid
    
    rs.Open "SELECT Company,TotalQty,Gross,Discount,TaxBase,Vat,NetAmount,ReceiptID FROM qry_Receipts_Qty WHERE StockID = " & StockID & " ORDER BY ReceiptID ASC", CN, adOpenStatic, adLockOptimistic
    FillListView lvList, rs, 2, 0, False, True, "ReceiptID"
End Sub

Private Sub Grid_Click()
    With Grid
        On Error Resume Next
        bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & .TextMatrix(.RowSel, 10), "Unit", dcUnit, "UnitID", True
        On Error GoTo 0
        
        nsdStock.Text = .TextMatrix(.RowSel, 2)
        nsdStock.Tag = .TextMatrix(.RowSel, 10) 'Add tag coz boundtext is empty
        intQtyOld = .TextMatrix(.RowSel, 3)
        txtQty.Text = .TextMatrix(.RowSel, 3)
        dcUnit.Text = .TextMatrix(.RowSel, 4)
        txtPrice.Text = toMoney(.TextMatrix(.RowSel, 5))
        txtExtPrice.Text = toMoney(.TextMatrix(.RowSel, 6))
        txtGross(1).Text = toMoney(.TextMatrix(.RowSel, 7))
        txtDisc.Text = toMoney(.TextMatrix(.RowSel, 8))
        txtNetAmount.Text = toMoney(.TextMatrix(.RowSel, 9))
    End With
End Sub

Private Sub lvList_Click()
    rs.MoveFirst
    rs.Find "ReceiptID = " & lvList.SelectedItem.Tag
    
    DisplayForEditing
End Sub

'Used to display record
Private Sub DisplayForEditing()
    On Error GoTo err
       
    txtGross(2).Text = toMoney(toNumber(rs![Gross]))
    txtDesc.Text = toMoney(toNumber(rs![Discount]))
    txtTaxBase.Text = toMoney(rs![TaxBase])
    txtVat.Text = toMoney(rs![Vat])
    txtNet.Text = toMoney(rs![NetAmount])

    cIGross = toNumber(txtGross(2).Text)
    cDAmount = toNumber(txtDesc.Text)
    cIAmount = toNumber(txtNet.Text)
'    cIRowCount = 1
    
    Dim I As Integer
    
    For I = 1 To cIRowCount
        If Grid.Rows = 2 Then Grid.Rows = Grid.Rows + 1
        Grid.RemoveItem (Grid.RowSel)
    Next
    
    cIRowCount = 0

    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Receipts_Detail WHERE ReceiptID=" & lvList.SelectedItem.Tag & " ORDER BY ReceiptDetailID ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            cIRowCount = cIRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 10) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Barcode]
                    .TextMatrix(1, 2) = RSDetails![Stock]
                    .TextMatrix(1, 3) = RSDetails![Qty]
                    .TextMatrix(1, 4) = RSDetails![Unit]
                    .TextMatrix(1, 5) = toMoney(RSDetails![Price])
                    .TextMatrix(1, 6) = toMoney(RSDetails![ExtPrice])
                    .TextMatrix(1, 7) = toMoney(RSDetails![Gross])
                    .TextMatrix(1, 8) = RSDetails![Discount] * 100
                    .TextMatrix(1, 9) = toMoney(RSDetails![NetAmount])
                    .TextMatrix(1, 10) = RSDetails![StockID]
                    .TextMatrix(1, 11) = RSDetails![Suggested]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Barcode]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![Qty]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![Price]
                    .TextMatrix(.Rows - 1, 6) = RSDetails![ExtPrice]
                    .TextMatrix(.Rows - 1, 7) = RSDetails![Gross]
                    .TextMatrix(.Rows - 1, 8) = RSDetails![Discount] * 100
                    .TextMatrix(.Rows - 1, 9) = toMoney(RSDetails![NetAmount])
                    .TextMatrix(.Rows - 1, 10) = RSDetails![StockID]
                    .TextMatrix(.Rows - 1, 11) = RSDetails![Suggested]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 10
        'Set fixed cols
'        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionFree
            Grid.FixedCols = 2
'        End If
    End If

    RSDetails.Close
    'Clear variables
    Set RSDetails = Nothing
    
    Exit Sub
err:
    'Error if encounter a null value
    If err.Number = 94 Then
        Resume Next
    Else
        MsgBox err.Description
    End If
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 12
        .ColSel = 11
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 2025
        .ColWidth(2) = 2505
        .ColWidth(3) = 1545
        .ColWidth(4) = 900
        .ColWidth(5) = 900
        .ColWidth(6) = 900
        .ColWidth(7) = 900
        .ColWidth(8) = 900
        .ColWidth(9) = 900
        .ColWidth(10) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Barcode"
        .TextMatrix(0, 2) = "Description"
        .TextMatrix(0, 3) = "Qty"
        .TextMatrix(0, 4) = "Unit"
        .TextMatrix(0, 5) = "Sales Price"
        .TextMatrix(0, 6) = "Ext Price"
        .TextMatrix(0, 7) = "Gross"
        .TextMatrix(0, 8) = "Discount(%)"
        .TextMatrix(0, 9) = "Net Amount"
        .TextMatrix(0, 10) = "Stock ID"
        .TextMatrix(0, 11) = "Suggested"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
        .ColAlignment(3) = vbLeftJustify
        .ColAlignment(4) = vbRightJustify
        .ColAlignment(5) = vbLeftJustify
        .ColAlignment(6) = vbRightJustify
        .ColAlignment(7) = vbRightJustify
        .ColAlignment(8) = vbRightJustify
        .ColAlignment(9) = vbRightJustify
    End With
End Sub

Private Sub txtQty_Change()
    If toNumber(txtQty.Text) < 1 Then
        btnUpdate.Enabled = False
        Exit Sub
    Else
        btnUpdate.Enabled = True
    End If
       
    txtGross(1).Text = toMoney((toNumber(txtQty.Text) * toNumber(txtPrice.Text)))
    txtNetAmount.Text = toMoney((toNumber(txtQty.Text) * toNumber(txtPrice.Text)) - ((toNumber(txtDisc.Text) / 100) * toNumber(toNumber(txtQty.Text) * toNumber(txtPrice.Text))))
End Sub

Private Sub txtQty_GotFocus()
    HLText txtQty
End Sub

Private Sub txtQty_Validate(Cancel As Boolean)
    txtQty.Text = toNumber(txtQty.Text)
End Sub

Private Sub ResetEntry()
    nsdStock.ResetValue
    txtPrice.Tag = 0
    txtPrice.Text = "0.00"
    txtQty.Text = 0
End Sub