frmvancollectionae.frm

Inventory control system

        Unload Me

    Else
        MsgBox "Changes in  record has been successfully saved.", vbInformation
        Unload Me
    End If

    Exit Sub
err:
    CN.RollbackTrans
    prompt_err err, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

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

Private Sub Form_Activate()
    On Error Resume Next
    If CloseMe = True Then Unload Me: Exit Sub
    txtEntry(0).SetFocus
End Sub

Private Sub Form_Load()
    
    'Bind the data combo
    bind_dc "SELECT * FROM tbl_AR_Van", "VanName", dcVan, "PK", True
    bind_dc "SELECT * FROM tbl_AR_Salesman", "Name", dcSalesman, "PK", True

    InitGrid

    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        frmVanCollectionAEPickFrom.show vbModal
        
        'Initialize controls
        cbPT.ListIndex = 0
        InitNSD

        'Set the recordset
         rs.Open "SELECT * FROM tbl_AR_VanCollection WHERE PK=" & PK, CN, adOpenStatic, adLockOptimistic
         dtpDate.Value = Date
         dtColDate.Value = Date
         Caption = "Create New Entry"
         cmdUsrHistory.Enabled = False
         GeneratePK
    Else
        Screen.MousePointer = vbHourglass
        'Set the recordset
        rs.Open "SELECT * FROM qry_AR_VanCollection WHERE PK=" & PK, CN, adOpenStatic, adLockOptimistic
        
        Caption = "View Record"
        cmdCancel.Caption = "Close"
        cmdUsrHistory.Enabled = True
        
        DisplayForViewing
        
        
        MsgBox "This is use for viewing the record only." & vbCrLf & _
               "You cannot perform any changes in this form." & vbCrLf & vbCrLf & _
               "Note:If you have mistake in adding this record then " & vbCrLf & _
               "void this record and re-enter.", vbExclamation
        
        Screen.MousePointer = vbDefault
    End If

End Sub

'Procedure used to generate PK
Private Sub GeneratePK()
    PK = getIndex("tbl_AR_VanCollection")
    txtEntry(0).Text = "COL" & GenerateID(PK, Format$(Date, "yyyy") & Format$(Date, "mm") & Format$(Date, "dd") & "-", "0")
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cCRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 9
        .ColSel = 8
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 2000
        .ColWidth(2) = 2000
        .ColWidth(3) = 3000
        .ColWidth(4) = 2000
        .ColWidth(5) = 1500
        .ColWidth(6) = 1500
        .ColWidth(7) = 4000
        .ColWidth(8) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Date"
        .TextMatrix(0, 2) = "Invoice No"
        .TextMatrix(0, 3) = "Customer Name"
        .TextMatrix(0, 4) = "Payment Type"
        .TextMatrix(0, 5) = "Payment"
        .TextMatrix(0, 6) = "Balance"
        .TextMatrix(0, 7) = "Remarks"
        .TextMatrix(0, 8) = "InvoiceFK"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
        .ColAlignment(3) = vbLeftJustify
        .ColAlignment(4) = vbLeftJustify
        .ColAlignment(5) = vbLeftJustify
        .ColAlignment(6) = vbLeftJustify
        .ColAlignment(7) = vbLeftJustify
        .ColAlignment(8) = vbLeftJustify
    End With
End Sub

Private Sub ResetEntry()
    dtColDate.Value = Date
    nsdInvoice.ResetValue
    txtCusAdd.Text = ""
    txtBal.Text = "0.00"
    
    txtPayment.Text = "0.00"
    cbPT.ListIndex = 0
    txtRem.Text = ""
    
    txtInv.Text = ""
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If HaveAction = True Then
        frmVanCollection.RefreshRecords
        MAIN.UpdateInfoMsg
    End If
    
    Set frmVanCollectionAE = Nothing
End Sub

Private Sub Grid_Click()
    If State = adStateEditMode Then Exit Sub
    If chkOldInv.Value = 1 Then
        If Grid.Rows = 2 And Grid.TextMatrix(1, 8) = "" Then
            btnRemove.Visible = False
        Else
            btnRemove.Visible = True
            btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
            btnRemove.Left = Grid.Left + 50
        End If
    Else
        If Grid.Rows = 2 And Grid.TextMatrix(1, 5) = "" Then
            btnRemove.Visible = False
        Else
            btnRemove.Visible = True
            btnRemove.Top = (Grid.CellTop + Grid.Top) - 20
            btnRemove.Left = Grid.Left + 50
        End If
    End If

End Sub


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

Private Sub Grid_SelChange()
    Grid_Click
End Sub

Private Sub nsdInvoice_Change()
    txtPayment.Text = "0.00"
    cbPT.ListIndex = 0
    txtRem.Text = ""
    
    If nsdInvoice.Text = "" Then
        txtPayment.Enabled = False
    Else
        txtPayment.Enabled = True
    End If
    
    txtCusAdd.Text = nsdInvoice.getSelValueAt(3)
    txtBal.Text = toMoney(toNumber(nsdInvoice.getSelValueAt(9)))
    txtBal.Tag = toMoney(toNumber(nsdInvoice.getSelValueAt(9)))
End Sub

Private Sub txtDate_GotFocus()
    HLText txtDate
End Sub

Private Sub txtPayment_Change()
    If chkOldInv.Value = 1 Then
        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 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 = 8 Then
        cmdSave.Default = False
    End If
End Sub

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

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

Private Sub txtEntry_Validate(Index As Integer, Cancel As Boolean)
    If Index > 1 And Index < 8 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(8).Text = ""

    txtTA.Text = "0.00"

    cCAmount = 0

    txtEntry(0).SetFocus
End Sub

'Used to display record
Private Sub DisplayForViewing()
    On Error GoTo err
    txtEntry(0).Text = rs![CollectionNo]
    txtDate.Text = Format$(rs![Date], "MMM-dd-yyyy")
    dcVan.BoundText = rs![VanFK]
    dcSalesman.BoundText = rs![SalesmanFK]
    txtEntry(8).Text = rs![Remarks]
    txtTA.Text = toMoney(toNumber(rs![Collection]))
    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_AR_VanCollectionDetails WHERE VCFK=" & PK & " ORDER BY PK ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            With Grid
                If .Rows = 2 And .TextMatrix(1, 8) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Date]
                    .TextMatrix(1, 2) = RSDetails![InvoiceNo]
                    .TextMatrix(1, 3) = RSDetails![CustomerName]
                    .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![InvoiceFK]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Date]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![InvoiceNo]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![CustomerName]
                    .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![InvoiceFK]
                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 = 2
        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

    'Resize and reposition the controls
    Shape3.Top = 900
    Label11.Top = 900
    Grid.Top = 1200
    Grid.Height = 3690
    
    ctrlLiner2.Visible = False
    ctrlLiner3.Visible = False
    
    Label3.Top = 5025
    txtTA.Top = 5025
    
    Labels(4).Top = 5025
    txtEntry(8).Top = 5250
    
    ctrlLiner1.Top = 6400
    
    cmdUsrHistory.Top = 6550
    cmdCancel.Top = 6550
       
    Me.Height = 7500
    Me.Top = (Screen.Height - Me.Height) / 2

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

Private Sub InitNSD()
    'For Invoice
    With nsdInvoice
        .ClearColumn
        .AddColumn "Invoice No", 1794.89
        .AddColumn "Date", 1994.89
        .AddColumn "Sold To", 2264.88
        .AddColumn "Address", 2670.23
        .AddColumn "Discount", 1400
        .AddColumn "Total Amount", 1400
        .AddColumn "Down Payment", 1400
        .AddColumn "Amount Paid", 1400
        .AddColumn "Balance", 1400
        
        .Connection = CN.ConnectionString
        .sqlFields = "InvoiceNo,Date,SoldTo,Address,Discount,TotalAmount,DownPayment,AmountPaid,Balance,Paid,PK"
        .sqlTables = "qry_AR_Invoice"
        .sqlwCondition = "Paid='N'"
        .sqlSortOrder = "PK DESC"
        
        .BoundField = "PK"
        .PageBy = 25
        .DisplayCol = 1
        
        .setDropWindowSize 8000, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Unpaid Invoices"
        
    End With

End Sub

Private Sub txtCusAdd_GotFocus()
    HLText txtCusAdd
End Sub

Private Sub txtTA_GotFocus()
    HLText txtTA
End Sub

Private Sub txtPayment_GotFocus()
    HLText txtPayment
End Sub