frmporeceiveoutoftownae.frm

Inventory control system

    'Connection for Purchase_Order_Receive
    Dim RSReceive As New Recordset

    RSReceive.CursorLocation = adUseClient
    RSReceive.Open "SELECT * FROM Forwarders WHERE ForwarderID=" & ForwarderPK, CN, adOpenStatic, adLockOptimistic

    Screen.MousePointer = vbHourglass

    Dim c As Integer

    On Error GoTo err

    CN.BeginTrans

    DeleteItems
    
    'Save the record
    With RSReceive
        If State = adStateAddMode Or State = adStatePopupMode Then
    
            .AddNew
            
            ForwarderPK = getIndex("Forwarders")
            ![ForwarderID] = ForwarderPK
            ![POID] = PK
            
            ![DateAdded] = Now
            ![AddedByFK] = CurrUser.USER_PK
        End If
        
        ![ShippingCompanyID] = IIf(nsdShippingCo.BoundText = "", nsdShippingCo.Tag, nsdShippingCo.BoundText)
        ![ShippingGuideNo] = txtShippingGuideNo.Text
        ![Ship] = txtShip.Text
        ![Class] = cboClass.ListIndex
        ![LocalForwarderID] = IIf(nsdLocal.BoundText = "", nsdLocal.Tag, nsdLocal.BoundText)
        ![ArrivalDate] = dtpArrivalDate.Value
        ![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 Transportation_Cost
    Dim RSTranspo As New Recordset

    RSTranspo.CursorLocation = adUseClient
    RSTranspo.Open "SELECT * FROM Transportation_Cost WHERE ForwarderID=" & ForwarderPK, CN, adOpenStatic, adLockOptimistic
   
    With RSTranspo
        If State = adStateAddMode Or State = adStatePopupMode Then
    
            .AddNew
            
            ![ForwarderID] = ForwarderPK
        End If

        ![MlaTruckingDate] = dtp(1).Value
        ![MlaTruckingOR] = txtOR(1).Text
        ![MlaTruckingAmount] = txtAmount(1).Text
        
        ![MlaArrastreDate] = dtp(2).Value
        ![MlaArrastreOR] = txtOR(2).Text
        ![MlaArrastreAmount] = txtAmount(2).Text
        
        ![MlaWfgFeeDate] = dtp(3).Value
        ![MlaWfgFeeOR] = txtOR(3).Text
        ![MlaWfgFeeAmount] = txtAmount(3).Text
        
        ![FreightDate] = dtp(4).Value
        ![FreightOR] = txtOR(4).Text
        ![FreightAmount] = txtAmount(4).Text
        
        ![LocalArrastreDate] = dtp(5).Value
        ![LocalArrastreOR] = txtOR(5).Text
        ![LocalArrastreAmount] = txtAmount(5).Text
        
        ![LocalTruckingDate] = dtp(6).Value
        ![LocalTruckingOR] = txtOR(6).Text
        ![LocalTruckingAmount] = txtAmount(6).Text
        
        ![SidewalkHandlingDate] = dtp(7).Value
        ![SidewalkHandlingOR] = txtOR(7).Text
        ![SidewalkHandlingAmount] = txtAmount(7).Text
                
        .Update
    End With
    
    'Connection for Forwarders_Detail
    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM Forwarders_Detail WHERE ForwarderID=" & ForwarderPK, CN, adOpenStatic, adLockOptimistic
          
    'Save to Purchase Order Details
    Dim RSPODetails As New Recordset

    'may be this is not needed since items received in forwarders guide is not added in inventory
    RSPODetails.CursorLocation = adUseClient
    RSPODetails.Open "SELECT * From Purchase_Order_Detail Where POID = " & PK, CN, adOpenStatic, adLockOptimistic
    
    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![ForwarderID] = ForwarderPK
                RSDetails![StockID] = toNumber(.TextMatrix(c, 15))
                RSDetails![Qty] = toNumber(.TextMatrix(c, 3))
                RSDetails![unit] = getValueAt("SELECT UnitID,Unit FROM Unit WHERE Unit='" & .TextMatrix(c, 4) & "'", "UnitID")
                RSDetails![LooseCargo] = toNumber(.TextMatrix(c, 5))
                RSDetails![LocalArrastre] = toNumber(.TextMatrix(c, 6))
                RSDetails![Price] = toNumber(.TextMatrix(c, 7))
                RSDetails![DiscPercent] = toNumber(.TextMatrix(c, 9)) / 100
                RSDetails![ExtDiscPercent] = toNumber(.TextMatrix(c, 10)) / 100
                RSDetails![ExtDiscAmt] = toNumber(.TextMatrix(c, 11))
                RSDetails![FreightPercent] = toNumber(.TextMatrix(c, 13))
                RSDetails![CostPerPackage] = toNumber(.TextMatrix(c, 14))
    
                RSDetails.Update
            ElseIf State = adStateEditMode Then
                RSDetails.Filter = "StockID = " & toNumber(.TextMatrix(c, 15))
            
                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![LooseCargo] = toNumber(.TextMatrix(c, 5))
                RSDetails![LocalArrastre] = toNumber(.TextMatrix(c, 6))
                RSDetails![Price] = toNumber(.TextMatrix(c, 7))
                RSDetails![DiscPercent] = toNumber(.TextMatrix(c, 9)) / 100
                RSDetails![ExtDiscPercent] = toNumber(.TextMatrix(c, 10)) / 100
                RSDetails![ExtDiscAmt] = toNumber(.TextMatrix(c, 11))
                RSDetails![FreightPercent] = toNumber(.TextMatrix(c, 13))
                RSDetails![CostPerPackage] = toNumber(.TextMatrix(c, 14))
    
                RSDetails.Update
                
            End If
            
            If cboStatus.Text = "Received" Then
                'add qty received in Purchase Order Details

                RSPODetails.Find "[StockID] = " & toNumber(.TextMatrix(c, 15)), , adSearchForward, 1
                RSPODetails!QtyReceived = toNumber(RSPODetails!QtyReceived) + toNumber(.TextMatrix(c, 3))
                
                RSPODetails.Update
            End If
        Next c
    End With

    'Clear variables
    c = 0
    Set RSDetails = Nothing

    CN.CommitTrans

    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:
    CN.RollbackTrans
    prompt_err err, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

Private Sub CmdSubdivide_Click()
    Dim TotalRows As Integer
    Dim CurrRow As Integer
    
    'Count total number of rows in grid
    TotalRows = Grid.Rows - 1
    
    Do While CurrRow < TotalRows
        With Grid
        'Increase the record count
        CurrRow = CurrRow + 1
        
        .TextMatrix(CurrRow, 13) = 100 / TotalRows
        End With
    Loop
    ReCalcCPP
End Sub

Private Sub cmdUsrHistory_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 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

    Screen.MousePointer = vbHourglass
    
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        'Set the recordset
        rs.Open "SELECT * FROM qry_Forwarders WHERE POID=" & PK, CN, adOpenStatic, adLockOptimistic
        dtpArrivalDate.Value = Date
        Caption = "Create New Entry"
        cmdUsrHistory.Enabled = False
                   
        DisplayForAdding
    ElseIf State = adStateEditMode Then
        'Set the recordset
        rs.Open "SELECT * FROM qry_ WHERE ForwarderID=" & ForwarderPK, CN, adOpenStatic, adLockOptimistic
        
        dtpArrivalDate.Value = Date
        Caption = "Edit Entry"
        cmdUsrHistory.Enabled = False
                   
        DisplayForEditing
    Else
        'Set the recordset
        rs.Open "SELECT * FROM qry_Forwarders WHERE ForwarderID=" & ForwarderPK, 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

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

Private Sub Grid_Click()
    If State = adStateViewMode Then Exit Sub
    
    With Grid
        txtStock.Text = .TextMatrix(.RowSel, 2)
        txtStock.Tag = .TextMatrix(.RowSel, 15) 'Create tag to get the StockID
        txtQty = .TextMatrix(.RowSel, 3)
        dcUnit.Text = .TextMatrix(.RowSel, 4)
        txtLooseCargo.Text = .TextMatrix(.RowSel, 5)
        txtLocalArrastre.Text = .TextMatrix(.RowSel, 6)
        txtPrice = toMoney(.TextMatrix(.RowSel, 7))
        txtGross(1) = toMoney(.TextMatrix(.RowSel, 8))
        txtDiscPercent.Text = toMoney(.TextMatrix(.RowSel, 9))
        txtExtDiscPerc.Text = toMoney(.TextMatrix(.RowSel, 10))
        txtExtDiscAmt.Text = toMoney(.TextMatrix(.RowSel, 11))
        txtNetAmount = toMoney(.TextMatrix(.RowSel, 12))
        txtFreight = .TextMatrix(.RowSel, 13)
        
        If State = adStateViewMode Then Exit Sub
        If Grid.Rows = 2 And Grid.TextMatrix(1, 15) = "" 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 nsdLocal_Change()
On Error GoTo err

    Dim intLocalForwarder As Integer
    
    If nsdLocal.BoundText = "" Then
        intLocalForwarder = nsdLocal.Tag
    Else
        intLocalForwarder = nsdLocal.BoundText
    End If
    
    cLocalTrucking = getValueAt("SELECT Amount FROM qry_Local_Forwarder WHERE LocalForwarderID = " & intLocalForwarder & " AND LocalForwarderAccTitleID = " & 1, "Amount")
    txtAmount(6).Text = toMoney(cLocalTrucking)
    cSidewalkHandling = getValueAt("SELECT Amount FROM qry_Local_Forwarder WHERE LocalForwarderID = " & intLocalForwarder & " AND LocalForwarderAccTitleID = " & 2, "Amount")
    txtAmount(7).Text = toMoney(cSidewalkHandling)

    Exit Sub
err:
    prompt_err err, Name, "nsdLocal_Change"
    Screen.MousePointer = vbDefault
End Sub

Private Sub nsdShippingCo_Change()
'    bind_dc "SELECT * FROM qry_Cargo_Class WHERE ShippingCompanyID=" & toNumber(nsdShippingCo.BoundText), "Cargo", dcClass, "CargoID", False 'False so that it will give an empty value
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