frmaccountreceivable.frm

hotel mnagement system

         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "Payment Type"
         Height          =   195
         Left            =   2160
         TabIndex        =   20
         Top             =   1680
         Width           =   1020
      End
      Begin VB.Label Label1 
         AutoSize        =   -1  'True
         BackStyle       =   0  'Transparent
         Caption         =   "Company"
         Height          =   195
         Left            =   120
         TabIndex        =   19
         Top             =   300
         Width           =   660
      End
   End
End
Attribute VB_Name = "frmAccountReceivable"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Public PK               As Long
Public Company          As String
Public Balance          As Currency
Public AmountPaid       As Currency

Dim cIRowCount          As Integer
Dim Amount              As Currency

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

    btnRemove.Visible = False

    txtBalance.Text = toMoney(toNumber(txtBalance.Text) + toNumber(Grid.TextMatrix(Grid.RowSel, 5)))
    txtAmountPaid.Text = toMoney(txtAmountPaid.Text) - toNumber(Grid.TextMatrix(Grid.RowSel, 5))
    
    Grid_Click
End Sub

Private Sub cmdAdd_Click()
    If Trim(txtAmount.Text) = "0.00" Then Exit Sub

    If toNumber(txtAmount) > Balance Then
        MsgBox "Amount paid exceed balance. Please enter correct amount.", vbInformation
        txtAmount.SetFocus
        
        Exit Sub
    End If

    Dim CurrRow As Integer

    'Add to grid
    With Grid

        'Perform if the record is not exist
        If .Rows = 2 And .TextMatrix(1, 2) = "" Then
            .TextMatrix(1, 2) = dtpDate.Value
            .TextMatrix(1, 3) = dcPaymentType.BoundText
            .TextMatrix(1, 4) = dcPaymentType.Text
            .TextMatrix(1, 5) = toMoney(txtAmount.Text)
        Else
            .Rows = .Rows + 1
            .TextMatrix(.Rows - 1, 2) = dtpDate.Value
            .TextMatrix(.Rows - 1, 3) = dcPaymentType.BoundText
            .TextMatrix(.Rows - 1, 4) = dcPaymentType.Text
            .TextMatrix(.Rows - 1, 5) = toMoney(txtAmount.Text)

            .Row = .Rows - 1
        End If
        'Increase the record count
        cIRowCount = cIRowCount + 1
        
        txtBalance.Text = toMoney(Balance - toNumber(txtAmount.Text))
'        Balance = Balance - toNumber(txtAmountPaid.Text)
        
        txtAmountPaid.Text = toMoney(toNumber(txtAmountPaid.Text) + toNumber(txtAmount.Text))
        
        'Highlight the current row's column
        .ColSel = 5
        'Display a remove button
        Call Grid_Click
        
        Call ResetFields
    End With
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Public Sub cmdSave_Click()
    Dim rsPayments As New Recordset

    rsPayments.CursorLocation = adUseClient
    rsPayments.Open "SELECT * FROM [Accounts Receivable] WHERE AccRecID=" & PK, CN, adOpenStatic, adLockOptimistic
    
    With rsPayments
        .Fields("Debit") = txtAmountPaid.Text
        
        .Update
    End With
    
    DeleteItems
    
    Dim rsPaymentRec As New Recordset
    rsPaymentRec.CursorLocation = adUseClient
    rsPaymentRec.Open "SELECT * FROM [Payments Received] WHERE AccRecID=" & PK, CN, adOpenStatic, adLockOptimistic

    Dim c As Integer
    
    With Grid
        'Save the details of the records
        For c = 1 To cIRowCount
            .Row = c
            
            If .TextMatrix(c, 1) = "" Then
                rsPaymentRec.AddNew
                
                rsPaymentRec![AccRecID] = PK
            Else
                rsPaymentRec.Filter = "RecievedPaymentID = " & toNumber(.TextMatrix(c, 1))
            
'                If rsPaymentRec.RecordCount = 0 Then
'                    rsPaymentRec.AddNew
'
'                    rsPaymentRec![AccRecID] = PK
'                End If
            End If

            rsPaymentRec![Date] = .TextMatrix(c, 2)
            rsPaymentRec![PaymentTypeID] = .TextMatrix(c, 3)
            rsPaymentRec![Amount] = .TextMatrix(c, 5)

            rsPaymentRec.Update
        Next c
    End With

    'Clear variables
    c = 0
    Set rsPayments = Nothing
    Set rsPaymentRec = Nothing
    
    Unload frmAccountReceivable
End Sub

Private Sub cmdUpdate_Click()
    If Trim(txtAmount.Text) = "0.00" Then Exit Sub
    
    txtBalance.Text = toMoney(txtBalance.Text) + Amount
    txtAmountPaid.Text = toMoney(txtAmountPaid.Text) - Amount

    txtBalance.Text = toMoney(toNumber(txtBalance.Text) - toNumber(txtAmount.Text))
    txtAmountPaid.Text = toMoney(toNumber(txtAmountPaid.Text) + toNumber(txtAmount.Text))
    
    With Grid
        .TextMatrix(.RowSel, 2) = dtpDate.Value
        .TextMatrix(.RowSel, 3) = dcPaymentType.BoundText
        .TextMatrix(.RowSel, 4) = dcPaymentType.Text
        .TextMatrix(.RowSel, 5) = toMoney(txtAmount.Text)
    End With

    Call Grid_Click
    
    Call ResetFields
End Sub

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

Private Sub Form_Load()
    Call InitGrid
    
    bind_dc "SELECT * FROM [Payment Type]", "PaymentType", dcPaymentType, "paymentTypeID", True

    txtCompany.Text = Company
    txtBalance.Text = toMoney(Balance)
    txtAmountPaid.Text = toMoney(AmountPaid)
    
    dtpDate.Value = Date
    
    DisplayForEditing
    
    If Balance = "0.00" Then
        Grid.Height = 3000
        Grid.Top = 2160
        
        dtpDate.Visible = False
        dcPaymentType.Visible = False
        txtAmount.Visible = False
        cmdAdd.Visible = False
        cmdUpdate.Visible = False
        lblPaid.Visible = True
    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 = 6
        .ColSel = 5
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 0
        .ColWidth(2) = 1200
        .ColWidth(3) = 0
        .ColWidth(4) = 1200
        .ColWidth(5) = 1200

        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "RecievedPaymentID"
        .TextMatrix(0, 2) = "Date"
        .TextMatrix(0, 3) = "Payment Type ID"
        .TextMatrix(0, 4) = "Payment Type"
        .TextMatrix(0, 5) = "Amount"
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    frmAccountReceivableList.RefreshRecords
    
    Set frmAccountReceivable = Nothing
End Sub

Private Sub Grid_Click()
    With Grid
        dtpDate.Value = .TextMatrix(.RowSel, 2)
        dcPaymentType.BoundText = .TextMatrix(.RowSel, 3)
        txtAmount.Text = toMoney(.TextMatrix(.RowSel, 5))
        
        Amount = .TextMatrix(.RowSel, 5)
        
        If Grid.Rows = 2 And Grid.TextMatrix(1, 2) = "" Then
            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 ResetFields()
    txtAmount.Text = ""
    
    txtAmount.SetFocus
End Sub

Private Sub DisplayForEditing()
    On Error GoTo err
    
    'Display the details
    Dim rsPayments As New Recordset

    cIRowCount = 0
    
    rsPayments.CursorLocation = adUseClient
    rsPayments.Open "SELECT * FROM [qry_Payment_Received] WHERE AccRecID=" & PK, CN, adOpenStatic, adLockOptimistic
    
    If rsPayments.RecordCount > 0 Then
        rsPayments.MoveFirst
        While Not rsPayments.EOF
          cIRowCount = cIRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 1) = "" Then
                    .TextMatrix(1, 1) = rsPayments!RecievedPaymentID
                    .TextMatrix(1, 2) = rsPayments!Date
                    .TextMatrix(1, 3) = rsPayments!PaymentTypeID
                    .TextMatrix(1, 4) = rsPayments!PaymentType
                    .TextMatrix(1, 5) = toMoney(rsPayments!Amount)
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = rsPayments!RecievedPaymentID
                    .TextMatrix(.Rows - 1, 2) = rsPayments!Date
                    .TextMatrix(.Rows - 1, 3) = rsPayments!PaymentTypeID
                    .TextMatrix(.Rows - 1, 4) = rsPayments!PaymentType
                    .TextMatrix(.Rows - 1, 5) = toMoney(rsPayments!Amount)
                End If
            End With
            
'            AmountPaid = rsPayments!Amount
            
            rsPayments.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 5
        'Set fixed cols

        Grid.FixedRows = Grid.Row: 'Grid.SelectionMode = flexSelectionFree
        Grid.FixedCols = 1
    End If

    rsPayments.Close
    'Clear variables
    Set rsPayments = Nothing

    Exit Sub
err:
    If err.Number = 94 Then Resume Next
    
    prompt_err err, Name, "DisplayForEditing"
    Screen.MousePointer = vbDefault
End Sub

Private Sub DeleteItems()
    Dim CurrRow As Integer
    Dim rsPayments As New Recordset
    
    rsPayments.CursorLocation = adUseClient
    rsPayments.Open "SELECT * FROM [Payments Received] WHERE AccRecID=" & PK, CN, adOpenStatic, adLockOptimistic
    If rsPayments.RecordCount > 0 Then
        rsPayments.MoveFirst
        While Not rsPayments.EOF
            CurrRow = getFlexPos(Grid, 1, rsPayments!RecievedPaymentID)
        
            'Add to grid
            With Grid
                If CurrRow < 0 Then
                    'Delete record if doesnt exist in flexgrid
                    DelRecwSQL "Payments", "RecievedPaymentID", "", True, rsPayments!RecievedPaymentID
                End If
            End With
            rsPayments.MoveNext
        Wend
    End If
End Sub

Private Sub txtAmount_GotFocus()
    HLText txtAmount
End Sub

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

Private Sub txtAmount_Validate(Cancel As Boolean)
    txtAmount.Text = toMoney(txtAmount.Text)
End Sub