frmqtyadjustmentae.frm

Inventory control system

        Screen.MousePointer = vbDefault
    End If
    
    'Initialize Graphics
    With MAIN
        'cmdGenerate.Picture = .i16x16.ListImages(14).Picture
        'cmdNew.Picture = .i16x16.ListImages(10).Picture
        'cmdReset.Picture = .i16x16.ListImages(15).Picture
    End With
End Sub

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

Private Sub ResetEntry()
    nsdStock.ResetValue
    txtNewQty.Text = 0
End Sub

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

Private Sub Grid_Click()
    With Grid
        If State = adStateViewMode Then Exit Sub

        nsdStock.Text = .TextMatrix(.RowSel, 1)
        nsdStock.Tag = .TextMatrix(.RowSel, 5) 'Add tag coz boundtext is empty
        intQtyOld = IIf(.TextMatrix(.RowSel, 2) = "", 0, .TextMatrix(.RowSel, 2))
        txtNewQty.Text = .TextMatrix(.RowSel, 2)
        
        On Error Resume Next
        bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & .TextMatrix(.RowSel, 5), "Unit", dcUnit, "UnitID", True
        On Error GoTo 0
        
        dcUnit.Text = .TextMatrix(.RowSel, 4)
        'disable unit to prevent user from changing it. changing of unit will result to imbalance of inventory
        If State = adStateEditMode Then dcUnit.Enabled = False
    
        If Grid.Rows = 2 And Grid.TextMatrix(1, 5) = "" Then '5 = StockID
            btnRemove.Visible = False
        Else
            btnRemove.Visible = True
            btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
            btnRemove.Left = Grid.Left + 50
        End If
    End With
End Sub

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

Private Sub Grid_SelChange()
    Grid_Click
End Sub


Private Sub nsdStock_Change()
    On Error Resume Next
    
    nsdStock.Tag = nsdStock.BoundText
    
    dcUnit.Text = ""
    bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & nsdStock.BoundText, "Unit", dcUnit, "UnitID", True
    
    Call GetQty
End Sub

Private Sub GetQty()
    Dim RSStockUnit As New Recordset
    
    RSStockUnit.CursorLocation = adUseClient
    RSStockUnit.Open "SELECT * FROM qry_Stock_Unit WHERE StockID =" & nsdStock.BoundText & " AND UnitID = " & dcUnit.BoundText & " ORDER BY Stock_Unit.Order ASC", CN, adOpenStatic, adLockOptimistic
    
    'Retrieve qty from Stock Unit's table
    txtOldQty.Text = RSStockUnit!Onhand
    txtNewQty.Text = RSStockUnit!Onhand
End Sub

Private Sub txtNewQty_Validate(Cancel As Boolean)
    txtNewQty.Text = toNumber(txtNewQty.Text)
End Sub

Private Sub txtNewQty_Change()
    If toNumber(txtNewQty.Text) < 1 Then
        btnAdd.Enabled = False
        Exit Sub
    Else
        btnAdd.Enabled = True
    End If
End Sub

Private Sub txtNewQty_GotFocus()
    HLText txtNewQty
End Sub

'Procedure used to reset fields
Private Sub ResetFields()
    InitGrid
    ResetEntry
    
'    nsdClient.Text = ""
'    txtSONo.Text = ""
'    txtAddress.Text = ""
'    txtDate.Text = ""
'    txtSalesman.Text = ""
'    txtOrderedBy.Text = ""
'    txtDispatchedBy.Text = ""
'    txtDeliveryInstructions.Text = ""
'
'    txtGross(2).Text = "0.00"
'    txtDesc.Text = "0.00"
'    txtTaxBase.Text = "0.00"
'    txtVat.Text = "0.00"
'    txtNet.Text = "0.00"
'
'    cIAmount = 0
'    cDAmount = 0
'
'    nsdClient.SetFocus
End Sub

'Used to display record
Private Sub DisplayForEditing()
    On Error GoTo erR
    
    cboReason.Text = rs![Reason]
    dtpDate.Value = rs![Date]
    cboStatus.Text = rs!Status_Alias
    txtNotes.Text = rs![Notes]
    
    cIRowCount = 0

    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Qty_Adjustment_Detail WHERE QtyAdjustmentID=" & PK & " ORDER BY QtyAdjDetailID 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, 5) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Stock]
                    .TextMatrix(1, 2) = RSDetails![NewQty]
                    .TextMatrix(1, 3) = RSDetails![OldQty]
                    .TextMatrix(1, 4) = RSDetails![Unit]
                    .TextMatrix(1, 5) = RSDetails![StockID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![NewQty]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![OldQty]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![StockID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 4
        'Set fixed cols
        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionByRow
            Grid.FixedCols = 1
        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

'Used to display record
Private Sub DisplayForViewing()
    On Error GoTo erR
    
    cboReason.Text = rs![Reason]
    dtpDate.Value = rs![Date]
    cboStatus.Text = rs!Status_Alias
    txtNotes.Text = rs![Notes]

    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Qty_Adjustment_Detail WHERE QtyAdjustmentID=" & PK & " ORDER BY QtyAdjDetailID ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            With Grid
                If .Rows = 2 And .TextMatrix(1, 5) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Stock]
                    .TextMatrix(1, 2) = RSDetails![NewQty]
                    .TextMatrix(1, 3) = RSDetails![OldQty]
                    .TextMatrix(1, 4) = RSDetails![Unit]
                    .TextMatrix(1, 5) = RSDetails![StockID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![NewQty]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![OldQty]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![StockID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 4
        'Set fixed cols
        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionByRow
            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

    'Resize and reposition the controls
   
    Shape3.Top = 1300
    Label11.Top = 1300
    Line1(1).Visible = False
    Line2(1).Visible = False
    Grid.Top = 1600
    Grid.Height = 3280

    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 = 6
        .ColSel = 5
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 3525
        .ColWidth(2) = 1545
        .ColWidth(3) = 1545
        .ColWidth(4) = 1545
        .ColWidth(5) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Description"
        .TextMatrix(0, 2) = "New Qty"
        .TextMatrix(0, 3) = "Old Qty"
        .TextMatrix(0, 4) = "Unit"
        .TextMatrix(0, 5) = "Stock ID"
        'Set the column alignment
'        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
'        .ColAlignment(2) = vbLeftJustify
'        .ColAlignment(3) = vbLeftJustify
'        .ColAlignment(4) = vbLeftJustify
    End With
End Sub

Private Sub InitNSD()
    'For Stock
    With nsdStock
        .ClearColumn
        .AddColumn "Barcode", 2064.882
        .AddColumn "Stock", 4085.26
        
        .Connection = CN.ConnectionString
        
        .sqlFields = "Barcode,Stock,StockID"
        .sqlTables = "Stocks"
        .sqlSortOrder = "Stock ASC"
        .BoundField = "StockID"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 6800, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Products"
    End With
End Sub

Private Sub DeleteItems()
    Dim CurrRow As Integer
    Dim RSQtyAdj As New Recordset

    If State = adStateAddMode Then Exit Sub

    RSQtyAdj.CursorLocation = adUseClient
    RSQtyAdj.Open "SELECT * FROM Qty_Adjustment_Detail WHERE QtyAdjustmentID=" & PK, CN, adOpenStatic, adLockOptimistic
    If RSQtyAdj.RecordCount > 0 Then
        RSQtyAdj.MoveFirst
        While Not RSQtyAdj.EOF
            CurrRow = getFlexPos(Grid, 5, RSQtyAdj!StockID)

            'Add to grid
            With Grid
                If CurrRow < 0 Then
                    'Delete record if doesnt exist in flexgrid
                    DelRecwSQL "Qty_Adjustment_Detail", "QtyAdjDetailID", "", True, RSQtyAdj!QtyAdjDetailID
                End If
            End With
            RSQtyAdj.MoveNext
        Wend
    End If
End Sub