frmpurchaseorderreceiveae.frm

Inventory control system

                RSStockCard!Incoming = RSStockCard!Incoming - toNumber(.TextMatrix(c, 3))
                RSStockCard!Pieces1 = toNumber(.TextMatrix(c, 3)) 'RSStockCard!Pieces1 + toNumber(.TextMatrix(c, 3))

                RSStockCard.Update
                '-----------------

                RSStockUnit.Filter = "StockID = " & toNumber(.TextMatrix(c, 12)) & " AND UnitID = " & getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
                
                RSStockUnit!Incoming = RSStockUnit!Incoming - toNumber(.TextMatrix(c, 3))
                RSStockUnit!Onhand = RSStockUnit!Onhand + toNumber(.TextMatrix(c, 3))
                
                RSStockUnit.Update
                '-----------------
                                    
'                'add qty received in Purchase Order Details
'                RSPODetails.Find "[StockID] = " & toNumber(.TextMatrix(c, 12)), , adSearchForward, 1
'                RSPODetails!QtyReceived = toNumber(RSPODetails!QtyReceived) + toNumber(.TextMatrix(c, 3))
'
'                RSPODetails.Update
'                '-----------------
            
                RSLandedCost.Open "SELECT * FROM Landed_Cost WHERE StockID=" & toNumber(.TextMatrix(c, 12)) & " ORDER BY LandedCostID DESC", CN, adOpenStatic, adLockOptimistic
                
                If RSLandedCost.RecordCount > 0 Then
                    If RSLandedCost!SupplierPrice <> toNumber(.TextMatrix(c, 5)) Then
AddLandedCost:
                        RSLandedCost.AddNew
        
                        RSLandedCost!VendorID = getValueAt("SELECT VendorID,Company FROM Vendors WHERE Company='" & nsdVendor.Text & "'", "VendorID")
                        RSLandedCost!RefNo = txtDRNo.Text
                        RSLandedCost!StockID = .TextMatrix(c, 12)
                        RSLandedCost!Unit = getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
                        RSLandedCost!Date = Date
                        RSLandedCost!SupplierPrice = toNumber(.TextMatrix(c, 5))
                        RSLandedCost!Discount = toNumber(.TextMatrix(c, 7)) / 100
                        RSLandedCost!ExtDiscPercent = toNumber(.TextMatrix(c, 8)) / 100
                        RSLandedCost!ExtDiscAmount = toNumber(.TextMatrix(c, 9))
                        
                        RSLandedCost.Update
                    End If
                ElseIf RSLandedCost.RecordCount = 0 Then
                    GoSub AddLandedCost
                End If
                
                RSLandedCost.Close
            End If
        Next c
    End With

    'Clear variables
    c = 0
    Set RSDetails = Nothing

    CN.CommitTrans

    blnSave = True

    HaveAction = True
    Screen.MousePointer = vbDefault

    If State = adStateAddMode Or State = adStateEditMode Then
        MsgBox "New record has been successfully saved.", vbInformation
        Unload Me
    End If

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

Private Sub CmdTasks_Click()
    PopupMenu mnu_Tasks
End Sub

Private Sub Form_Activate()
    On Error Resume Next
    If CloseMe = True Then
        Unload Me
    Else
        txtDRNo.SetFocus
    End If
End Sub

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

Private Sub Form_Load()
    InitGrid

    CN.BeginTrans

    bind_dc "SELECT * FROM Unit", "Unit", dcUnit, "UnitID", True
    bind_dc "SELECT * FROM Warehouses", "Warehouse", dcWarehouse, "WarehouseID", True

    Screen.MousePointer = vbHourglass
    
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        'Set the recordset
        RS.Open "SELECT * FROM qry_Purchase_Order WHERE POID=" & PK, CN, adOpenStatic, adLockOptimistic
        dtpDRDate.Value = Date
        dtpDeliveryDate.Value = Date
        mnu_History.Enabled = False
                   
        DisplayForAdding
    ElseIf State = adStateEditMode Then
        'Set the recordset
        RS.Open "SELECT * FROM qry_Purchase_Order_Receive_Local WHERE InvoiceID=" & InvoicePK, CN, adOpenStatic, adLockOptimistic
        
        dtpDRDate.Value = Date
        dtpDeliveryDate.Value = Date
        mnu_History.Enabled = False
                   
        DisplayForEditing
    Else
        'Set the recordset
        RS.Open "SELECT * FROM qry_Purchase_Order_Receive_Local WHERE InvoiceID=" & InvoicePK, CN, adOpenStatic, adLockOptimistic
        
        cmdCancel.Caption = "Close"
        DisplayForViewing
    End If
    
    Screen.MousePointer = vbDefault
    
    'Initialize Graphics
    With MAIN
        'cmdGenerate.Picture = .i16x16.ListImages(14).Picture
        'cmdNew.Picture = .i16x16.ListImages(10).Picture
        'cmdReset.Picture = .i16x16.ListImages(15).Picture
    End With
End Sub

'Procedure used to generate PK
Private Sub GeneratePK()
    PK = getIndex("Local_Purchase")
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 13
        .ColSel = 11
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 0
        .ColWidth(2) = 2430
        .ColWidth(3) = 465
        .ColWidth(4) = 1000
        .ColWidth(5) = 1005
        .ColWidth(6) = 900
        .ColWidth(7) = 690
        .ColWidth(8) = 995
        .ColWidth(9) = 1150
        .ColWidth(10) = 1000
        .ColWidth(11) = 1000
        .ColWidth(12) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Barcode"
        .TextMatrix(0, 2) = "Item"
        .TextMatrix(0, 3) = "Qty"
        .TextMatrix(0, 4) = "Unit"
        .TextMatrix(0, 5) = "Price" 'Supplier Price
        .TextMatrix(0, 6) = "Gross"
        .TextMatrix(0, 7) = "Disc(%)"
        .TextMatrix(0, 8) = "Ext. Disc(%)"
        .TextMatrix(0, 9) = "Ext. Disc(Amt)"
        .TextMatrix(0, 10) = "Net Amount"
        .TextMatrix(0, 11) = "Warehouse"
        .TextMatrix(0, 12) = "Stock ID"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
        .ColAlignment(3) = vbLeftJustify
        .ColAlignment(4) = vbRightJustify
        .ColAlignment(5) = vbLeftJustify
        .ColAlignment(6) = vbRightJustify
        .ColAlignment(7) = vbRightJustify
        .ColAlignment(8) = vbRightJustify
        .ColAlignment(9) = vbRightJustify
        .ColAlignment(10) = vbRightJustify
    End With
End Sub

Private Sub ResetEntry()
    txtStock.Text = ""
    txtQty.Text = "0"
    txtPrice.Tag = 0
    txtPrice.Text = "0.00"
    txtDiscPercent.Text = "0"
    txtExtDiscPerc.Text = "0"
    txtExtDiscAmt.Text = "0"
End Sub

Private Sub Form_Unload(Cancel As Integer)
    'If HaveAction = True Then
    '    frmLocalPurchaseReturn.RefreshRecords
    'End If
    
    Set frmPOReceiveLocalAE = Nothing
End Sub

Private Sub Grid_Click()
    With Grid
        txtStock.Text = .TextMatrix(.RowSel, 2)
        txtStock.Tag = .TextMatrix(.RowSel, 12) 'Create tag to get the StockID
        intQtyOld = IIf(.TextMatrix(.RowSel, 3) = "", 0, .TextMatrix(.RowSel, 3))
        txtQty = .TextMatrix(.RowSel, 3)
        dcUnit.Text = .TextMatrix(.RowSel, 4)
        txtPrice = toMoney(.TextMatrix(.RowSel, 5))
        txtGross(1) = toMoney(.TextMatrix(.RowSel, 6))
        txtDiscPercent.Text = toMoney(.TextMatrix(.RowSel, 7))
        txtExtDiscPerc.Text = toMoney(.TextMatrix(.RowSel, 8))
        txtExtDiscAmt.Text = toMoney(.TextMatrix(.RowSel, 9))
        txtNetAmount = toMoney(.TextMatrix(.RowSel, 10))
        
        If State = adStateViewMode Then Exit Sub
        If Grid.Rows = 2 And Grid.TextMatrix(1, 12) = "" 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 Grid_Scroll()
    btnRemove.Visible = False
End Sub

Private Sub Grid_SelChange()
    Grid_Click
End Sub

Private Sub mnu_History_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 mnu_ReturnItems_Click()
    'Display the details
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Purchase_Order_Receive_Local_Detail WHERE InvoiceID=" & InvoicePK & " AND QtyDue > 0 ORDER BY Stock ASC", CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        With frmPOReturnAE
            .State = adStateAddMode
            .PK = InvoicePK
            .show vbModal
        End With
    Else
        MsgBox "All items are already returned.", vbInformation
    End If
End Sub

Private Sub mnu_Vat_Click()
  If mnu_Vat.Caption = "Show VAT && Taxbase" Then
    Label5.Visible = True
    Label8.Visible = True
    txtTaxBase.Visible = True
    txtVat.Visible = True
    mnu_Vat.Caption = "Hide VAT && Taxbase"
  Else
    Label5.Visible = False
    Label8.Visible = False
    txtTaxBase.Visible = False
    txtVat.Visible = False
    mnu_Vat.Caption = "Show VAT && Taxbase"
  End If
End Sub

Private Sub txtDeliveryNo_GotFocus()
    HLText txtDeliveryNo
End Sub

Private Sub txtDesc_GotFocus()
    HLText txtDesc
End Sub

Private Sub txtDiscPercent_Change()
    ComputeGrossNet
End Sub

Private Sub txtDiscPercent_GotFocus()
    HLText txtDiscPercent
End Sub

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

Private Sub txtDRNo_GotFocus()
    HLText txtDRNo
End Sub

Private Sub txtExtDiscAmt_Change()
    ComputeGrossNet
End Sub

Private Sub txtExtDiscAmt_GotFocus()
    HLText txtExtDiscAmt
End Sub

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

Private Sub txtExtDiscAmt_Validate(Cancel As Boolean)
    txtExtDiscAmt.Text = toMoney(toNumber(txtExtDiscAmt.Text))
End Sub

Private Sub txtExtDiscPerc_Change()
    ComputeGrossNet
End Sub

Private Sub txtExtDiscPerc_GotFocus()
    HLText txtExtDiscPerc
End Sub

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

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

Private Sub txtQty_LostFocus()
    Dim intQtyDue As Integer
      
    intQtyDue = getValueAt("SELECT QtyDue FROM qry_Purchase_Order_Detail WHERE POID=" & PK, "QtyDue")
    If txtQty.Text > (intQtyDue + intQtyOld) Then
        MsgBox "Overdelivery for " & txtStock.Text & ".", vbInformation
        txtQty.Text = intQtyOld
    End If
End Sub

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

Private Sub txtPrice_Change()
'    txtQty_Change
    ComputeGrossNet
End Sub

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