frmassortedproductae.frm

Inventory control system

        Next c
    End With
    
    'Clear variables
    c = 0
    Set RSDetails = Nothing

    CN.CommitTrans
    
    blnSave = True
    
    HaveAction = True
    Screen.MousePointer = vbDefault

    If State = adStateAddMode Then
        MsgBox "New record has been successfully saved.", vbInformation
        
        If MsgBox("Do you want to add another new record?", vbQuestion + vbYesNo) = vbYes Then
            ResetFields
            GeneratePK
        Else
            Unload Me
        End If
    Else
        MsgBox "Changes in record has been successfully saved.", vbInformation
        Unload Me
    End If

    Exit Sub
erR:
    blnSave = False
'    CN.RollbackTrans
'    CN.BeginTrans
    prompt_err erR, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

Private Sub Form_Activate()
    On Error Resume Next
    If CloseMe = True Then
        Unload Me
    Else
        nsdProduct.SetFocus
    End If
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 Then SendKeys ("{tab}")
End Sub

Private Sub Form_Load()
    Dim strRoute As String
    
    InitGrid
    
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        InitNSD
    
    'Set the recordset
    If rs.State = 1 Then rs.Close
        rs.Open "SELECT * FROM Assorted_Product WHERE AssortedProductID=" & PK, CN, adOpenStatic, adLockOptimistic
        dtpDate.Value = Date
        
        CN.BeginTrans

        GeneratePK
    Else
        Screen.MousePointer = vbHourglass
        'Set the recordset
        rs.Open "SELECT * FROM qry_Assorted_Product WHERE AssortedProductID=" & PK, CN, adOpenStatic, adLockOptimistic
        
        If State = adStateViewMode Then
            cmdCancel.Caption = "Close"
                   
            DisplayForViewing
        Else
            InitNSD
            
            CN.BeginTrans
            
            DisplayForEditing
        End If
    
        If ForCusAcc = True Then
            Me.Icon = frmSalesReceipts.Icon
        End If

        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("Assorted_Product")
End Sub

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

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

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

        nsdStock.Text = .TextMatrix(.RowSel, 1)
        nsdStock.Tag = .TextMatrix(.RowSel, 4) 'Add tag coz boundtext is empty
        intQtyOld = IIf(.TextMatrix(.RowSel, 2) = "", 0, .TextMatrix(.RowSel, 2))
        txtQty.Text = .TextMatrix(.RowSel, 2)
        
        On Error Resume Next
        bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & .TextMatrix(.RowSel, 4), "Unit", dcUnit, "UnitID", True
        On Error GoTo 0
        
        dcUnit.Text = .TextMatrix(.RowSel, 3)
        '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, 4) = "" Then '4 = 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
    txtQty.Text = "0"
        
    dcUnit.Text = ""
    bind_dc "SELECT * FROM qry_Unit WHERE StockID=" & nsdStock.BoundText, "Unit", dcUnit, "UnitID", True
    
'    txtPrice.Text = toMoney(nsdStock.getSelValueAt(3)) 'Selling Price
End Sub

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

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

Private Sub txtQty_GotFocus()
    HLText txtQty
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
    
    nsdProduct.Tag = rs!StockID
    nsdProduct.DisableDropdown = True
    nsdProduct.TextReadOnly = True
    nsdProduct.Text = rs![Stock]
    txtQtyParent.Text = rs![Qty]
    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_Assorted_Product_Detail WHERE AssortedProductID=" & PK & " ORDER BY AProdDetailID 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, 4) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Stock]
                    .TextMatrix(1, 2) = RSDetails![Qty]
                    .TextMatrix(1, 3) = RSDetails![Unit]
                    .TextMatrix(1, 4) = RSDetails![StockID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Qty]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![StockID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 3
        '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

    cmdSave.Caption = "Save"
    
    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
    
    nsdProduct.Tag = rs!StockID
    nsdProduct.DisableDropdown = True
    nsdProduct.TextReadOnly = True
    nsdProduct.Text = rs![Stock]
    txtQtyParent.Text = rs![Qty]
    txtDate.Text = 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_Assorted_Product_Detail WHERE AssortedProductID=" & PK & " ORDER BY AProdDetailID ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            With Grid
                If .Rows = 2 And .TextMatrix(1, 4) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Stock]
                    .TextMatrix(1, 2) = RSDetails![Qty]
                    .TextMatrix(1, 3) = RSDetails![Unit]
                    .TextMatrix(1, 4) = RSDetails![StockID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Qty]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 4) = 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

    '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 = 1850
    Label11.Top = 1850
    Line1(1).Visible = False
    Line2(1).Visible = False
    Grid.Top = 2200
    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 = 5
        .ColSel = 4
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 4000
        .ColWidth(2) = 1505
        .ColWidth(3) = 1545
        .ColWidth(4) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Description"
        .TextMatrix(0, 2) = "Qty"
        .TextMatrix(0, 3) = "Unit"
        .TextMatrix(0, 4) = "Stock ID"
        'Set the column alignment
'        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
'        .ColAlignment(2) = vbLeftJustify
'        .ColAlignment(3) = vbLeftJustify
    End With
End Sub

Private Sub InitNSD()
    'For Stock
    With nsdProduct
        .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
    
    '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 RSAssorted As New Recordset

    If State = adStateAddMode Then Exit Sub

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

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