frmpurchaseorderae.frm

Inventory control system

    If toNumber(nsdStock.getSelValueAt(4)) = 0 Then
      txtDiscPercent.Text = toNumber(txtGenDiscPerc.Text)
    Else
      txtDiscPercent.Text = toNumber(nsdStock.getSelValueAt(4))
    End If
End Sub

Private Sub txtDate_GotFocus()
    HLText txtDate
End Sub

Private Sub txtDesc_GotFocus()
    HLText txtDesc
End Sub

Private Sub txtExtDiscAmt_Change()
    ComputeGrossNet
End Sub

Private Sub txtExtDiscAmt_GotFocus()
  HLText txtExtDiscAmt
End Sub

Private Sub txtGenDiscPerc_Change()
  ComputeGrossNet
End Sub

Private Sub txtGenDiscPerc_GotFocus()
  HLText txtGenDiscPerc
End Sub

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

Private Sub txtQty_Validate(Cancel As Boolean)
    txtQty.Text = toNumber(txtQty.Text)
End Sub

Private Sub txtPrice_Change()
  ComputeGrossNet
End Sub

Private Sub txtPrice_Validate(Cancel As Boolean)
    txtPrice.Text = toMoney(toNumber(txtPrice.Text))
End Sub

Private Sub txtQty_Change()
    ComputeGrossNet
End Sub

Private Sub ComputeGrossNet()
    Dim curDiscPerc As Currency
    Dim curExtDiscPerc As Currency
    
    If toNumber(txtQty.Text) < 1 Or toMoney(txtPrice.Text) < 1 Then Exit Sub
    
    txtGross(1).Text = toMoney(toNumber(txtQty.Text) * toNumber(txtPrice.Text))
    
    curDiscPerc = txtGross(1).Text * txtDiscPercent.Text / 100
    curExtDiscPerc = txtGross(1).Text * txtExtDiscPerc.Text / 100
    
    txtNetAmount.Text = txtGross(1).Text - (curDiscPerc + curExtDiscPerc + txtExtDiscAmt.Text)

    If toNumber(txtQty.Text) < 1 Then
        btnAdd.Enabled = False
    Else
        btnAdd.Enabled = True
    End If
End Sub

Private Sub txtQty_GotFocus()
    HLText txtQty
End Sub

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

'Procedure used to reset fields
Private Sub ResetFields()
    InitGrid
    ResetEntry
    
    nsdVendor.Text = ""
    txtPONo.Text = ""
    txtLocation.Text = ""
    txtDate.Text = ""
    txtContact.Text = ""
    txtSalesman.Text = ""
    'txtShippingInstructions.Text = ""
    'txtAdditionalInstructions.Text = ""
    'txtDeclaredAs.Text = ""
    'txtDeclaredValue.Text = ""
    
    txtGross(2).Text = "0.00"
    txtDesc.Text = "0.00"
    txtTaxBase.Text = "0.00"
    txtVat.Text = "0.00"
    txtNet.Text = "0.00"

    cIAmount = 0
    cDAmount = 0

    nsdVendor.SetFocus
End Sub

'Used to display record
Private Sub DisplayForEditing()
    On Error GoTo err
    nsdVendor.Tag = RS![VendorID]
    nsdVendor.DisableDropdown = True
    nsdVendor.TextReadOnly = True
    nsdVendor.Text = RS!Company
    txtCreditTerm.Text = RS!CreditTerm
    txtPONo.Text = RS!PONo
    txtLocation.Text = RS!Location
    dtpDate.Value = Format(RS![Date], "MMM-dd-yy")
    txtContact.Text = RS![Contact]
    dtpDeliveryDate.Value = RS![DeliveryDate]
    txtDeliveryTime.Text = RS![DeliveryTime]
    txtSalesman.Text = RS![Salesman]
    cboStatus.Text = RS!Status_Alias
        
    txtGross(2).Text = toMoney(toNumber(RS![Gross]))
    txtDesc.Text = toMoney(toNumber(RS![Discount]))
    txtTaxBase.Text = toMoney(RS![TaxBase])
    txtVat.Text = toMoney(RS![Vat])
    txtNet.Text = toMoney(RS![NetAmount])
    txtNotes.Text = RS![Notes]
    txtGenDiscPerc.Text = toNumber(RS![GenDiscPercent])
    
    cIGross = toNumber(txtGross(2).Text)
    cDAmount = toNumber(txtDesc.Text)
    cIAmount = toNumber(txtNet.Text)
    cIRowCount = 0
    
    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Purchase_Order_Detail WHERE POID=" & PK & " ORDER BY Stock ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            cIRowCount = cIRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 13) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Barcode]
                    .TextMatrix(1, 2) = RSDetails![Stock]
                    .TextMatrix(1, 3) = RSDetails![Qty]
                    .TextMatrix(1, 4) = RSDetails![QtyReceived]
                    .TextMatrix(1, 5) = RSDetails![QtyDue]
                    .TextMatrix(1, 6) = RSDetails![Unit]
                    .TextMatrix(1, 7) = toMoney(RSDetails![Price])
                    .TextMatrix(1, 8) = toMoney(RSDetails![Gross])
                    .TextMatrix(1, 9) = RSDetails![DiscPercent] * 100
                    .TextMatrix(1, 10) = RSDetails![ExtDiscPercent] * 100
                    .TextMatrix(1, 11) = toMoney(RSDetails![ExtDiscAmt])
                    .TextMatrix(1, 12) = toMoney(RSDetails!NetAmount)
                    .TextMatrix(1, 13) = RSDetails![StockID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Barcode]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![Qty]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![QtyReceived]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![QtyDue]
                    .TextMatrix(.Rows - 1, 6) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 7) = toMoney(RSDetails![Price])
                    .TextMatrix(.Rows - 1, 8) = toMoney(RSDetails![Gross])
                    .TextMatrix(.Rows - 1, 9) = RSDetails![DiscPercent] * 100
                    .TextMatrix(.Rows - 1, 10) = RSDetails![ExtDiscPercent] * 100
                    .TextMatrix(.Rows - 1, 11) = toMoney(RSDetails![ExtDiscAmt])
                    .TextMatrix(.Rows - 1, 12) = toMoney(RSDetails!NetAmount)
                    .TextMatrix(.Rows - 1, 13) = RSDetails![StockID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 12
        '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
    
    Exit Sub
err:
    'Error if encounter a null value
    If err.Number = 94 Then
        Resume Next
    Else
        MsgBox err.Description
    End If
End Sub

'Used to display record
Private Sub DisplayForViewing()
    On Error GoTo err
    nsdVendor.Tag = RS![VendorID]
    nsdVendor.DisableDropdown = True
    nsdVendor.TextReadOnly = True
    nsdVendor.Text = RS!Company
    txtCreditTerm.Text = RS!CreditTerm
    txtPONo.Text = RS!PONo
    txtLocation.Text = RS!Location
    txtDate.Text = Format(RS![Date], "MMM-dd-yy")
    txtContact.Text = RS![Contact]
    txtDeliveryDate.Text = RS![DeliveryDate]
    txtDeliveryTime.Text = RS![DeliveryTime]
    txtSalesman.Text = RS![Salesman]
    cboStatus.Text = RS!Status_Alias
        
    txtGross(2).Text = toMoney(toNumber(RS![Gross]))
    txtDesc.Text = toMoney(toNumber(RS![Discount]))
    txtTaxBase.Text = toMoney(RS![TaxBase])
    txtVat.Text = toMoney(RS![Vat])
    txtNet.Text = toMoney(RS![NetAmount])
    txtNotes.Text = RS![Notes]
    txtGenDiscPerc.Text = toNumber(RS![GenDiscPercent])
    
'    cIRowCount = 0
    
    'Display the details
    Dim RSDetails As New Recordset
    
    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Purchase_Order_Detail WHERE POID=" & PK & " ORDER BY Stock ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
'          cIRowCount = cIRowCount + 1     'increment
            With Grid
                If .Rows = 2 And .TextMatrix(1, 13) = "" Then
                    .TextMatrix(1, 1) = RSDetails![Barcode]
                    .TextMatrix(1, 2) = RSDetails![Stock]
                    .TextMatrix(1, 3) = RSDetails![Qty]
                    .TextMatrix(1, 4) = RSDetails![QtyReceived]
                    .TextMatrix(1, 5) = RSDetails![QtyDue]
                    .TextMatrix(1, 6) = RSDetails![Unit]
                    .TextMatrix(1, 7) = toMoney(RSDetails![Price])
                    .TextMatrix(1, 8) = toMoney(RSDetails![Gross])
                    .TextMatrix(1, 9) = RSDetails![DiscPercent] * 100
                    .TextMatrix(1, 10) = RSDetails![ExtDiscPercent] * 100
                    .TextMatrix(1, 11) = toMoney(RSDetails![ExtDiscAmt])
                    .TextMatrix(1, 12) = toMoney(RSDetails!NetAmount)
                    .TextMatrix(1, 13) = RSDetails![StockID]
                Else
                    .Rows = .Rows + 1
                    .TextMatrix(.Rows - 1, 1) = RSDetails![Barcode]
                    .TextMatrix(.Rows - 1, 2) = RSDetails![Stock]
                    .TextMatrix(.Rows - 1, 3) = RSDetails![Qty]
                    .TextMatrix(.Rows - 1, 4) = RSDetails![QtyReceived]
                    .TextMatrix(.Rows - 1, 5) = RSDetails![QtyDue]
                    .TextMatrix(.Rows - 1, 6) = RSDetails![Unit]
                    .TextMatrix(.Rows - 1, 7) = toMoney(RSDetails![Price])
                    .TextMatrix(.Rows - 1, 8) = toMoney(RSDetails![Gross])
                    .TextMatrix(.Rows - 1, 9) = RSDetails![DiscPercent] * 100
                    .TextMatrix(.Rows - 1, 10) = RSDetails![ExtDiscPercent] * 100
                    .TextMatrix(.Rows - 1, 11) = toMoney(RSDetails![ExtDiscAmt])
                    .TextMatrix(.Rows - 1, 12) = toMoney(RSDetails!NetAmount)
                    .TextMatrix(.Rows - 1, 13) = RSDetails![StockID]
                End If
            End With
            RSDetails.MoveNext
        Wend
        Grid.Row = 1
        Grid.ColSel = 12
        '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
    
    'unlock Notes
    txtNotes.Locked = False
    
    dtpDate.Visible = False
    txtDate.Visible = True
    dtpDeliveryDate.Visible = False
    txtDeliveryDate.Visible = True
    picPurchase.Visible = False
    cmdSave.Visible = False
    btnAdd.Enabled = False
    
    mnu_ReceiveItem.Visible = True
    mnu_ReceiveItem.Enabled = True
    
    'Resize and reposition the controls
    Shape3.Top = 2500
    Label11.Top = 2500
    Line1(1).Visible = False
    Line2(1).Visible = False
    Grid.Top = 2850
    Grid.Height = 3420

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

Private Sub InitNSD()
    'For Vendor
    With nsdVendor
        .ClearColumn
        .AddColumn "Supplier ID", 1794.89
        .AddColumn "Supplier", 2264.88
        .AddColumn "Location", 2670.23
        .AddColumn "Gen Disc (%)", 0
        .AddColumn "Credit Term", 0
        
        .Connection = CN.ConnectionString
        
        .sqlFields = "VendorID, Company, Location, GenDiscPercent, CreditTerm"
        .sqlTables = "qry_Vendors1"
        .sqlSortOrder = "Company ASC"
        
        .BoundField = "VendorID"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 7000, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Vendors Record"
    End With
    
    'For Stock
    With nsdStock
        .ClearColumn
        .AddColumn "Barcode", 2064.882
        .AddColumn "Product", 4085.26
        .AddColumn "Supplier Price", 1500
        .AddColumn "Disc (%)", 1500
        .AddColumn "Ext Disc (%)", 0
        .AddColumn "Ext Disc (Amt)", 0
        
        .Connection = CN.ConnectionString
        
        .sqlFields = "Barcode,Stock,SupplierPrice,DiscPercent,ExtDiscPercent,ExtDiscAmount,StockID"
        '.sqlTables = "Stocks"
        .sqlTables = "qry_Vendors_Stocks"

        .sqlSortOrder = "Stock ASC"
        .BoundField = "StockID"
        .PageBy = 25
        .DisplayCol = 2
        
        .setDropWindowSize 6800, 4000
        .TextReadOnly = True
        .SetDropDownTitle = "Stocks"
    End With
End Sub

Private Sub txtPrice_GotFocus()
    HLText txtPrice
End Sub

Private Sub txtSalesman_GotFocus()
    HLText txtSalesman
End Sub