frmsuppliersae.frm

Inventory control system

Private Sub DisplayForEditing()
    On Error GoTo errHandler
    
    With rs
        txtEntry(0).Text = .Fields("company")
        dcLocation.BoundText = .Fields![LocationID]
        cboDelivery.Text = .Fields("Delivery")
        cboFreightAgreement.Text = .Fields("FreightAgreement")
        cboFreightPeriod.Text = .Fields("FreightPeriod")
        txtEntry(1).Text = .Fields("Telephone")
        txtEntry(2).Text = .Fields("Owner")
        txtEntry(3).Text = .Fields("ContactPerson")
        txtEntry(4).Text = .Fields("Address1")
        txtEntry(5).Text = .Fields("Address2")
        txtEntry(6).Text = .Fields("Cellphone")
        txtEntry(11).Text = .Fields("Fax")
        txtEntry(12).Text = .Fields("CreditTerm")
        cboCreditOption.Text = IIf(IsNull(.Fields("CreditOption")), "", .Fields("CreditOption"))
        txtEntry(13).Text = .Fields("CreditLimit")
        txtEntry(14).Text = .Fields("Remarks")
        txtEntry(15).Text = toNumber(.Fields("GenDiscPercent"))
    End With
    
    'Display the details of Bank
    Dim rsVendorBank As New Recordset

    cIRowCountBank = 0
    
    rsVendorBank.CursorLocation = adUseClient
    rsVendorBank.Open "SELECT * FROM qry_Vendors_Bank WHERE VendorID=" & PK, CN, adOpenStatic, adLockOptimistic
    
    If rsVendorBank.RecordCount > 0 Then
        rsVendorBank.MoveFirst
        While Not rsVendorBank.EOF
          cIRowCountBank = cIRowCountBank + 1     'increment
            With GridBanks
                If .Rows = 2 And .TextMatrix(1, 5) = "" Then
                    .TextMatrix(1, 1) = rsVendorBank![Bank]
                    .TextMatrix(1, 2) = rsVendorBank![Branch]
                    .TextMatrix(1, 3) = rsVendorBank![AccountNo]
                    .TextMatrix(1, 4) = rsVendorBank![AccountName]
                    .TextMatrix(1, 5) = rsVendorBank![BankID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = rsVendorBank![Bank]
                    .TextMatrix(.Rows - 1, 2) = rsVendorBank![Branch]
                    .TextMatrix(.Rows - 1, 3) = rsVendorBank![AccountNo]
                    .TextMatrix(.Rows - 1, 4) = rsVendorBank![AccountName]
                    .TextMatrix(.Rows - 1, 5) = rsVendorBank![BankID]
                End If
            End With
            rsVendorBank.MoveNext
        Wend
        GridBanks.Row = 1
        GridBanks.ColSel = 5
        'Set fixed cols
        If State = adStateEditMode Then
            GridBanks.FixedRows = GridBanks.Row: 'GridBanks.SelectionMode = flexSelectionFree
            GridBanks.FixedCols = 1
        End If
    End If

    'Display the details of Stock
    Dim rsVendorStock As New Recordset

    cIRowCountStock = 0
    
    rsVendorStock.CursorLocation = adUseClient
    rsVendorStock.Open "SELECT * FROM qry_Vendors_Stocks WHERE VendorID=" & PK, CN, adOpenStatic, adLockOptimistic
    
    If rsVendorStock.RecordCount > 0 Then
        rsVendorStock.MoveFirst
        While Not rsVendorStock.EOF
          cIRowCountStock = cIRowCountStock + 1     'increment
            With GridStocks
                If .Rows = 2 And .TextMatrix(1, 6) = "" Then
                    .TextMatrix(1, 1) = rsVendorStock!Stock
                    .TextMatrix(1, 2) = rsVendorStock!DiscPercent
                    .TextMatrix(1, 3) = rsVendorStock!ExtDiscPercent
                    .TextMatrix(1, 4) = rsVendorStock!ExtDiscAmount
                    .TextMatrix(1, 5) = rsVendorStock!SupplierPrice
                    .TextMatrix(1, 6) = rsVendorStock!ExtPrice
                    .TextMatrix(1, 7) = rsVendorStock!StockID
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = rsVendorStock!Stock
                    .TextMatrix(.Rows - 1, 2) = rsVendorStock!DiscPercent
                    .TextMatrix(.Rows - 1, 3) = rsVendorStock!ExtDiscPercent
                    .TextMatrix(.Rows - 1, 4) = rsVendorStock!ExtDiscAmount
                    .TextMatrix(.Rows - 1, 5) = rsVendorStock!SupplierPrice
                    .TextMatrix(.Rows - 1, 6) = rsVendorStock!ExtPrice
                    .TextMatrix(.Rows - 1, 7) = rsVendorStock!StockID
                End If
            End With
            rsVendorStock.MoveNext
        Wend
        GridStocks.Row = 1
        GridStocks.ColSel = 7
        'Set fixed cols
        If State = adStateEditMode Then
            GridStocks.FixedRows = GridStocks.Row: 'GridStocks.SelectionMode = flexSelectionFree
            GridStocks.FixedCols = 1
        End If
    End If
    
    rsVendorBank.Close
    rsVendorStock.Close
    'Clear variables
    Set rsVendorBank = Nothing
    Set rsVendorStock = Nothing
    
    Exit Sub
errHandler:
  If err.Number = 94 Then
    Resume Next
  Else
    MsgBox "Error: " & err.Description
  End If
End Sub

Private Sub btnAddBank_Click()
    If nsdBank.Text = "" Or txtAcctNo.Text = "" Or txtAcctName.Text = "" Then nsdBank.SetFocus: Exit Sub

    Dim CurrRow As Integer
    Dim intBankID As Integer
    
    If nsdBank.BoundText = "" Then
        CurrRow = getFlexPos(GridBanks, 5, nsdBank.Tag)
        intBankID = nsdBank.Tag
    Else
        CurrRow = getFlexPos(GridBanks, 5, nsdBank.BoundText)
        intBankID = nsdBank.BoundText
    End If

    'Add to GridBanks
    With GridBanks
        If CurrRow < 0 Then
            'Perform if the record is not exist
            If .Rows = 2 And .TextMatrix(1, 5) = "" Then
                .TextMatrix(1, 1) = nsdBank.Text
                .TextMatrix(1, 2) = txtBranch.Text
                .TextMatrix(1, 3) = txtAcctNo.Text
                .TextMatrix(1, 4) = txtAcctName.Text
                .TextMatrix(1, 5) = intBankID
            Else
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1, 1) = nsdBank.Text
                .TextMatrix(.Rows - 1, 2) = txtBranch.Text
                .TextMatrix(.Rows - 1, 3) = txtAcctNo.Text
                .TextMatrix(.Rows - 1, 4) = txtAcctName.Text
                .TextMatrix(.Rows - 1, 5) = intBankID

                .Row = .Rows - 1
            End If
            'Increase the record count
            cIRowCountBank = cIRowCountBank + 1
        Else
            If MsgBox("Item already added. Do you want to replace it?", vbQuestion + vbYesNo) = vbYes Then
                .Row = CurrRow
                
                .TextMatrix(CurrRow, 1) = nsdBank.Text
                .TextMatrix(CurrRow, 2) = txtBranch.Text
                .TextMatrix(CurrRow, 3) = txtAcctNo.Text
                .TextMatrix(CurrRow, 4) = txtAcctName.Text
                .TextMatrix(CurrRow, 5) = intBankID
            Else
                Exit Sub
            End If
        End If
        
        'Highlight the current row's column
        .ColSel = 5
        'Display a remove button
        GridBanks_Click
    End With
End Sub

Private Sub btnRemoveBank_Click()
    'Remove selected load product
    With GridBanks
        'Update the record count
        cIRowCountBank = cIRowCountBank - 1
        
        If .Rows = 2 Then .Rows = .Rows + 1
        .RemoveItem (.RowSel)
    End With

    btnRemoveBank.Visible = False
    GridBanks_Click
End Sub

Private Sub btnRemoveStock_Click()
    'Remove selected load product
    With GridStocks
        'Update the record count
        cIRowCountStock = cIRowCountStock - 1
        
        If .Rows = 2 Then .Rows = .Rows + 1
        .RemoveItem (.RowSel)
    End With

    btnRemoveStock.Visible = False
    GridStocks_Click
End Sub

Private Sub cmdAddStock_Click()
    If nsdStock.Text = "" Or txtExtDiscPercent.Text = "" Or txtExtDiscAmount.Text = "" Or txtUnitPrice.Text = "" Then nsdBank.SetFocus: Exit Sub

    Dim CurrRow As Integer
    Dim intStockID As Integer
    
    If nsdStock.BoundText = "" Then
        CurrRow = getFlexPos(GridStocks, 7, nsdStock.Tag)
        intStockID = nsdStock.Tag
    Else
        CurrRow = getFlexPos(GridStocks, 7, nsdStock.BoundText)
        intStockID = nsdStock.BoundText
    End If

    'Add to GridStocks
    With GridStocks
        If CurrRow < 0 Then
            'Perform if the record is not exist
            If .Rows = 2 And .TextMatrix(1, 7) = "" Then
                .TextMatrix(1, 1) = nsdStock.Text
                .TextMatrix(1, 2) = txtDiscPercent.Text
                .TextMatrix(1, 3) = txtExtDiscPercent.Text
                .TextMatrix(1, 4) = txtExtDiscAmount.Text
                .TextMatrix(1, 5) = txtUnitPrice.Text
                .TextMatrix(1, 6) = txtExtPrice.Text
                .TextMatrix(1, 7) = intStockID
            Else
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1, 1) = nsdStock.Text
                .TextMatrix(.Rows - 1, 2) = txtDiscPercent.Text
                .TextMatrix(.Rows - 1, 3) = txtExtDiscPercent.Text
                .TextMatrix(.Rows - 1, 4) = txtExtDiscAmount.Text
                .TextMatrix(.Rows - 1, 5) = txtUnitPrice.Text
                .TextMatrix(.Rows - 1, 6) = txtExtPrice.Text
                .TextMatrix(.Rows - 1, 7) = intStockID

                .Row = .Rows - 1
            End If
            'Increase the record count
            cIRowCountStock = cIRowCountStock + 1
        Else
            If MsgBox("Item already added. Do you want to replace it?", vbQuestion + vbYesNo) = vbYes Then
                .Row = CurrRow
                
                .TextMatrix(CurrRow, 1) = nsdStock.Text
                .TextMatrix(CurrRow, 2) = txtDiscPercent.Text
                .TextMatrix(CurrRow, 3) = txtExtDiscPercent.Text
                .TextMatrix(CurrRow, 4) = txtExtDiscAmount.Text
                .TextMatrix(CurrRow, 5) = txtUnitPrice.Text
                .TextMatrix(CurrRow, 6) = txtExtPrice.Text
                .TextMatrix(CurrRow, 7) = intStockID
            Else
                Exit Sub
            End If
        End If
        
        'Highlight the current row's column
        .ColSel = 5
        'Display a remove button
        GridStocks_Click
    End With
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub ResetFields()
    clearText Me
    
    txtEntry(0).SetFocus
End Sub

Private Sub cmdSave_Click()
On Error GoTo err

    If is_empty(txtEntry(0), True) = True Then
      MsgBox "The field 'Company' is required. Please check it!", vbExclamation
      Exit Sub
    End If
    
    If Trim(dcLocation.Text) = "" Then
      MsgBox "The field 'Category' is required. Please check it!", vbExclamation
      Exit Sub
    End If
    
    If Trim(cboDelivery) = "" Then
      MsgBox "The field 'Mode of Delivery' is required. Please check it!", vbExclamation
      Exit Sub
    End If
    
    If Trim(cboFreightAgreement) = "" Then
      MsgBox "The field 'Frieght Payment Agreement' is required.Please check it!", vbExclamation
      Exit Sub
    End If
    
    If Trim(cboFreightPeriod) = "" Then
      MsgBox "The field 'Frieght Payment Period' is required.Please check it!", vbExclamation
      Exit Sub
    End If
    
    If Trim(cboCreditOption) = "" Then
      MsgBox "The field 'Credit Option' is required. Please check it!", vbExclamation
      Exit Sub
    End If
    
    CN.BeginTrans
    If State = adStateAddMode Or State = adStatePopupMode Then
        rs.AddNew
        
        rs.Fields("VendorID") = PK
        rs.Fields("addedbyfk") = CurrUser.USER_PK
    Else
        rs.Fields("datemodified") = Now
        rs.Fields("lastuserfk") = CurrUser.USER_PK
    End If
    
    With rs
        .Fields("company") = txtEntry(0).Text
        .Fields("LocationID") = dcLocation.BoundText
        .Fields("delivery") = cboDelivery.Text
        .Fields("FreightAgreement") = cboFreightAgreement.Text
        .Fields("FreightPeriod") = cboFreightPeriod.Text
        .Fields("Telephone") = txtEntry(1).Text
        .Fields("Owner") = txtEntry(2).Text
        .Fields("ContactPerson") = txtEntry(3).Text
        .Fields("Address1") = txtEntry(4).Text
        .Fields("Address2") = txtEntry(5).Text
        .Fields("Cellphone") = txtEntry(6).Text
        .Fields("Fax") = txtEntry(11).Text
        .Fields("CreditTerm") = txtEntry(12).Text
        .Fields("CreditOption") = cboCreditOption.Text
        .Fields("CreditLimit") = toNumber(txtEntry(13).Text)
        .Fields("Remarks") = txtEntry(14).Text
        .Fields("GenDiscPercent") = toNumber(txtEntry(15).Text)
        .Update
    End With
    
    Dim rsVendorBank As New Recordset

    rsVendorBank.CursorLocation = adUseClient
    rsVendorBank.Open "SELECT * FROM Vendors_Bank WHERE VendorID=" & PK, CN, adOpenStatic, adLockOptimistic
    
    DeleteItemsBank
    
    Dim cBank As Integer
    
    With GridBanks
        'Save the details of the records
        For cBank = 1 To cIRowCountBank
            .Row = cBank
            If State = adStateAddMode Or State = adStatePopupMode Then
AddNewBank:
                rsVendorBank.AddNew

                rsVendorBank![VendorID] = PK
                rsVendorBank![BankID] = toNumber(.TextMatrix(cBank, 5))
                rsVendorBank![AccountNo] = .TextMatrix(cBank, 3)
                rsVendorBank![AccountName] = .TextMatrix(cBank, 4)

                rsVendorBank.Update
            ElseIf State = adStateEditMode Then
                rsVendorBank.Filter = "BankID = " & toNumber(.TextMatrix(cBank, 5))
            
                If rsVendorBank.RecordCount = 0 Then GoTo AddNewBank

                rsVendorBank![VendorID] = PK
                rsVendorBank![BankID] = toNumber(.TextMatrix(cBank, 5))
                rsVendorBank![AccountNo] = .TextMatrix(cBank, 3)
                rsVendorBank![AccountName] = .TextMatrix(cBank, 4)

                rsVendorBank.Update
            End If

        Next cBank
    End With
   
    '-------------Stocks--------------------
    
    Set rsVendorBank = Nothing
    
    Dim rsVendorStock As New Recordset
    
    'save vendorstock
    rsVendorStock.CursorLocation = adUseClient
    rsVendorStock.Open "SELECT * FROM Vendors_Stocks WHERE VendorId = " & PK, CN, adOpenStatic, adLockOptimistic
    
    DeleteItemsStock
    
    Dim cStock As Integer
    
    With GridStocks