frminvoiceae.frm

Inventory control system

                txtTA.Text = Format$(cIAmount, "#,##0.00")
                cDAmount = cDAmount - toNumber(Grid.TextMatrix(.RowSel, 12))
                txtDesc.Text = Format$(cDAmount, "#,##0.00")
                
                .TextMatrix(CurrRow, 1) = nsdProduct.Text
                .TextMatrix(CurrRow, 2) = nsdProduct.getSelValueAt(2)
                .TextMatrix(CurrRow, 3) = txtSP.Text 'Unit price
                .TextMatrix(CurrRow, 4) = txtEntry(2).Text
                .TextMatrix(CurrRow, 5) = txtEntry(3).Text
                .TextMatrix(CurrRow, 6) = txtEntry(4).Text
                .TextMatrix(CurrRow, 7) = txtTQty.Text
                .TextMatrix(CurrRow, 8) = toNumber(cbDisc.Text)
                .TextMatrix(CurrRow, 9) = txtAmount.Text
                .TextMatrix(CurrRow, 10) = changeYNValue(ckFree.Value)
                .TextMatrix(CurrRow, 11) = nsdProduct.BoundText
                .TextMatrix(CurrRow, 12) = toNumber(toNumber(cbDisc.Text) / 100) * toNumber(toNumber(txtTQty.Text) * toNumber(txtSP.Text))
                .TextMatrix(CurrRow, 13) = txtSP.Tag 'Unit cost
            Else
                Exit Sub
            End If
        End If
        'Add the amount to current load amount
        cIAmount = cIAmount + toNumber(txtAmount.Text)
        cDAmount = cDAmount + toNumber(toNumber(cbDisc.Text) / 100) * (toNumber(toNumber(txtTQty.Text) * toNumber(txtSP.Text)))
        txtDesc.Text = Format$(cDAmount, "#,##0.00")
        txtTA.Text = Format$(cIAmount, "#,##0.00")
        'Highlight the current row's column
        .ColSel = 11
        'Display a remove button
        Grid_Click
        'Reset the entry fields
        ResetEntry
    End With
End Sub

Private Sub btnRemove_Click()
    'Remove selected load product
    With Grid
        'Update amount to current invoice amount
        cIAmount = cIAmount - toNumber(Grid.TextMatrix(.RowSel, 9))
        txtTA.Text = Format$(cIAmount, "#,##0.00")
        'Update discount to current invoice disc
        cDAmount = cDAmount - toNumber(Grid.TextMatrix(.RowSel, 12))
        txtDesc.Text = Format$(cDAmount, "#,##0.00")
        'Update the record count
        cIRowCount = cIRowCount - 1
        
        If .Rows = 2 Then Grid.Rows = Grid.Rows + 1
        .RemoveItem (.RowSel)
    End With

    btnRemove.Visible = False
    Grid_Click
    
End Sub

Private Sub cbBI_Click()
    'Not paid Option
    If cbBI.ListIndex = 0 Then
        txtDP.Enabled = False
        txtDP.Text = "0.00"
        cbPT.ListIndex = -1
        cbPT.Enabled = False
    Else 'If Partial
        txtDP.Enabled = True
        cbPT.ListIndex = 0
        cbPT.Enabled = True
    End If
End Sub

Private Sub cbCA_Click()
    txtDP.Text = "0.00"
    'Charge Account Option
    If cbCA.ListIndex = 1 Then 'If Credit
        cbBI.Visible = True
        Label2.Visible = True
        txtDP.Visible = True
        cbPT.ListIndex = -1
        cbPT.Enabled = False
        
        Label1.Visible = True
        Label9.Visible = True
        txtAP.Visible = True
        txtBal.Visible = True
        
    Else 'If Cash
        cbBI.Visible = False
        Label2.Visible = False
        txtDP.Visible = False
        cbPT.ListIndex = 0
        cbPT.Enabled = True
        
        Label1.Visible = False
        Label9.Visible = False
        txtAP.Visible = False
        txtBal.Visible = False
    End If
End Sub

Private Sub cbDisc_Change()
    txtTQty_Change
End Sub

Private Sub cbDisc_Click()
    txtTQty_Change
End Sub

Private Sub ckFree_Click()
If ckFree.Value = 1 Then 'If checked
    cbDisc.Text = "0"
    cbDisc.Visible = False
    txtAmount.Text = "0.00"
    txtAmount.Visible = False
    Labels(14).Visible = False
    Labels(17).Visible = False
Else
    cbDisc.Visible = True
    txtAmount.Visible = True
    Labels(14).Visible = True
    Labels(17).Visible = True
End If
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cbDisc_Validate(Cancel As Boolean)
    cbDisc.Text = toNumber(cbDisc.Text)
End Sub

Private Sub cmdGenerate_Click()
    GeneratePK
End Sub

Private Sub cmdNew_Click()
    With frmCustomerAE
        .State = adStatePopupMode
        Set .srcText = txtNCus
        Set .srcTextAdd = txtCusAdd
        Set .srcTextCP = txtCusCP
        Set .srcTextDisc = cbDisc
        .show vbModal
    End With
    If txtNCus.Tag = "" And txtNCus.Text = "" Then Exit Sub
    nsdCustomer.DisableDropdown = True
    nsdCustomer.Text = txtNCus.Text
End Sub

Private Sub cmdPH_Click()
    frmInvoiceViewerPH.INV_PK = PK
    frmInvoiceViewerPH.Caption = "Payment History Viewer"
    frmInvoiceViewerPH.lblTitle.Caption = "Payment History Viewer"
    frmInvoiceViewerPH.show vbModal
End Sub

Private Sub cmdReset_Click()
    With nsdCustomer
        .ResetValue
        .DisableDropdown = False
    End With
    
    txtNCus.Tag = ""
    txtNCus.Text = ""
    
    txtCusAdd.Text = ""
    txtCusCP.Text = ""
End Sub

Private Sub cmdSave_Click()
    'Verify the entries
    If txtEntry(0).Text = "" Then
        MsgBox "Please enter an invoice number.", vbExclamation
        txtEntry(0).SetFocus
        Exit Sub
    End If
       
    If dcSalesman.BoundText = "" Then
        MsgBox "Please select a salesman in the list.", vbExclamation
        dcSalesman.SetFocus
        Exit Sub
    End If
    
    If nsdCustomer.BoundText = "" And txtNCus.Tag = "" Then
        MsgBox "Please select a customer.", vbExclamation
        nsdCustomer.SetFocus
        Exit Sub
    End If
    
    If txtBal.Visible = True And toNumber(txtBal.Text) <= 0 Then
            MsgBox "Please enter a valid downpayment.", vbExclamation
            txtDP.SetFocus
            Exit Sub
    End If
    
    If cIRowCount < 1 Then
        MsgBox "Please enter a sold product first before you can save this record.", vbExclamation
        nsdProduct.SetFocus
        Exit Sub
    End If
    
    If isRecordExist("tbl_AR_Invoice", "InvoiceNo", txtEntry(0).Text, True) = True Then
        MsgBox "Invoice No. already exist.Please change it.", vbExclamation
        txtEntry(0).SetFocus
        Exit Sub
    End If
    

    If MsgBox("This save the record.Do you want to proceed?", vbQuestion + vbYesNo) = vbNo Then Exit Sub


    Dim RSDetails As New Recordset


    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM tbl_AR_InvoiceDetails WHERE InvoiceFK=" & PK, CN, adOpenStatic, adLockOptimistic

    Screen.MousePointer = vbHourglass

    Dim c As Integer

    On Error GoTo err

    CN.BeginTrans

    If cbPT.ListIndex = 2 Then
        Screen.MousePointer = vbDefault
        
        frmPDCManagerAE.State = adStatePopupMode
        If toNumber(txtDP.Text) > 0 Then
            frmPDCManagerAE.txtEntry(6).Text = "Downpayment for Invoice No. " & txtEntry(0).Text & "."
            frmPDCManagerAE.txtEntry(3).Text = toMoney(toNumber(txtDP.Text))
        Else
            frmPDCManagerAE.txtEntry(6).Text = "Payment for Invoice No. " & txtEntry(0).Text & "."
            frmPDCManagerAE.txtEntry(3).Text = toMoney(toNumber(txtTA.Text))
        End If
        frmPDCManagerAE.show vbModal
        
        Screen.MousePointer = vbHourglass
    End If

    If nsdCustomer.BoundText <> "" Then
        If toNumber(getRecordCount("tbl_AR_Invoice", "WHERE SoldToPK =" & nsdCustomer.BoundText)) >= 1 And getValueAt("SELECT PK,Status FROM tbl_AR_Customer WHERE PK=" & nsdCustomer.BoundText, "Status") = "New" Then
            ChangeValue CN, "tbl_AR_Customer", "Status", "Old", False, "WHERE PK=" & nsdCustomer.BoundText
        End If
    Else
        If toNumber(getRecordCount("tbl_AR_Invoice", "WHERE SoldToPK =" & txtNCus.Tag)) >= 1 And getValueAt("SELECT PK,Status FROM tbl_AR_Customer WHERE PK=" & txtNCus.Tag, "Status") = "New" Then
            ChangeValue CN, "tbl_AR_Customer", "Status", "Old", False, "WHERE PK=" & txtNCus.Tag
        End If
    End If


    'Save the record
    With rs
        If State = adStateAddMode Or State = adStatePopupMode Then
            .AddNew
            ![PK] = PK
            ![DateAdded] = Now
            ![AddedByFK] = CurrUser.USER_PK
        Else
            ![DateModified] = Now
            ![LastUserFK] = CurrUser.USER_PK
        End If
        ![InvoiceNo] = txtEntry(0).Text
        ![Date] = dtpDate.Value
        If nsdCustomer.BoundText <> "" Then
            ![SoldToPK] = nsdCustomer.BoundText
        Else
            ![SoldToPK] = txtNCus.Tag
        End If
        ![VanFK] = LLVFK
        ![SalesmanFK] = dcSalesman.BoundText
        ![LastLoadingFK] = LLFK
        ![ChargeAccount] = cbCA.Text
        ![PaymentType] = cbPT.Text
        If cbBI.Visible = True Then
            ![BilledIn] = cbBI.Text
            ![AmountPaid] = toNumber(txtAP.Text)
            ![Paid] = "N"
        Else
            ![BilledIn] = "Full Payment"
            ![AmountPaid] = toNumber(txtTA.Text)
            ![Paid] = "Y"
        End If
        ![DownPayment] = toNumber(txtDP.Text)
        ![DAmount] = cDAmount
        ![TAmount] = cIAmount - toNumber(txtLess.Text)
        ![Less] = toNumber(txtLess.Text)
        ![Remarks] = txtEntry(8).Text
        
        .Update
        
    End With

    With Grid
        'Save the details of the records
        For c = 1 To cIRowCount
            .Row = c
            If State = adStateAddMode Or State = adStatePopupMode Then
            
                RSDetails.AddNew

                RSDetails![PK] = getIndex("tbl_AR_InvoiceDetails")

                RSDetails![InvoiceFK] = PK
                RSDetails![ProductFK] = toNumber(.TextMatrix(c, 11))
                RSDetails![UnitCost(Each)] = toNumber(.TextMatrix(c, 13))
                RSDetails![SalesPrice(Each)] = toNumber(.TextMatrix(c, 3))
                
                RSDetails![SoldCases] = toNumber(.TextMatrix(c, 4))
                RSDetails![SoldBoxes] = toNumber(.TextMatrix(c, 5))
                RSDetails![SoldPieces] = toNumber(.TextMatrix(c, 6))
                RSDetails![TotalQty] = toNumber(.TextMatrix(c, 7))
                RSDetails![Disc] = toNumber(.TextMatrix(c, 8))
                RSDetails![Discount] = toNumber(.TextMatrix(c, 12))
                RSDetails![Free] = .TextMatrix(c, 10)

                RSDetails.Update

            End If

        Next c
    End With

    'Clear variables
    c = 0
    Set RSDetails = Nothing

    CN.CommitTrans

    HaveAction = True
    Screen.MousePointer = vbDefault

    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
            GeneratePK
         Else
            Unload Me
        End If
    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
    Else
        txtEntry(0).SetFocus
    End If
End Sub

Private Sub Form_Load()
    InitGrid

    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        frmInvoiceAEPickFrom.show vbModal
        
        'Bind the data combo
        bind_dc "SELECT * FROM tbl_AR_Salesman", "Name", dcSalesman, "PK", True
        'Initialize controls
        cbBI.ListIndex = 0
        cbCA.ListIndex = 0
        cbPT.ListIndex = 0
        InitNSD
        

        'Discount per product
        Labels(14).Visible = True
        cbDisc.Visible = True
        
        'Set the recordset
         rs.Open "SELECT * FROM tbl_AR_Invoice WHERE PK=" & PK, CN, adOpenStatic, adLockOptimistic
         dtpDate.Value = Date
         Caption = "Create New Entry"
         cmdUsrHistory.Enabled = False
         GeneratePK
    Else
        Screen.MousePointer = vbHourglass
        'Set the recordset
        rs.Open "SELECT * FROM qry_AR_Invoice WHERE PK=" & PK, CN, adOpenStatic, adLockOptimistic
        
        If rs![Paid] = "Y" Then
            Caption = "View Record (Paid)"
        Else
            Caption = "View Record (Not Paid)"
        End If
        
        cmdCancel.Caption = "Close"
        cmdUsrHistory.Enabled = True
        
        txtEntry(0).Width = txtDate.Width
        
        DisplayForViewing
        If cbCA.ListIndex = 1 Then cmdPH.Enabled = True
        
        If ForCusAcc = True Then
            Me.Icon = frmAccCustomer.Icon
        Else
            
            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
        End If

        Screen.MousePointer = vbDefault
    End If
    
    'Initialize Graphics
    With MAIN
        cmdGenerate.Picture = .i16x16.ListImages(14).Picture
        cmdNew.Picture = .i16x16.ListImages(10).Picture
        cmdReset.Picture = .i16x16.ListImages(15).Picture
    End With
    
    'Fill the discount combo
    cbDisc.AddItem "0.01"
    cbDisc.AddItem "0.02"
    cbDisc.AddItem "0.03"
    cbDisc.AddItem "0.04"
    cbDisc.AddItem "0.05"
    cbDisc.AddItem "0.06"
    cbDisc.AddItem "0.07"
    cbDisc.AddItem "0.08"
    cbDisc.AddItem "0.09"
    cbDisc.AddItem "0.1"
    
End Sub

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

'Procedure used to initialize the grid