frminvoiceae.frm

Inventory control system

Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 14
        .ColSel = 11
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 2025
        .ColWidth(2) = 2505
        .ColWidth(3) = 1545
        .ColWidth(4) = 900
        .ColWidth(5) = 900
        .ColWidth(6) = 900
        .ColWidth(7) = 900
        .ColWidth(8) = 900
        .ColWidth(9) = 1545
        .ColWidth(10) = 750
        .ColWidth(11) = 0
        .ColWidth(12) = 0
        .ColWidth(13) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Product Code"
        .TextMatrix(0, 2) = "Description"
        .TextMatrix(0, 3) = "Sales Price(Each)"
        .TextMatrix(0, 4) = "Cases"
        .TextMatrix(0, 5) = "Boxes"
        .TextMatrix(0, 6) = "Pieces"
        .TextMatrix(0, 7) = "Qty Sold"
        .TextMatrix(0, 8) = "Disc%"
        .TextMatrix(0, 9) = "Amount"
        .TextMatrix(0, 10) = "FREE"
        .TextMatrix(0, 11) = "ProductFK"
        .TextMatrix(0, 12) = "Disc"
        .TextMatrix(0, 13) = "UC" 'Unit Cost
        '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
        .ColAlignment(9) = vbLeftJustify
        .ColAlignment(10) = vbLeftJustify
        .ColAlignment(11) = vbLeftJustify
        .ColAlignment(12) = vbLeftJustify
        .ColAlignment(13) = vbLeftJustify
    End With
End Sub

Private Sub ResetEntry()
    txtEntry(2).Text = "0"
    txtEntry(3).Text = "0"
    txtEntry(4).Text = "0"

    cbDisc.Text = toNumber(nsdCustomer.getSelValueAt(8))
    ckFree.Value = 0
    
    nsdProduct.ResetValue
    txtSP.Tag = 0
    txtSP.Text = "0.00"
    PCase = 0
    PBox = 0
End Sub

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

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


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

Private Sub Grid_SelChange()
    Grid_Click
End Sub

Private Sub nsdCustomer_Change()
    If nsdCustomer.DisableDropdown = False Then
    
        txtCusAdd.Text = nsdCustomer.getSelValueAt(3)
        
        If nsdCustomer.getSelValueAt(4) <> "" Then txtCusAdd.Text = txtCusAdd.Text & "," & nsdCustomer.getSelValueAt(4)
        If nsdCustomer.getSelValueAt(5) <> "" Then txtCusAdd.Text = txtCusAdd.Text & "," & nsdCustomer.getSelValueAt(5)
        If nsdCustomer.getSelValueAt(6) <> "" Then txtCusAdd.Text = txtCusAdd.Text & "," & nsdCustomer.getSelValueAt(6)
             
        txtCusCP.Text = nsdCustomer.getSelValueAt(7)
        If ckFree.Value = 1 Then
            cbDisc.Text = "0"
            cbDisc.Enabled = False
        Else
            cbDisc.Text = toNumber(nsdCustomer.getSelValueAt(8))
            cbDisc.Enabled = True
        End If
    End If
End Sub

Private Sub nsdProduct_Change()
    txtEntry(2).Text = "0"
    txtEntry(3).Text = "0"
    txtEntry(4).Text = "0"
    
    txtSP.Tag = nsdProduct.getSelValueAt(3) 'Unit Cost
    txtSP.Text = nsdProduct.getSelValueAt(4) 'Selling Price
    PCase = toNumber(nsdProduct.getSelValueAt(6))
    PBox = toNumber(nsdProduct.getSelValueAt(5))
End Sub

Private Sub txtAmount_GotFocus()
    HLText txtAmount
End Sub

Private Sub txtAP_Change()
    txtBal.Text = toMoney(toNumber(txtTA.Text) - toNumber(txtAP.Text))
End Sub

Private Sub txtBal_GotFocus()
    HLText txtBal
End Sub

Private Sub txtDate_GotFocus()
    HLText txtDate
End Sub

Private Sub txtDesc_GotFocus()
    HLText txtDesc
End Sub

Private Sub txtDP_Change()
    txtAP.Text = toMoney(toNumber(txtDP.Text))
End Sub

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

Private Sub txtEntry_Change(Index As Integer)
    If Index > 1 And Index < 5 Then
        txtTQty.Text = (toNumber(txtEntry(2).Text) * PCase) + (toNumber(txtEntry(3).Text) * PBox) + toNumber(txtEntry(4).Text)
    End If
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

Private Sub txtLess_GotFocus()
    HLText txtLess
End Sub

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

Private Sub txtSP_Change()
    txtTQty_Change
End Sub

Private Sub txtSP_Validate(Cancel As Boolean)
    txtSP.Text = toMoney(toNumber(txtSP.Text))
End Sub

Private Sub txtTA_Change()
    txtAP_Change
End Sub

Private Sub txtTQty_Change()
    If toNumber(txtTQty.Text) < 1 Then
        btnSold.Enabled = False
    Else
        btnSold.Enabled = True
    End If
    txtAmount.Text = toMoney((toNumber(txtTQty.Text) * toNumber(txtSP.Text)) - ((toNumber(cbDisc.Text) / 100) * toNumber(toNumber(txtTQty.Text) * toNumber(txtSP.Text))))
End Sub

Private Sub txtTQty_GotFocus()
    HLText txtTQty
End Sub

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

'Procedure used to reset fields
Private Sub ResetFields()
    InitGrid
    ResetEntry
    cmdReset_Click
    
    dtpDate.Value = Date
    cbCA.ListIndex = 0
    
    txtEntry(8).Text = ""
    
    txtDesc.Text = "0.00"
    txtTA.Text = "0.00"
    txtAP.Text = "0.00"
    txtBal.Text = "0.00"

    cIAmount = 0
    cDAmount = 0

    txtEntry(0).SetFocus
End Sub

'Used to display record
Private Sub DisplayForViewing()
    On Error GoTo err
    txtEntry(0).Text = rs![InvoiceNo]
    txtDate.Text = Format$(rs![Date], "MMM-dd-yyyy")
    txtVan.Text = rs![VanName]
    bind_dc "SELECT * FROM tbl_AR_Salesman", "Name", dcSalesman, "PK", True
    dcSalesman.BoundText = rs![SalesmanFK]
    'Initialize nsd controls
    nsdCustomer.DisableDropdown = True
    nsdCustomer.TextReadOnly = True
    nsdCustomer.Text = rs![SoldTo]
    txtCusAdd.Text = rs![Address]
    txtCusCP.Text = rs![ContactPerson]
    'Display charge account
    If rs![ChargeAccount] = "Cash" Then
        cbCA.ListIndex = 0
    Else
        cbCA.ListIndex = 1
    End If
    'Display payment type
    If rs![PaymentType] = "Cash" Then
        cbPT.ListIndex = 0
    ElseIf rs![PaymentType] = "On Date Check" Then
        cbPT.ListIndex = 1
    Else
        cbPT.ListIndex = 2
    End If
    'Display billed in
    If rs![BilledIn] = "Full Payment" Then
        cbBI.Visible = False
    ElseIf rs![BilledIn] = "Not Paid" Then
        cbBI.ListIndex = 0
    Else
        cbBI.ListIndex = 1
    End If
    txtDP.Text = toMoney(toNumber(rs![DownPayment]))
    
    txtEntry(8).Text = rs![Remarks]
    txtLess.Text = toMoney(rs![Less])
    txtDesc.Text = toMoney(rs![Discount])
    txtTA.Text = toMoney(rs![TotalAmount])
    txtAP.Text = toMoney(rs![AmountPaid])
    txtBal.Text = toMoney(rs![Balance])
    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_AR_InvoiceDetails WHERE InvoiceFK=" & 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, 11) = "" Then
                    .TextMatrix(1, 1) = RSDetails![ProductCode]
                    .TextMatrix(1, 2) = RSDetails![Description]
                    .TextMatrix(1, 3) = toMoney(RSDetails![SalesPrice(Each)])
                    .TextMatrix(1, 4) = RSDetails![SoldCases]
                    .TextMatrix(1, 5) = RSDetails![SoldBoxes]
                    .TextMatrix(1, 6) = RSDetails![SoldPieces]
                    .TextMatrix(1, 7) = RSDetails![TotalQty]
                    .TextMatrix(1, 8) = RSDetails![Disc]
                    .TextMatrix(1, 9) = toMoney(RSDetails![Amount])
                    .TextMatrix(1, 10) = RSDetails![Free]
                    .TextMatrix(1, 11) = RSDetails![PK]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![ProductCode]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Description]
                    .TextMatrix(.Rows - 1, 3) = toMoney(RSDetails![SalesPrice(Each)])
                    .TextMatrix(.Rows - 1, 4) = RSDetails![SoldCases]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![SoldBoxes]
                    .TextMatrix(.Rows - 1, 6) = RSDetails![SoldPieces]
                    .TextMatrix(.Rows - 1, 7) = RSDetails![TotalQty]
                    .TextMatrix(.Rows - 1, 8) = RSDetails![Disc]
                    .TextMatrix(.Rows - 1, 9) = toMoney(RSDetails![Amount])
                    .TextMatrix(.Rows - 1, 10) = RSDetails![Free]
                    .TextMatrix(.Rows - 1, 11) = RSDetails![PK]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 11
        '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

    cmdNew.Visible = False
    cmdReset.Visible = False
    cmdGenerate.Visible = False
    dtpDate.Visible = False
    txtDate.Visible = True
    picPurchase.Visible = False
    cmdSave.Visible = False
    btnSold.Visible = False
    txtLess.Locked = True

    'Resize and reposition the controls
    Shape3.Top = 2850
    Label11.Top = 2850
    Line1(1).Visible = False
    Line2(1).Visible = False
    Grid.Top = 3150
    Grid.Height = 2800

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

Private Sub InitNSD()
    'For Customer
    With nsdCustomer
        .ClearColumn
        .AddColumn "Customer ID", 1794.89
        .AddColumn "Name", 2264.88
        .AddColumn "Address", 2670.23
        .AddColumn "City/Town", 2190.04
        .AddColumn "Province", 2025.07
        .AddColumn "Zip Code", 1299.96
        .AddColumn "Contact Person", 2174.74
        .AddColumn "Disc.%", 800
        .Connection = CN.ConnectionString
        
        .sqlFields = "CustomerID, Name, Address, CityTown, Province, ZipCode, ContactPerson,Discount, Status, PK"
        .sqlTables = "tbl_AR_Customer"
        .sqlSortOrder = "Name ASC"
        
        .BoundField = "PK"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 7000, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Customer Records"
        
    End With
    'For Product
    With nsdProduct
        .ClearColumn
        .AddColumn "Product Code", 2064.882
        .AddColumn "Description", 4085.26
        .AddColumn "Unit Cost", 1500
        .AddColumn "Sales Price", 1500
        .AddColumn "Pieces Per Box", 0
        .AddColumn "Pieces Per Case", 0
        
        .Connection = CN.ConnectionString
        
        .sqlFields = "ProductCode,Description,UnitCost,SalesPrice,PiecesPerBox,PiecesPerCase,PK,LoadingFK"
        .sqlTables = "qry_IC_dwnLoadingDetails"
        .sqlSortOrder = "ProductCode ASC"
        .sqlwCondition = "LoadingFK = " & LLFK
        .BoundField = "PK"
        .PageBy = 25
        .DisplayCol = 1
        
        .setDropWindowSize 6800, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Loaded Products From " & LLDate
        
    End With

End Sub

Private Sub txtVan_GotFocus()
    HLText txtVan
End Sub

Private Sub txtCusAdd_GotFocus()
    HLText txtCusAdd
End Sub

Private Sub txtCusCP_GotFocus()
    HLText txtCusCP
End Sub

Private Sub txtSP_GotFocus()
    HLText txtSP
End Sub

Private Sub txtTA_GotFocus()
    HLText txtTA
End Sub

Private Sub txtDP_GotFocus()
    HLText txtDP
End Sub

Private Sub txtAP_GotFocus()
    HLText txtAP
End Sub

Private Sub txtLess_Change()
    txtTA.Text = toMoney(cIAmount - toNumber(txtLess.Text))
End Sub

Private Sub txtLess_Validate(Cancel As Boolean)
    txtLess.Text = toMoney(toNumber(txtLess.Text))
End Sub