frmshippingguideae.frm

Inventory control system

   Begin VB.Label Label11 
      BackStyle       =   0  'Transparent
      Caption         =   "Items"
      BeginProperty Font 
         Name            =   "Tahoma"
         Size            =   8.25
         Charset         =   0
         Weight          =   700
         Underline       =   0   'False
         Italic          =   0   'False
         Strikethrough   =   0   'False
      EndProperty
      ForeColor       =   &H80000014&
      Height          =   210
      Left            =   270
      TabIndex        =   25
      Top             =   2820
      Width           =   915
   End
   Begin VB.Shape Shape2 
      FillColor       =   &H00C0C0C0&
      FillStyle       =   0  'Solid
      Height          =   435
      Left            =   90
      Top             =   90
      Width           =   12285
   End
   Begin VB.Shape Shape3 
      BackColor       =   &H80000010&
      BackStyle       =   1  'Opaque
      BorderColor     =   &H80000010&
      Height          =   240
      Left            =   210
      Top             =   2820
      Width           =   12030
   End
End
Attribute VB_Name = "frmForwardersGuideAE"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Public State                As FormState 'Variable used to determine on how the form used
Public PK                   As Long 'Variable used to get what record is going to edit
Public CloseMe              As Boolean
Public ForCusAcc            As Boolean

Dim cIGross                 As Currency 'Gross Amount
Dim cIAmount                As Currency 'Current Invoice Amount
Dim cDAmount                As Currency 'Current Invoice Discount Amount
Dim cIRowCount              As Integer

Dim cCostPerPackage         As Double
Dim cTotalAmount            As Double
Dim cTotalTranspoCost       As Double

Dim HaveAction              As Boolean 'Variable used to detect if the user perform some action
Dim rs                      As New Recordset 'Main recordset for Invoice
Dim intQtyOld               As Integer 'Allowed value for receive qty
Dim dblLoose                As Double   'sum of all loose cargos




Private Sub btnUpdate_Click()
    Dim CurrRow As Integer

    CurrRow = getFlexPos(Grid, 10, Grid.TextMatrix(Grid.RowSel, 10))

    'validate the entry
    If txtRQty.Text = "0" Or txtValue.Text = "0.00" Or (cboClass.Text = "" And dcClass.Text = "Loose Cargo") Then Exit Sub
    If toNumber(txtOQty.Text) < toNumber(txtRQty.Text) Then
      MsgBox "Shipped Qty is greater than Ordered Qty.", vbExclamation
      Exit Sub
    End If
    
    
      




    'Add to grid
    With Grid
        .Row = CurrRow
        
        'If dcClass.Text = "Loose Cargo" Then
        '  dblLoose = dblLoose + GetFreight(nsdShippingCo.Text, cboClass.Text)
        '  txtAmount4.Text = toMoney(dblLoose)
        'Else
        '
        'End If
        
        
        .TextMatrix(CurrRow, 3) = txtRQty.Text
        .TextMatrix(CurrRow, 4) = dcUnit.Text
        .TextMatrix(CurrRow, 5) = toMoney(txtValue.Text)
        .TextMatrix(CurrRow, 6) = cboClass.Text
        .TextMatrix(CurrRow, 7) = toMoney(txtGross(0).Text)
        .TextMatrix(CurrRow, 8) = txtDisc.Text
        .TextMatrix(CurrRow, 9) = toMoney(txtNetAmount.Text)
        .TextMatrix(CurrRow, 10) = toNumber(txtFreight.Text)
        
        'compute total amount
        Dim i As Integer
        txtTotal.Text = 0
        For i = 1 To .Rows - 1
          txtTotal.Text = toMoney(txtTotal.Text) + toNumber(.TextMatrix(1, 9))
        Next
        
        'sum-up freight of loose cargo
        Dim cFreight As Double
        cFreight = 0
        'txtAmount4.Text = "0.00"
        For i = 1 To .Rows - 1
          cFreight = cFreight + toNumber(.TextMatrix(i, 10))
        Next
        txtAmount4.Text = toMoney(cFreight)
        
        
        'if item is alone
        If Grid.Rows = 2 And Grid.TextMatrix(1, 1) <> "" Then Grid.TextMatrix(1, 10) = 100
        
        'clear boxes
        txtOQty.Text = ""
        txtRQty.Text = ""
        dcUnit.Text = ""
        txtValue.Text = ""
        cboFindList cboClass, ""
        txtGross(0).Text = ""
        txtDisc.Text = ""
        txtNetAmount.Text = ""
        txtFreight.Text = ""
        
'        'Add the amount to current load amount
'        cIGross = cIGross + toNumber(txtGross(0).Text)
'        txtGross(2).Text = Format$(cIGross, "#,##0.00")
'        cIAmount = cIAmount + toNumber(txtNetAmount.Text)
'        cDAmount = cDAmount + toNumber(toNumber(txtDisc.Text) / 100) * (toNumber(toNumber(txtRQty.Text) * toNumber(txtValue.Text)))
'        txtDesc.Text = Format$(cDAmount, "#,##0.00")
'        txtNet.Text = Format$(cIAmount, "#,##0.00")
'        txtTaxBase.Text = toMoney(txtNet.Text / 1.12)
'        txtVat.Text = toMoney(txtNet.Text - txtTaxBase.Text)
'
'        txtAmount1_Change
        
        
        
        'Highlight the current row's column
        .ColSel = 10
        'Display a remove button
        'Grid_Click
        'Reset the entry fields
        ResetEntry
    End With
    
    btnUpdate.Enabled = False
End Sub

Private Sub btnRemove_Click()
    'Remove selected load product
    With Grid
        'Update grooss to current purchase amount
        cIGross = cIGross - toNumber(Grid.TextMatrix(.RowSel, 7))
        txtGross(2).Text = Format$(cIGross, "#,##0.00")
        'Update amount to current invoice amount
        cIAmount = cIAmount - toNumber(Grid.TextMatrix(.RowSel, 9))
        txtNet.Text = Format$(cIAmount, "#,##0.00")
        'Update discount to current invoice disc
        cDAmount = cDAmount - toNumber(toNumber(txtDisc.Text) / 100) * (toNumber(toNumber(Grid.TextMatrix(.RowSel, 4)) * toNumber(Grid.TextMatrix(.RowSel, 6))))
        txtDesc.Text = Format$(cDAmount, "#,##0.00")
        txtTaxBase.Text = toMoney(txtNet.Text / 1.12)
        txtVat.Text = toMoney(txtNet.Text - txtTaxBase.Text)

        '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 Function GetFreightOfLooseCargo(ByVal Supplier As String, ByVal Class As String) As Double
  Dim sql As String
  Dim rstemp As New ADODB.Recordset
  
  sql = "SELECT Cargo_Class.Freight " _
  & "FROM Shipping_Company LEFT JOIN Cargo_Class ON Shipping_Company.ShippingCompanyID = Cargo_Class.ShippingCompanyID " _
  & "WHERE (((Shipping_Company.ShippingCompany)='" & Replace(Supplier, "'", "''") & "') AND " _
  & "((Cargo_Class.Class)='" & Replace(Class, "'", "''") & "'))"
  rstemp.Open sql, CN, adOpenDynamic, adLockOptimistic
  
  If Not rstemp.EOF Then
    GetFreightOfLooseCargo = rstemp!freight
  Else
    GetFreightOfLooseCargo = 0
  End If
  
  
  rstemp.Close
  Set rstemp = Nothing
End Function

Private Sub cboClass_Click()
  txtFreight.Text = toMoney(GetFreightOfLooseCargo(nsdShippingCo.Text, cboClass.Text))
End Sub

Private Sub CmdReturn_Click()
  Dim RSDetails As New Recordset
  
  RSDetails.CursorLocation = adUseClient
  RSDetails.Open "SELECT * FROM qry_Forwarders_Detail WHERE ForwarderID=" & PK & " AND QtyOnDock > 0 ORDER BY Stock ASC", CN, adOpenStatic, adLockOptimistic
  If RSDetails.RecordCount > 0 Then
    With frmPOReceiveLocalAE
      .State = adStateAddMode
      .PK = PK
      .show vbModal
    End With
  Else
    MsgBox "All items are already delivered to VT.", vbInformation
  End If
End Sub

Private Sub dcClass_Click(Area As Integer)
  txtAmount4.Enabled = True
  cboClass.Enabled = True
  cboClass.Clear
  If dcClass.Text = "Loose Cargo" Then
    cboClass.AddItem "Bundle by Cases"
    cboClass.AddItem "Bundle by Bags"
    cboClass.AddItem "Sacks"
    txtFreight.Locked = True
    Grid.TextMatrix(0, 10) = "Freight Amt."
    Labels(7).Caption = "Freight Amt."
    
    'dblLoose = dblLoose + GetFreight(nsdShippingCo.Text, dcClass.Text)
    'txtAmount4.Text = toMoney(dblLoose)
  Else
    'If nsdShippingCo.Text = "" Then Exit Sub
    'txtAmount4.Text = toMoney(GetFreight(nsdShippingCo.Text, dcClass.Text))
    'txtFreight.Locked = False
  End If
End Sub


Private Function GetFreight(ByVal Company As String, ByVal Class As String) As Double
'  Dim sql As String
'  Dim rstemp As New ADODB.Recordset
'
'  sql = "SELECT Cargo_Class.Freight " _
'  & "FROM Shipping_Company INNER JOIN Cargo_Class ON Shipping_Company.ShippingCompanyID = Cargo_Class.ShippingCompanyID " _
'  & "WHERE (((Shipping_Company.ShippingCompany)='" & Replace(Company, "'", "''") & "') AND " _
'  & "((Cargo_Class.Class)='" & Replace(Class, "'", "''") & "'))"
'  rstemp.Open sql, CN, adOpenDynamic, adLockOptimistic
'  If Not rstemp.EOF Then
'    GetFreight = rstemp!freight
'  Else
'    GetFreight = 0
'  End If
'
'
'  rstemp.Close
'  Set rstemp = Nothing
End Function

Private Sub nsdLocal_Change()
  Dim sql As String
  Dim rstemp As New ADODB.Recordset
  
  sql = "SELECT Local_Forwarder.LocalForwarderID, Local_Forwarder_Account_Description.AccTitle, Local_Forwarder_Detail.Amount " _
  & "FROM Local_Forwarder_Account_Description RIGHT JOIN (Local_Forwarder LEFT JOIN Local_Forwarder_Detail ON Local_Forwarder.LocalForwarderID = Local_Forwarder_Detail.LocalForwarderID) ON Local_Forwarder_Account_Description.LocalForwarderAccTitleID = Local_Forwarder_Detail.AccountDescriptionID " _
  & "WHERE (((Local_Forwarder.LocalForwarder)='" & Replace(nsdLocal.Text, "'", "''") & "'))"
  
  rstemp.Open sql, CN, adOpenDynamic, adLockOptimistic
  
  txtAmount6.Text = "0.00"
  txtAmount7.Text = "0.00"
  
  Do While Not rstemp.EOF
    If rstemp!AccTitle = "Local Trucking" Then txtAmount6.Text = toMoney(rstemp!Amount)
    If rstemp!AccTitle = "Sidewalk Handling" Then txtAmount7.Text = toMoney(rstemp!Amount)
    
    rstemp.MoveNext
  Loop
  
  rstemp.Close
  Set rstemp = Nothing
End Sub

Private Sub txtAmount1_Change()
  txtTotalTranspoCost.Text = toMoney(toNumber(txtAmount1.Text) + toNumber(txtAmount2.Text) _
  + toNumber(txtAmount3.Text) + toNumber(txtAmount4.Text) + toNumber(txtAmount5.Text) _
  + toNumber(txtAmount6.Text) + toNumber(txtAmount7.Text))
End Sub

Private Sub txtAmount2_Change()
txtAmount1_Change
End Sub

Private Sub txtAmount3_Change()
txtAmount1_Change
End Sub

Private Sub txtAmount4_Change()
'txtAmount1_Change
End Sub

Private Sub txtAmount5_Change()
txtAmount1_Change
End Sub

Private Sub txtAmount6_Change()
txtAmount1_Change
End Sub

Private Sub txtAmount7_Change()
txtAmount1_Change
End Sub

Private Sub txtCostPerPackage_Click()
'  MsgBox Grid.Row
End Sub

Private Sub txtdisc_Change()
  If Trim(txtDisc.Text) = "" Then txtDisc.Text = 0
  
  txtRQty_Change
End Sub

Private Sub txtdisc_Click()
    txtQty_Change
End Sub

Private Sub CmdCancel_Click()
    Unload Me
End Sub

Private Sub txtDisc_GotFocus()
    HLText txtDisc
End Sub

Private Sub txtdisc_Validate(Cancel As Boolean)
    txtDisc.Text = toNumber(txtDisc.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 cmdSave_Click()
    'Verify the entries
    
    If Trim(nsdShippingCo.Text) = "" Then
        MsgBox "Please enter shipping company before saving.", vbExclamation
        Exit Sub
    End If
    If (dcClass.Text = "A" Or dcClass.Text = "B" Or dcClass.Text = "C") And Trim(txtVanNo.Text) = "" Then
       MsgBox "Please enter Van No. before saving.", vbExclamation
        Exit Sub
    End If
   
    If cIRowCount < 1 Then
        MsgBox "Please enter item to return before saving this record.", vbExclamation
        Exit Sub
    End If
    
    'check if freight allocation is 100 percent
    Dim i As Integer
    Dim j As Double
    j = 0
    For i = 1 To Grid.Rows - 1
      j = j + toNumber(Grid.TextMatrix(i, 10))
    Next
    
    If (Grid.Rows > 2) And (j <> 100) Then
      MsgBox "System detects that your freight allocation has a problem, please make correction before saving.", vbExclamation
      Exit Sub
    End If
    '----
    
    
    
    
   
    If MsgBox("This save the record. Do you want to proceed?", vbQuestion + vbYesNo) = vbNo Then Exit Sub

    'Connection for Forwarde