frmsuppliersae.frm

Inventory control system

        'Save the details of the records
        For cStock = 1 To cIRowCountStock
            .Row = cStock
            If State = adStateAddMode Or State = adStatePopupMode Then
AddNewStock:
                rsVendorStock.AddNew

                rsVendorStock![VendorID] = PK
                rsVendorStock![StockID] = toNumber(.TextMatrix(cStock, 7))
                rsVendorStock![DiscPercent] = .TextMatrix(cStock, 2)
                rsVendorStock![ExtDiscPercent] = .TextMatrix(cStock, 3)
                rsVendorStock![ExtDiscAmount] = .TextMatrix(cStock, 4)
                rsVendorStock![Price] = .TextMatrix(cStock, 5)
                rsVendorStock![ExtPrice] = .TextMatrix(cStock, 6)

                rsVendorStock.Update
            ElseIf State = adStateEditMode Then
                rsVendorStock.Filter = "StockID = " & toNumber(.TextMatrix(cStock, 7))
            
                If rsVendorStock.RecordCount = 0 Then GoTo AddNewStock

                rsVendorStock![VendorID] = PK
                rsVendorStock![StockID] = toNumber(.TextMatrix(cStock, 7))
                rsVendorStock![DiscPercent] = .TextMatrix(cStock, 2)
                rsVendorStock![ExtDiscPercent] = .TextMatrix(cStock, 3)
                rsVendorStock![ExtDiscAmount] = .TextMatrix(cStock, 4)
                rsVendorStock![Price] = .TextMatrix(cStock, 5)
                rsVendorStock![ExtPrice] = .TextMatrix(cStock, 6)

                rsVendorStock.Update
            End If

        Next cStock
    End With
    
    'Clear variables
    cBank = 0
    cStock = 0
    
    HaveAction = True
    
    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
        Else
          Unload Me
        End If
    ElseIf State = adStatePopupMode Then
        MsgBox "New record has been successfully saved.", vbInformation
        Unload Me
    Else
        MsgBox "Changes in  record has been successfully saved.", vbInformation
        Unload Me
    End If
    
    CN.CommitTrans
    
    Exit Sub
err:
  
  MsgBox "Error: " & err.Description & vbCr _
  & "Form: frmSupplier" & vbCr _
  & "Sub: cmdSave_Click", vbExclamation, "Error"
  If err.Number = -2147217887 Then Resume Next
        
  CN.RollbackTrans
End Sub

Private Sub cmdUsrHistory_Click()
    On Error Resume Next
    Dim tDate1 As String
    Dim tDate2 As String
    Dim tUser1 As String
    Dim tUser2 As String
    
    tDate1 = Format$(rs.Fields("DateAdded"), "MMM-dd-yyyy HH:MM AMPM")
    tDate2 = Format$(rs.Fields("DateModified"), "MMM-dd-yyyy HH:MM AMPM")
    
    tUser1 = getValueAt("SELECT PK,CompleteName FROM tbl_SM_Users WHERE PK = " & rs.Fields("AddedByFK"), "CompleteName")
    tUser2 = getValueAt("SELECT PK,CompleteName FROM tbl_SM_Users WHERE PK = " & rs.Fields("LastUserFK"), "CompleteName")
    
    MsgBox "Date Added: " & tDate1 & vbCrLf & _
           "Added By: " & tUser1 & vbCrLf & _
           "" & vbCrLf & _
           "Last Modified: " & tDate2 & vbCrLf & _
           "Modified By: " & tUser2, vbInformation, "Modification History"
           
    tDate1 = vbNullString
    tDate2 = vbNullString
    tUser1 = vbNullString
    tUser2 = vbNullString
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
    If KeyAscii = 13 And blnRemarks = False Then
        SendKeys "{TAB}"
    End If
End Sub

Private Sub Form_Load()
    InitGrid

    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM Vendors WHERE VendorID = " & PK, CN, adOpenStatic, adLockOptimistic
    
    bind_dc "SELECT * FROM Vendors_Location", "Location", dcLocation, "LocationID", True
    
    'Check the form state
    InitNSD
    If State = adStateAddMode Or State = adStatePopupMode Then
        Caption = "Create New Entry"
        cmdUsrHistory.Enabled = False
      
        GeneratePK
    Else
      Caption = "Edit Entry"
      DisplayForEditing
    End If
End Sub

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

Private Sub Form_Unload(Cancel As Integer)
    If HaveAction = True Then
        If State = adStateAddMode Or State = adStateEditMode Then
            frmSuppliers.RefreshRecords
        ElseIf State = adStatePopupMode Then
            srcText.Text = txtEntry(0).Text
            srcText.Tag = PK
            On Error Resume Next
            srcTextAdd.Text = rs![DisplayAddr]
            srcTextCP.Text = txtEntry(6).Text
            'srcTextDisc.Text = toNumber(cmdDisc.Text)
        End If
    End If
    
    Set frmSuppliersAE = Nothing
End Sub

Private Sub GridBanks_Click()
    With GridBanks
        nsdBank.Text = .TextMatrix(.RowSel, 1)
        nsdBank.Tag = .TextMatrix(.RowSel, 5) 'Add tag coz boundtext is empty
        txtBranch.Text = .TextMatrix(.RowSel, 2)
        txtAcctNo.Text = .TextMatrix(.RowSel, 3)
        txtAcctName.Text = .TextMatrix(.RowSel, 4)
    
        If GridBanks.Rows = 2 And GridBanks.TextMatrix(1, 5) = "" Then '10 = StockID
            btnRemoveBank.Visible = False
        Else
            btnRemoveBank.Visible = True
            btnRemoveBank.Top = (GridBanks.CellTop + GridBanks.Top) - 20
            btnRemoveBank.Left = GridBanks.Left + 50
        End If
    End With
End Sub

Private Sub GridStocks_Click()
    With GridStocks
        nsdStock.Text = .TextMatrix(.RowSel, 1)
        nsdStock.Tag = .TextMatrix(.RowSel, 7) 'Add tag coz boundtext is empty
        txtDiscPercent.Text = .TextMatrix(.RowSel, 2)
        txtExtDiscPercent.Text = .TextMatrix(.RowSel, 3)
        txtExtDiscAmount.Text = .TextMatrix(.RowSel, 4)
        txtUnitPrice.Text = .TextMatrix(.RowSel, 5)
        txtExtPrice.Text = .TextMatrix(.RowSel, 6)
    
        If GridStocks.Rows = 2 And GridStocks.TextMatrix(1, 5) = "" Then '10 = StockID
            btnRemoveStock.Visible = False
        Else
            btnRemoveStock.Visible = True
            btnRemoveStock.Top = (GridStocks.CellTop + GridStocks.Top) - 20
            btnRemoveStock.Left = GridStocks.Left + 50
        End If
    End With
End Sub

Private Sub GridStocks_DblClick()
    With frmLandedCost
        .intProductID = GridStocks.TextMatrix(GridStocks.RowSel, 7) 'StockID
        .intSupplierID = PK
        
        .show 1
    End With
End Sub

Private Sub nsdBank_Change()
    txtBranch.Text = nsdBank.getSelValueAt(3)
End Sub

Private Sub nsdStock_Change()
  txtUnitPrice.Text = toMoney(nsdStock.getSelValueAt(3)) 'Selling Price
End Sub

Private Sub txtExtDiscAmount_GotFocus()
    HLText txtExtDiscAmount
End Sub

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

Private Sub txtextDiscPercent_GotFocus()
    HLText txtExtDiscPercent
End Sub

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

Private Sub txtEntry_GotFocus(Index As Integer)
    If Index = 14 Then
        blnRemarks = True
        Exit Sub
    Else
        blnRemarks = False
    End If
    
    HLText txtEntry(Index)
End Sub

Private Sub txtEntry_KeyPress(Index As Integer, KeyAscii As Integer)
    If Index = 12 Or Index = 13 Or Index = 15 Then KeyAscii = isNumber(KeyAscii)
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCountBank = 0
    With GridBanks
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 6
        .ColSel = 5
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 1400
        .ColWidth(2) = 1500
        .ColWidth(3) = 1400
        .ColWidth(4) = 1500
        .ColWidth(5) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Bank"
        .TextMatrix(0, 2) = "Branch"
        .TextMatrix(0, 3) = "Acct. No."
        .TextMatrix(0, 4) = "Acct. Name"
        .TextMatrix(0, 5) = "Bank ID"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
        .ColAlignment(3) = vbLeftJustify
        .ColAlignment(4) = vbRightJustify
    End With
    
    cIRowCountStock = 0
    With GridStocks
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 8
        .ColSel = 7
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 3400
        .ColWidth(2) = 1000
        .ColWidth(3) = 1000
        .ColWidth(4) = 1400
        .ColWidth(5) = 1400
        .ColWidth(6) = 1000
        .ColWidth(7) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Product"
        .TextMatrix(0, 2) = "Disc (%)"
        .TextMatrix(0, 3) = "Ext Disc (%)"
        .TextMatrix(0, 4) = "Ext Disc (Amt)"
        .TextMatrix(0, 5) = "Price"
        .TextMatrix(0, 6) = "Ext Price"
        .TextMatrix(0, 7) = "StockID"
        'Set the column alignment
'        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbRightJustify
'        .ColAlignment(2) = vbLeftJustify
'        .ColAlignment(3) = vbRightJustify
'        .ColAlignment(4) = vbRightJustify
'        .ColAlignment(5) = vbRightJustify
'        .ColAlignment(6) = vbRightJustify
    End With
End Sub

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

    'For Bank
    With nsdBank
        .ClearColumn
        .AddColumn "Bank ID", 1794.89
        .AddColumn "Bank", 2264.88
        .AddColumn "Branch", 2670.23
        .Connection = CN.ConnectionString
        
        .sqlFields = "BankID, Bank, Branch"
        .sqlTables = "Banks"
        .sqlSortOrder = "Bank ASC"
        
        .BoundField = "BankID"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 7000, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Banks Record"
    End With
End Sub

Private Sub DeleteItemsBank()
    Dim CurrRow As Integer
    Dim RSBank As New Recordset
    
    If State = adStateAddMode Then Exit Sub
    
    RSBank.CursorLocation = adUseClient
    RSBank.Open "SELECT * FROM Vendors_Bank WHERE VendorID=" & PK, CN, adOpenStatic, adLockOptimistic
    If RSBank.RecordCount > 0 Then
        RSBank.MoveFirst
        While Not RSBank.EOF
            CurrRow = getFlexPos(GridBanks, 5, RSBank!BankID)
        
            'Add to GridBanks
            With GridBanks
                If CurrRow < 0 Then
                    'Delete record if doesnt exist in flexgrid
                    DelRecwSQL "Vendors_Bank", "PK", "", True, RSBank!PK
                End If
            End With
            RSBank.MoveNext
        Wend
    End If
End Sub

Private Sub DeleteItemsStock()
    Dim CurrRow As Integer
    Dim RSStock As New Recordset
    
    If State = adStateAddMode Then Exit Sub
    
    RSStock.CursorLocation = adUseClient
    RSStock.Open "SELECT * FROM Vendors_Stocks WHERE VendorID=" & PK, CN, adOpenStatic, adLockOptimistic
    If RSStock.RecordCount > 0 Then
        RSStock.MoveFirst
        While Not RSStock.EOF
            CurrRow = getFlexPos(GridStocks, 7, RSStock!StockID)
        
            'Add to GridBanks
            With GridStocks
                If CurrRow < 0 Then
                    'Delete record if doesnt exist in flexgrid
                    DelRecwSQL "Vendors_Stocks", "VendorStockID", "", True, RSStock!VendorStockID
                End If
            End With
            RSStock.MoveNext
        Wend
    End If
End Sub