frmforwardersreceiveae.frm

Inventory control system

        ![Class] = cboClass.ListIndex
        ![LocalForwarderID] = IIf(nsdLocal.BoundText = "", nsdLocal.Tag, nsdLocal.BoundText)
        ![DeliveryDate] = txtDeliveryDate.Text
        ![ReceiptDate] = txtReceiptDate.Text
        ![Ref] = cboRef.Text
        ![RefNo] = txtRefNo.Text
'        ![DRNo] = txtDRNo.Text
'        ![BLNo] = txtBLNo.Text
'        ![TruckNo] = txtTruckNo.Text
'        ![VanNo] = txtVanNo.Text
'        ![VoyageNo] = txtVoyageNo.Text
        ![Status] = IIf(cboStatus.Text = "Received", True, False)
        ![Notes] = txtNotes.Text
        
        ![Gross] = toNumber(txtGross(2).Text)
        ![Discount] = txtDesc.Text
        ![TaxBase] = toNumber(txtTaxBase.Text)
        ![Vat] = toNumber(txtVat.Text)
        ![NetAmount] = toNumber(txtNet.Text)
    
        ![DateModified] = Now
        ![LastUserFK] = CurrUser.USER_PK
                
        .Update
    End With
  
    'Connection for Forwarders_Detail
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM Forwarders_Receive_Detail WHERE ForwarderReceiveID=" & ForwarderReceivePK, CN, adOpenStatic, adLockOptimistic
          
    'Save to stock card
    Dim RSStockCard As New Recordset

    RSStockCard.CursorLocation = adUseClient
    RSStockCard.Open "SELECT * FROM Stock_Card", CN, adOpenStatic, adLockOptimistic
          
    'Add qty ordered to qty onhand
    Dim RSStockUnit As New Recordset

    RSStockUnit.CursorLocation = adUseClient
    RSStockUnit.Open "SELECT * From Stock_Unit", CN, adOpenStatic, adLockOptimistic
          
    'Save to Purchase Order Details
'    Dim RSForwarderDetails As New Recordset
'
'    RSForwarderDetails.CursorLocation = adUseClient
'    RSForwarderDetails.Open "SELECT * From Forwarders_Detail Where ForwarderID = " & PK, CN, adOpenStatic, adLockOptimistic
    
    'Save to Landed Cost table
    Dim RSLandedCost As New Recordset

    RSLandedCost.CursorLocation = adUseClient
    
    With Grid
        
        'Save the details of the records to Purchase_Order_Receive_Local_Detail
        For c = 1 To cIRowCount
            .Row = c
            If State = adStateAddMode Or State = adStatePopupMode Then
AddNew:
            
                RSDetails.AddNew
    
                RSDetails![ForwarderReceiveID] = ForwarderReceivePK
                RSDetails![StockID] = toNumber(.TextMatrix(c, 13))
                RSDetails![Qty] = toNumber(.TextMatrix(c, 3))
                RSDetails![Unit] = getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
                RSDetails![Price] = toNumber(.TextMatrix(c, 5))
                RSDetails![DiscPercent] = toNumber(.TextMatrix(c, 7)) / 100
                RSDetails![ExtDiscPercent] = toNumber(.TextMatrix(c, 8)) / 100
                RSDetails![ExtDiscAmt] = toNumber(.TextMatrix(c, 9))
                RSDetails![WarehouseID] = getValueAt("SELECT WarehouseID,Warehouse FROM Warehouses WHERE Warehouse='" & .TextMatrix(c, 12) & "'", "WarehouseID")
    
                RSDetails.Update
            ElseIf State = adStateEditMode Then
                RSDetails.Filter = "StockID = " & toNumber(.TextMatrix(c, 13))
            
                If RSDetails.RecordCount = 0 Then GoTo AddNew

                RSDetails![Qty] = toNumber(.TextMatrix(c, 3))
                RSDetails![Unit] = getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
                RSDetails![Price] = toNumber(.TextMatrix(c, 5))
                RSDetails![DiscPercent] = toNumber(.TextMatrix(c, 7)) / 100
                RSDetails![ExtDiscPercent] = toNumber(.TextMatrix(c, 8)) / 100
                RSDetails![ExtDiscAmt] = toNumber(.TextMatrix(c, 9))
                RSDetails![WarehouseID] = getValueAt("SELECT WarehouseID,Warehouse FROM Warehouses WHERE Warehouse='" & .TextMatrix(c, 12) & "'", "WarehouseID")
    
                RSDetails.Update
            End If
            
            If State = adStateAddMode Then
                RSStockCard.Filter = "StockID = " & toNumber(.TextMatrix(c, 13)) & " AND RefNo1 = '" & txtRefNo.Text & "'"
                
                If RSStockCard.RecordCount = 0 Then
                    'Add to stock card
                    RSStockCard.AddNew
    
                    RSStockCard!Type = "PR"
                    RSStockCard!UnitID = getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
                    RSStockCard!RefNo1 = txtRefNo.Text
                    RSStockCard!Incoming = toNumber(.TextMatrix(c, 3))
                    RSStockCard!Cost = toNumber(.TextMatrix(c, 5))
                    RSStockCard!StockID = toNumber(.TextMatrix(c, 13))
                Else
                    RSStockCard!Incoming = RSStockCard!Incoming + toNumber(.TextMatrix(c, 3))
                End If
                
                RSStockCard.Update
                '-----------------
                
                'Deduct pending and add incoming in Stock Unit
                RSStockUnit.Filter = "StockID = " & toNumber(.TextMatrix(c, 13)) & " AND UnitID = " & getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")

                RSStockUnit!Pending = RSStockUnit!Pending - toNumber(.TextMatrix(c, 3))
                RSStockUnit!Incoming = RSStockUnit!Incoming + toNumber(.TextMatrix(c, 3))
                RSStockUnit.Update
            ElseIf cboStatus.Text = "On Hold" And State = adStateEditMode Then
                RSStockCard.Filter = "StockID = " & toNumber(.TextMatrix(c, 13)) & " AND RefNo1 = '" & txtRefNo.Text & "'"
                RSStockUnit.Filter = "StockID = " & toNumber(.TextMatrix(c, 13)) & " AND UnitID = " & getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
               
                'Restore Pending from incoming
                RSStockUnit!Pending = RSStockUnit!Pending + RSStockCard!Incoming
                RSStockUnit!Incoming = RSStockUnit!Incoming - RSStockCard!Incoming
                
                RSStockUnit.Update
                '-----------------
                
                'Update Incoming, Overight Incoming encoded in add mode
                RSStockCard!Incoming = toNumber(.TextMatrix(c, 3))
    
                RSStockCard.Update
                '-----------------

                'Deduct pending and add incoming in Stock Unit
                RSStockUnit!Pending = RSStockUnit!Pending - toNumber(.TextMatrix(c, 3))
                RSStockUnit!Incoming = RSStockUnit!Incoming + toNumber(.TextMatrix(c, 3))
                
                RSStockUnit.Update
            End If
            
            If cboStatus.Text = "Received" Then
                RSStockCard.Filter = "StockID = " & toNumber(.TextMatrix(c, 13)) & " AND RefNo1 = '" & txtRefNo.Text & "'"

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

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

                RSStockUnit.Filter = "StockID = " & toNumber(.TextMatrix(c, 13)) & " 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 Forwarders Details
'                RSForwarderDetails.Find "[StockID] = " & toNumber(.TextMatrix(c, 13)), , adSearchForward, 1
'                RSForwarderDetails!QtyReceived = toNumber(RSForwarderDetails!QtyReceived) + toNumber(.TextMatrix(c, 3))
'
'                RSForwarderDetails.Update
                
                RSLandedCost.Open "SELECT * FROM Landed_Cost WHERE StockID=" & toNumber(.TextMatrix(c, 13)) & " ORDER BY LandedCostID DESC", CN, adOpenStatic, adLockOptimistic
                
                If RSLandedCost.RecordCount > 0 Then
                    If toNumber(RSLandedCost!SupplierPrice) <> toNumber(.TextMatrix(c, 5)) Then
AddLandedCost:
                        'Save to landed cost table
                        RSLandedCost.AddNew
        
                        RSLandedCost!VendorID = intVendorID
                        RSLandedCost!RefNo = txtRefNo.Text
                        RSLandedCost!StockID = .TextMatrix(c, 13)
                        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!Freight = toNumber(.TextMatrix(c, 11))
                        
                        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
        txtShippingGuideNo.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
    InitNSD
        
    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_Forwarders WHERE ForwarderID=" & PK, CN, adOpenStatic, adLockOptimistic
        mnu_Return.Enabled = False
        
        CN.BeginTrans
                   
        DisplayForAdding
    ElseIf State = adStateEditMode Then
        'Set the recordset
        RS.Open "SELECT * FROM qry_Forwarders_Receive WHERE ForwarderReceiveID=" & ForwarderReceivePK, CN, adOpenStatic, adLockOptimistic
        
        mnu_Return.Enabled = False

        CN.BeginTrans

        DisplayForEditing
    Else
        'Set the recordset
        RS.Open "SELECT * FROM qry_Forwarders_Receive WHERE ForwarderReceiveID=" & ForwarderReceivePK, 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

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 frmForwardersReceiveAE = Nothing
End Sub

Private Sub Grid_Click()
    If State = adStateViewMode Then Exit Sub
    
    With Grid
        txtStock.Text = .TextMatrix(.RowSel, 2)
        txtStock.Tag = .TextMatrix(.RowSel, 13) '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.Text = toMoney(.TextMatrix(.RowSel, 10))
        dcWarehouse.Text = .TextMatrix(.RowSel, 12)
        
        If State = adStateViewMode Then Exit Sub
        If Grid.Rows = 2 And Grid.TextMatrix(1, 13) = "" 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_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