frmcollectionae.frm

Inventory control system

        cbCA.ListIndex = 1
    End If
End Sub

Private Sub txtDate_GotFocus()
    HLText txtDate
End Sub

Private Sub txtPayment_Change()
    If toNumber(txtPayment.Text) > 0 Then
        btnCollect.Enabled = True
    Else
        btnCollect.Enabled = False
    End If
    
    If toNumber(txtPayment.Text) > toNumber(txtBal.Tag) Then
        txtBal.Text = "0.00"
        txtPayment.Text = toMoney(toNumber(txtBal.Tag))
        txtPayment.SelStart = Len(txtPayment.Text)
    Else
        txtBal.Text = toMoney(toNumber(txtBal.Tag) - toNumber(txtPayment.Text))
    End If
End Sub

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

Private Sub txtPayment_Validate(Cancel As Boolean)
    txtPayment.Text = toMoney(toNumber(txtPayment.Text))
End Sub

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

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

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

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

'Procedure used to reset fields
Private Sub ResetFields()
    InitGrid
    ResetEntry

    dtpDate.Value = Date

    txtEntry(3).Text = ""

    txtTA.Text = "0.00"

    cCAmount = 0

    txtEntry(0).SetFocus
End Sub

'Used to display record
Private Sub DisplayForEditing()
    On Error GoTo err
    txtEntry(0).Text = rs![ReceiptBatchID]
    dtpDate.Value = Format$(rs![DeliveryDate], "MMM-dd-yyyy")
    dcRoute.BoundText = rs![RouteID]
    txtEntry(1).Text = rs![TruckNo]
    dcBooking.BoundText = rs![BookingAgent]
    dcCollection.BoundText = rs![CollectionAgent]
    txtEntry(3).Text = rs![Remarks]
    txtTA.Text = toMoney(toNumber(rs![Gross]))
    cboStatus.Text = rs!Status_Alias
    
    'Display the details
    Dim RSDetails As New Recordset

    cCAmount = txtTA.Text
    cCRowCount = 0
    
    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Collection_Details WHERE ReceiptBatchID=" & PK & " ORDER BY CollectionDetailID ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            cCRowCount = cCRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 9) = "" Then
                    .TextMatrix(1, 1) = RSDetails![RefNo]
                    .TextMatrix(1, 2) = RSDetails![Company]
                    .TextMatrix(1, 3) = RSDetails![ChargeAccount]
                    .TextMatrix(1, 4) = RSDetails![PaymentType]
                    .TextMatrix(1, 5) = toMoney(RSDetails![Amount])
                    .TextMatrix(1, 6) = toMoney(RSDetails![Balance])
                    .TextMatrix(1, 7) = RSDetails![Remarks]
                    .TextMatrix(1, 8) = RSDetails![ClientID]
                    .TextMatrix(1, 9) = RSDetails![ReceiptID]
                    .TextMatrix(1, 10) = RSDetails![CollectionDetailID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![RefNo]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Company]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![ChargeAccount]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![PaymentType]
                    .TextMatrix(.Rows - 1, 5) = toMoney(RSDetails![Amount])
                    .TextMatrix(.Rows - 1, 6) = toMoney(RSDetails![Balance])
                    .TextMatrix(.Rows - 1, 7) = RSDetails![Remarks]
                    .TextMatrix(.Rows - 1, 8) = RSDetails![ClientID]
                    .TextMatrix(.Rows - 1, 9) = RSDetails![ReceiptID]
                    .TextMatrix(.Rows - 1, 10) = RSDetails![CollectionDetailID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 8
        'Set fixed cols
        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionFree
            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
    
    MsgBox err.Number & " " & err.Description
End Sub

Private Sub DisplayForViewing()
    On Error GoTo err
    txtEntry(0).Text = rs![ReceiptBatchID]
    dtpDate.Value = Format$(rs![DeliveryDate], "MMM-dd-yyyy")
    dcRoute.BoundText = rs![RouteID]
    txtEntry(1).Text = rs![TruckNo]
    dcBooking.BoundText = rs![Booking]
    dcCollection.BoundText = rs![Collection]
    txtEntry(3).Text = rs![Remarks]
    txtTA.Text = toMoney(toNumber(rs![Gross]))
    cboStatus.Text = rs!Status_Alias
    
    'Display the details
    Dim RSDetails As New Recordset

    cCAmount = txtTA.Text
    cCRowCount = 0
    
    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Collection_Details WHERE ReceiptBatchID=" & PK & " ORDER BY CollectionDetailID ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            cCRowCount = cCRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 8) = "" Then
                    .TextMatrix(1, 1) = RSDetails![RefNo]
                    .TextMatrix(1, 2) = RSDetails![Company]
                    .TextMatrix(1, 3) = RSDetails![ChargeAccount]
                    .TextMatrix(1, 4) = RSDetails![PaymentType]
                    .TextMatrix(1, 5) = toMoney(RSDetails![Amount])
                    .TextMatrix(1, 6) = toMoney(RSDetails![Balance])
                    .TextMatrix(1, 7) = RSDetails![Remarks]
                    .TextMatrix(1, 8) = RSDetails![ClientID]
'                    .TextMatrix(1, 8) = RSDetails![ReceiptID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![RefNo]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Company]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![ChargeAccount]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![PaymentType]
                    .TextMatrix(.Rows - 1, 5) = toMoney(RSDetails![Amount])
                    .TextMatrix(.Rows - 1, 6) = toMoney(RSDetails![Balance])
                    .TextMatrix(.Rows - 1, 7) = RSDetails![Remarks]
'                    .TextMatrix(.Rows - 1, 7) = RSDetails![ClientID]
'                    .TextMatrix(.Rows - 1, 8) = RSDetails![ReceiptID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 8
        'Set fixed cols
        If State = adStateEditMode Then
            Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionFree
            Grid.FixedCols = 1
        End If
    End If

    RSDetails.Close
    'Clear variables
    Set RSDetails = Nothing

    'Disable commands
    LockInput Me, True

    picCusInfo.Visible = False
    dtpDate.Visible = False
    txtDate.Visible = True
    cmdSave.Visible = False
    btnCollect.Visible = False
    btnRemove.Visible = False
    
    'Resize and reposition the controls
    Shape3.Top = 1350
    Label11.Top = 1350
    Grid.Top = 1600
    Grid.Height = 3590

    ctrlLiner2.Visible = False
    ctrlLiner3.Visible = False

    Label3.Top = 5425
    txtTA.Top = 5425

    Labels(4).Top = 5325
    txtEntry(3).Top = 5550

    ctrlLiner1.Top = 6800

    cmdUsrHistory.Top = 6950
    cmdCancel.Top = 6950

    Me.Height = 7900
    Me.Top = (Screen.Height - Me.Height) / 2

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

Private Sub txtTA_GotFocus()
    HLText txtTA
End Sub

Private Sub txtPayment_GotFocus()
    HLText txtPayment
End Sub

Private Sub InitNSD()
    'For Invoice
    With nsdClient
        .ClearColumn
        .AddColumn "Company", 1794.89
        .AddColumn "OwnersName", 1994.89
        .AddColumn "Balance", 2264.88
        
        .Connection = CN.ConnectionString
        .sqlFields = "Company,OwnersName,Balance,ClientID"
        .sqlTables = "qry_Client_Balance"
        .sqlSortOrder = "Company ASC"
        
        .BoundField = "ClientID"
        .PageBy = 25
        .DisplayCol = 1
        
        .setDropWindowSize 6000, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Unpaid Invoices"
    End With

    'For DR#/OR#
    With nsdORNo
        .ClearColumn
        .AddColumn "OR No", 1300.89
        .AddColumn "Company", 1994.89
        .AddColumn "Balance", 1264.88
        .AddColumn "ReceiptID", 0
        
        .Connection = CN.ConnectionString
        .sqlFields = "RefNo,Company,Balance,ReceiptID"
        .sqlTables = "qry_Client_Balance_Per_Receipt"
        
        .sqlSortOrder = "RefNo ASC"
        
        .BoundField = "ReceiptID"
        .PageBy = 25
        .DisplayCol = 1
        
        .setDropWindowSize 6500, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Unpaid Invoices"
    End With
    
    'For Bank
    With nsdBank
        .ClearColumn
        .AddColumn "Bank ID", 1794.89
        .AddColumn "Bank", 2264.88
        .AddColumn "Branch", 2670.23
        .Connection = CN.ConnectionString
        
        '.sqlFields = "VendorID, Company, Location"
        .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 DeleteItems()
    Dim CurrRow As Integer
    Dim RSCollection As New Recordset

    If State = adStateAddMode Then Exit Sub

    RSCollection.CursorLocation = adUseClient
    RSCollection.Open "SELECT * FROM Collection_Details WHERE ReceiptBatchID=" & PK, CN, adOpenStatic, adLockOptimistic
    If RSCollection.RecordCount > 0 Then
        RSCollection.MoveFirst
        While Not RSCollection.EOF
            CurrRow = getFlexPos(Grid, 8, RSCollection!ClientID)

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