frmstockreceiveae.frm

Inventory control system

     
End Sub

'Procedure used to generate PK
Private Sub GeneratePK()
    PK = getIndex("purchase_order")
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 13
        .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) = 1545
        .ColWidth(10) = 750
        .ColWidth(11) = 500
        .ColWidth(12) = 500
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Barcode"
        .TextMatrix(0, 2) = "Description"
        .TextMatrix(0, 3) = "ICode"
        .TextMatrix(0, 4) = "Unit Qty"
        .TextMatrix(0, 5) = "Unit"
        .TextMatrix(0, 6) = "Sales Price"
        .TextMatrix(0, 7) = "Gross"
        .TextMatrix(0, 8) = "Discount(%)"
        .TextMatrix(0, 9) = "Net Amount"
        .TextMatrix(0, 10) = "Net Price"
        .TextMatrix(0, 11) = "Stock ID"
        .TextMatrix(0, 12) = "Disc"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
        .ColAlignment(3) = vbLeftJustify
        .ColAlignment(4) = vbLeftJustify
        .ColAlignment(5) = vbLeftJustify
        .ColAlignment(6) = vbLeftJustify
        .ColAlignment(7) = vbLeftJustify
        .ColAlignment(8) = vbLeftJustify
        .ColAlignment(9) = vbLeftJustify
        .ColAlignment(10) = vbLeftJustify
        .ColAlignment(11) = vbLeftJustify
        .ColAlignment(12) = vbLeftJustify
    End With
End Sub

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

Private Sub Form_Unload(Cancel As Integer)
    If HaveAction = True Then
        frmPurchaseOrder.RefreshRecords
    End If
    
    Set frmPurchaseOrderAE = Nothing
End Sub

Private Sub Grid_Click()
    If State = adStateEditMode Then Exit Sub
    If Grid.Rows = 2 And Grid.TextMatrix(1, 11) = "" Then
        btnRemove.Visible = False
    Else
        btnRemove.Visible = True
        btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
        btnRemove.Left = Grid.Left + 50
    End If
End Sub

Private Sub Grid_Scroll()
    btnRemove.Visible = False
End Sub

Private Sub Grid_SelChange()
    Grid_Click
End Sub


Private Sub nsdStock_Change()
    txtQty.Text = "0"
    
    txtSP.Tag = nsdStock.getSelValueAt(3) 'Unit Cost
    txtSP.Text = nsdStock.getSelValueAt(4) 'Selling Price
End Sub

Private Sub nsdVendor_Change()
    If nsdVendor.DisableDropdown = False Then
        txtAddress.Text = nsdVendor.getSelValueAt(3)
    End If
End Sub

Private Sub txtDate_GotFocus()
    HLText txtDate
End Sub

Private Sub txtDesc_GotFocus()
    HLText txtDesc
End Sub

Private Sub txtEntry_Change(Index As Integer)
    If Index > 1 And Index < 5 Then
        txtQty.Text = (toNumber(txtEntry(2).Text) * PCase) + (toNumber(txtEntry(3).Text) * PBox) + toNumber(txtEntry(4).Text)
    End If
End Sub

Private Sub txtEntry_GotFocus(Index As Integer)
    HLText txtEntry(Index)
    If Index = 8 Then
        cmdSave.Default = False
    End If
End Sub

Private Sub txtEntry_KeyPress(Index As Integer, KeyAscii As Integer)
    If Index > 1 And Index < 8 Then
        KeyAscii = isNumber(KeyAscii)
    End If
End Sub

Private Sub txtEntry_LostFocus(Index As Integer)
    If Index = 8 Then
        cmdSave.Default = True
    End If
End Sub

Private Sub txtEntry_Validate(Index As Integer, Cancel As Boolean)
    If Index > 1 And Index < 8 Then
        txtEntry(Index).Text = toNumber(txtEntry(Index).Text)
    End If
End Sub

Private Sub txtSP_Change()
    txtQty_Change
End Sub

Private Sub txtSP_Validate(Cancel As Boolean)
    txtSP.Text = toMoney(toNumber(txtSP.Text))
End Sub

Private Sub txtQty_Change()
    If toNumber(txtQty.Text) < 1 Then
        btnAdd.Enabled = False
    Else
        btnAdd.Enabled = True
    End If
    
    txtGross(1).Text = toMoney((toNumber(txtQty.Text) * toNumber(txtSP.Text)))
    txtNetAmount.Text = toMoney((toNumber(txtQty.Text) * toNumber(txtSP.Text)) - ((toNumber(cbDisc.Text) / 100) * toNumber(toNumber(txtQty.Text) * toNumber(txtSP.Text))))
    If toNumber(txtQty.Text) < 1 Then txtNetPrice.Text = 0: Exit Sub
    txtNetPrice.Text = toMoney(toNumber(txtSP.Text)) - ((toNumber(txtSP.Text) * (toNumber(cbDisc.Text) / 100)))
End Sub

Private Sub txtQty_GotFocus()
    HLText txtQty
End Sub

Private Sub txtSP_KeyPress(KeyAscii As Integer)
    KeyAscii = isNumber(KeyAscii)
End Sub

'Procedure used to reset fields
Private Sub ResetFields()
    InitGrid
    ResetEntry
    
    txtEntry(0).Text = ""
    dtpDate.Value = Date
    txtpurchase_from.Text = ""
        
    txtEntry(8).Text = ""
    
    txtGross(2).Text = "0.00"
    txtDesc.Text = "0.00"
    txtNet.Text = "0.00"

    cIAmount = 0
    cDAmount = 0

    txtEntry(0).SetFocus
End Sub

'Used to display record
Private Sub DisplayForViewing()
    On Error GoTo err
    nsdVendor.DisableDropdown = True
    nsdVendor.TextReadOnly = True
    nsdVendor.Text = rs!company
    txtPONo.Text = rs!po_no
    txtAddress.Text = rs!address
    txtDate.Text = rs![Date]
    txtSalesman.Text = rs![salesman]
    txtShipping_Instructions.Text = rs![shipping_instructions]
    txtAdditional_Instructions.Text = rs![additional_instructions]
    txtDeclared_as.Text = rs![declared_as]
    txtDeclared_Value.Text = rs![declared_value]
    txtGross(2).Text = toMoney(toNumber(rs![Gross]))
    txtDesc.Text = toMoney(toNumber(rs![Discount]))
    txtNet.Text = toMoney(rs![amount_net])
    txtEntry(8).Text = rs![Remarks]
    
    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_purchase_orderDetails WHERE purchase_order_id=" & PK & " ORDER BY stock ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            With Grid
                If .Rows = 2 And .TextMatrix(1, 11) = "" Then
                    .TextMatrix(1, 1) = RSDetails![barcode]
                    .TextMatrix(1, 2) = RSDetails![stock]
                    .TextMatrix(1, 3) = RSDetails![icode]
                    .TextMatrix(1, 4) = RSDetails![Qty]
                    .TextMatrix(1, 5) = RSDetails![Unit]
                    .TextMatrix(1, 6) = RSDetails![Price]
                    .TextMatrix(1, 7) = RSDetails![amount_gross]
                    .TextMatrix(1, 8) = RSDetails![discount_percent]
                    .TextMatrix(1, 9) = toMoney(RSDetails![amount_net])
                    .TextMatrix(1, 10) = RSDetails![net_price]
                    .TextMatrix(1, 11) = RSDetails![stock_id]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![barcode]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![stock]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![icode]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![Qty]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 6) = RSDetails![Price]
                    .TextMatrix(.Rows - 1, 7) = RSDetails![amount_gross]
                    .TextMatrix(.Rows - 1, 8) = RSDetails![discount_percent]
                    .TextMatrix(.Rows - 1, 9) = toMoney(RSDetails![amount_net])
                    .TextMatrix(.Rows - 1, 10) = RSDetails![net_price]
                    .TextMatrix(.Rows - 1, 11) = RSDetails![stock_id]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 11
        '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

    'Disable commands
    LockInput Me, True

    dtpDate.Visible = False
    txtDate.Visible = True
    picPurchase.Visible = False
    cmdSave.Visible = False
    btnAdd.Visible = False
    'txtLess.Locked = True

    'Resize and reposition the controls
    Shape3.Top = 2100 '2850
    Label11.Top = 2100 '2850
    Line1(1).Visible = False
    Line2(1).Visible = False
    Grid.Top = 2400 '3150
    Grid.Height = 3180 '2800

    Exit Sub
err:
    'Error if encounter a null value
    If err.Number = 94 Then
        Resume Next
    Else
        MsgBox err.Description
    End If
End Sub

Private Sub InitNSD()
    'For Vendor
    With nsdVendor
        .ClearColumn
        .AddColumn "Vendor ID", 1794.89
        .AddColumn "Company", 2264.88
        .AddColumn "Address", 2670.23
        .Connection = CN.ConnectionString
        
        .sqlFields = "vendor_id, company, address"
        .sqlTables = "vendors"
        .sqlSortOrder = "company ASC"
        
        .BoundField = "vendor_id"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 7000, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Vendors Record"
        
    End With

    'For Stock
    With nsdStock
        .ClearColumn
        .AddColumn "Barcode", 2064.882
        .AddColumn "Stock", 4085.26
        .AddColumn "Cost", 1500
        .AddColumn "Sales Price", 1500
        .AddColumn "ICode", 1500
        
        .Connection = CN.ConnectionString
        
        .sqlFields = "barcode,stock,cost,sales_price,icode,stock_id"
        .sqlTables = "stocks"
        .sqlSortOrder = "stock ASC"
        .BoundField = "stock_id"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 6800, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Stocks"
        
    End With

End Sub

Private Sub txtSP_GotFocus()
    HLText txtSP
End Sub

Private Sub InitInvoice()
    
End Sub