frmlocalforwarderae.frm

Inventory control system

        cIRowCount = cIRowCount - 1
        
        If .Rows = 2 Then Grid.Rows = Grid.Rows + 1
        .RemoveItem (.RowSel)
    End With

    btnRemove.Visible = False
    Grid_Click
End Sub

Private Sub cmdAdd_Click()
    If dcTitle.Text = "" Or txtAmount.Text = "" Then dcTitle.SetFocus: Exit Sub

    Dim CurrRow As Integer
    Dim intDetailID As Integer
    
    CurrRow = getFlexPos(Grid, 3, dcTitle.BoundText)
    intDetailID = dcTitle.BoundText

    'Add to grid
    With Grid
        If CurrRow < 0 Then
            'Perform if the record is not exist
            If .Rows = 2 And .TextMatrix(1, 3) = "" Then
                .TextMatrix(1, 1) = dcTitle.Text
                .TextMatrix(1, 2) = txtAmount.Text
                .TextMatrix(1, 3) = intDetailID
            Else
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1, 1) = dcTitle.Text
                .TextMatrix(.Rows - 1, 2) = txtAmount.Text
                .TextMatrix(.Rows - 1, 3) = intDetailID

                .Row = .Rows - 1
            End If
            'Increase the record count
            cIRowCount = cIRowCount + 1
        Else
            If MsgBox("Item already added. Do you want to replace it?", vbQuestion + vbYesNo) = vbYes Then
                .Row = CurrRow
                
                .TextMatrix(CurrRow, 1) = dcTitle.Text
                .TextMatrix(CurrRow, 2) = txtAmount.Text
                .TextMatrix(CurrRow, 3) = intDetailID
            Else
                Exit Sub
            End If
        End If
        
        'Highlight the current row's column
        .ColSel = 3
        'Display a remove button
        Grid_Click
    End With
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub ResetFields()
    clearText Me
    
    txtEntry(0).SetFocus
End Sub

Private Sub cmdSave_Click()
On Error GoTo erR

    'check for blank product
    If Trim(txtEntry(0).Text) = "" Then
        MsgBox "Company should not be empty.", vbExclamation
        Exit Sub
    End If
    
    If cIRowCount < 1 Then
        MsgBox "Please provide at least one title for local services.", vbExclamation
        Exit Sub
    End If
    
    CN.BeginTrans
    If State = adStateAddMode Or State = adStatePopupMode Then
        rs.AddNew
        rs.Fields("LocalForwarderID") = PK
        rs.Fields("addedbyfk") = CurrUser.USER_PK
    Else
        rs.Fields("datemodified") = Now
        rs.Fields("lastuserfk") = CurrUser.USER_PK
    End If
    
    With rs
        .Fields("LocalForwarder") = txtEntry(0).Text
        .Fields("Area") = txtEntry(1).Text
        .Fields("City") = txtEntry(2).Text
        .Fields("Telephone") = txtEntry(3).Text
        .Fields("Mobile") = txtEntry(4).Text
        
        .Update
    End With
    
    Dim RSDetails As New Recordset
       
    'save stockunit
    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM qry_Local_Forwarder_Details WHERE LocalForwarderID = " & PK, CN, adOpenStatic, adLockOptimistic
  
    DeleteItems
    
    Dim c As Integer
    
    With Grid
        'Save the details of the records
        For c = 1 To cIRowCount
            .Row = c
            If State = adStateAddMode Or State = adStatePopupMode Then
AddNew:
                'Add qty received in Local Purchase Details
                RSDetails.AddNew

                RSDetails![LocalForwarderID] = PK
                RSDetails![AccountDescriptionID] = toNumber(.TextMatrix(c, 3))
                RSDetails![Amount] = toNumber(.TextMatrix(c, 2))

                RSDetails.Update
            ElseIf State = adStateEditMode Then
                RSDetails.Filter = "AccountDescriptionID = " & toNumber(.TextMatrix(c, 3))
            
                If RSDetails.RecordCount = 0 Then GoTo AddNew

                RSDetails![LocalForwarderID] = PK
                RSDetails![AccountDescriptionID] = toNumber(.TextMatrix(c, 3))
                RSDetails![Amount] = toNumber(.TextMatrix(c, 2))

                RSDetails.Update
            End If

        Next c
    End With

    'Clear variables
    c = 0
    Set RSDetails = Nothing
    
    CN.CommitTrans
      
    HaveAction = True
      
    If State = adStateAddMode Then
      MsgBox "New record has been successfully saved.", vbInformation
      If MsgBox("Do you want to add another new record?", vbQuestion + vbYesNo) = vbYes Then
        ResetFields
      Else
        Unload Me
      End If
    ElseIf State = adStatePopupMode Then
      MsgBox "New record has been successfully saved.", vbInformation
      Unload Me
    Else
      MsgBox "Changes in  record has been successfully saved.", vbInformation
      Unload Me
    End If
    
    Exit Sub
erR:
    CN.RollbackTrans
    MsgBox "Error: " & erR.Description, vbExclamation
End Sub

Private Sub cmdUsrHistory_Click()
    On Error Resume Next
    Dim tDate1 As String
    Dim tDate2 As String
    Dim tUser1 As String
    Dim tUser2 As String
    
    tDate1 = Format$(rs.Fields("DateAdded"), "MMM-dd-yyyy HH:MM AMPM")
    tDate2 = Format$(rs.Fields("DateModified"), "MMM-dd-yyyy HH:MM AMPM")
    
    tUser1 = getValueAt("SELECT PK,CompleteName FROM tbl_SM_Users WHERE PK = " & rs.Fields("AddedByFK"), "CompleteName")
    tUser2 = getValueAt("SELECT PK,CompleteName FROM tbl_SM_Users WHERE PK = " & rs.Fields("LastUserFK"), "CompleteName")
    
    MsgBox "Date Added: " & tDate1 & vbCrLf & _
           "Added By: " & tUser1 & vbCrLf & _
           "" & vbCrLf & _
           "Last Modified: " & tDate2 & vbCrLf & _
           "Modified By: " & tUser2, vbInformation, "Modification History"
           
    tDate1 = vbNullString
    tDate2 = vbNullString
    tUser1 = vbNullString
    tUser2 = vbNullString
End Sub

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

Private Sub Form_Load()
    InitGrid
    
    rs.CursorLocation = adUseClient
    rs.Open "SELECT * FROM Local_Forwarder WHERE LocalForwarderID = " & PK, CN, adOpenStatic, adLockOptimistic
    
    bind_dc "SELECT * FROM Local_Forwarder_Account_Description", "AccTitle", dcTitle, "LocalForwarderAccTitleID", True
        
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        Caption = "Create New Entry"
        cmdUsrHistory.Enabled = False

        GeneratePK
    Else
        Caption = "Edit Entry"
        DisplayForEditing
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    If HaveAction = True Then
        If State = adStateAddMode Or State = adStateEditMode Then
            frmLocalForwarder.RefreshRecords
        ElseIf State = adStatePopupMode Then
            srcText.Text = txtEntry(0).Text
            srcText.Tag = PK
            On Error Resume Next
            srcTextAdd.Text = rs![DisplayAddr]
            srcTextCP.Text = txtEntry(6).Text
        End If
    End If
    
    Set frmLocalForwarderAE = Nothing
End Sub

Private Sub Grid_Click()
    With Grid
        dcTitle.Text = .TextMatrix(.RowSel, 1)
        txtAmount.Text = .TextMatrix(.RowSel, 2)
    
        If Grid.Rows = 2 And Grid.TextMatrix(1, 3) = "" Then '10 = StockID
            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 txtEntry_GotFocus(Index As Integer)
    If Index = 8 Then cmdSave.Default = False
    HLText txtEntry(Index)
End Sub

Private Sub txtEntry_KeyPress(Index As Integer, KeyAscii As Integer)
  If Index = 6 Then KeyAscii = isNumber(KeyAscii)
End Sub

'Procedure used to initialize the grid
Private Sub InitGrid()
    cIRowCount = 0
    With Grid
        .Clear
        .ClearStructure
        .Rows = 2
        .FixedRows = 1
        .FixedCols = 1
        .Cols = 4
        .ColSel = 3
        'Initialize the column size
        .ColWidth(0) = 315
        .ColWidth(1) = 2400
        .ColWidth(2) = 1500
        .ColWidth(3) = 0
        'Initialize the column name
        .TextMatrix(0, 0) = ""
        .TextMatrix(0, 1) = "Class"
        .TextMatrix(0, 2) = "Cost Handling"
        .TextMatrix(0, 3) = "LocalForwarderDetailID"
        'Set the column alignment
        .ColAlignment(0) = vbLeftJustify
        .ColAlignment(1) = vbLeftJustify
        .ColAlignment(2) = vbLeftJustify
    End With
End Sub

Private Sub DeleteItems()
    Dim CurrRow As Integer
    Dim RSDetails As New Recordset
    
    If State = adStateAddMode Then Exit Sub
    
    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM Local_Forwarder_Detail WHERE LocalForwarderID=" & PK, CN, adOpenStatic, adLockOptimistic
    If RSDetails.RecordCount > 0 Then
        RSDetails.MoveFirst
        While Not RSDetails.EOF
            CurrRow = getFlexPos(Grid, 3, RSDetails!AccountDescriptionID)
        
            'Add to grid
            With Grid
                If CurrRow < 0 Then
                    'Delete record if doesnt exist in flexgrid
                    DelRecwSQL "Local_Forwarder_Detail", "LocalForwarderDetailID", "", True, RSDetails!LocalForwarderDetailID
                End If
            End With
            RSDetails.MoveNext
        Wend
    End If
End Sub