frmqtyadjustmentae.frm

Inventory control system

   End
End
Attribute VB_Name = "frmQtyAdjustmentAE"
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 cIRowCount              As Integer

Dim HaveAction              As Boolean 'Variable used to detect if the user perform some action
Dim rs                      As New Recordset 'Main recordset for Invoice
Dim blnSave                 As Boolean
Dim intQtyOld               As Integer 'Old txtQty Value. Hold when editing qty

Private Sub btnAdd_Click()
On Error GoTo erR
    
    Dim RSStockUnit As New Recordset
    
    If nsdStock.Text = "" Then nsdStock.SetFocus: Exit Sub
    
    If dcUnit.Text = "" Then
        MsgBox "Please select unit", vbInformation
        dcUnit.SetFocus
        Exit Sub
    End If
    
    Dim CurrRow As Integer

    Dim intStockID As Integer
    
    CurrRow = getFlexPos(Grid, 5, nsdStock.Tag)
    intStockID = nsdStock.Tag
    
    RSStockUnit.CursorLocation = adUseClient
    RSStockUnit.Open "SELECT * FROM qry_Stock_Unit WHERE StockID =" & intStockID & " AND UnitID = " & dcUnit.BoundText & " ORDER BY Stock_Unit.Order ASC", CN, adOpenStatic, adLockOptimistic
    
    'Add to grid
    With Grid
        If CurrRow < 0 Then
            'Perform if the record is not exist
            If .Rows = 2 And .TextMatrix(1, 5) = "" Then
                .TextMatrix(1, 1) = nsdStock.Text
                .TextMatrix(1, 2) = txtNewQty.Text
                .TextMatrix(1, 3) = txtOldQty.Text
                .TextMatrix(1, 4) = dcUnit.Text
                .TextMatrix(1, 5) = intStockID
            Else
                .Rows = .Rows + 1
                .TextMatrix(.Rows - 1, 1) = nsdStock.Text
                .TextMatrix(.Rows - 1, 2) = txtNewQty.Text
                .TextMatrix(.Rows - 1, 3) = txtOldQty.Text
                .TextMatrix(.Rows - 1, 4) = dcUnit.Text
                .TextMatrix(.Rows - 1, 5) = intStockID
                
                .FillStyle = 1

                .Row = .Rows - 1
                .ColSel = 3
            End If
            'Increase the record count
            cIRowCount = cIRowCount + 1
        Else
            If MsgBox("Item already exist. Do you want to replace it?", vbQuestion + vbYesNo) = vbYes Then
                .Row = CurrRow
                                
                .TextMatrix(CurrRow, 1) = nsdStock.Text
                .TextMatrix(CurrRow, 2) = txtNewQty.Text
                .TextMatrix(CurrRow, 3) = txtOldQty.Text
                .TextMatrix(CurrRow, 4) = dcUnit.Text
                
                    'restore qty to Stock Unit's table
                RSStockUnit!Onhand = RSStockUnit!Onhand - intQtyOld

                RSStockUnit.Update
            Else
                Exit Sub
            End If
        End If
               
            'Add/deduct qty from Stock Unit's table
        RSStockUnit!Onhand = txtNewQty.Text

        RSStockUnit.Update
            
        'Highlight the current row's column
        .ColSel = 4
        'Display a remove button
        
        Grid_Click
        'Reset the entry fields
        ResetEntry
    End With
    
    Exit Sub
    
erR:
    prompt_err erR, Name, "cmdSave_Click"
    Screen.MousePointer = vbDefault
End Sub

Private Sub btnRemove_Click()
    If MsgBox("This will restore the qty added previously from Products profile" & vbCrLf & vbCrLf & "Are you sure you want to continue?", vbInformation + vbYesNo) = vbNo Then Exit Sub
    
    Dim RSStockUnit As New Recordset
    
    RSStockUnit.CursorLocation = adUseClient
    RSStockUnit.Open "SELECT * FROM qry_Stock_Unit WHERE StockID =" & nsdStock.Tag & " AND UnitID = " & dcUnit.BoundText & " ORDER BY Stock_Unit.Order ASC", CN, adOpenStatic, adLockOptimistic

        'restore qty to Stock Unit's table
    RSStockUnit!Onhand = RSStockUnit!Onhand + (toNumber(Grid.TextMatrix(Grid.RowSel, 3)) - toNumber(Grid.TextMatrix(Grid.RowSel, 2)))

    RSStockUnit.Update
    
    'Remove selected load product
    With Grid
        'Update the record count
        cIRowCount = cIRowCount - 1
        
        If .Rows = 2 Then Grid.Rows = Grid.Rows + 1
        .RemoveItem (.RowSel)
    End With
    
    'Save to stock card
'    Dim RSStockCard As New Recordset
'
'    With RSStockCard
'        .CursorLocation = adUseClient
'        .Open "SELECT * FROM Stock_Card WHERE StockID = " & toNumber(Grid.TextMatrix(Grid.RowSel, 10)) & " AND RefNo2 = '" & txtRefNo.Text & "'", CN, adOpenStatic, adLockOptimistic
'
'        !Pieces2 = !Pieces2 - toNumber(Grid.TextMatrix(Grid.RowSel, 3))
'
'        .Update
'    End With
'
    btnRemove.Visible = False
    Grid_Click
End Sub

Private Sub CmdTasks_Click()
    PopupMenu mnu_Tasks
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 cmdCancel_Click()
On Error Resume Next

    If blnSave = False Then CN.RollbackTrans
    Unload Me
End Sub

Private Sub cmdSave_Click()
On Error GoTo erR

    'Verify the entries
    If cboReason.Text = "" Then
        MsgBox "Please select a reason.", vbExclamation
        cboReason.SetFocus
        Exit Sub
    End If
   
    If cIRowCount < 1 Then
        MsgBox "Please enter item to Adjust before you can save this record.", vbExclamation
        nsdStock.SetFocus
        Exit Sub
    End If
              
    If MsgBox("This save the record. Do you want to proceed?", vbQuestion + vbYesNo) = vbNo Then Exit Sub

    Dim RSDetails As New Recordset

    RSDetails.CursorLocation = adUseClient
    RSDetails.Open "SELECT * FROM Qty_Adjustment_Detail WHERE QtyAdjustmentID=" & PK, CN, adOpenStatic, adLockOptimistic

    Screen.MousePointer = vbHourglass

    Dim c As Integer

    DeleteItems
    
    'Save the record
    With rs
        If State = adStateAddMode Or State = adStatePopupMode Then
            .AddNew
            ![QtyAdjustmentID] = PK
            
            ![DateAdded] = Now
            ![AddedByFK] = CurrUser.USER_PK
        ElseIf State = adStateEditMode Then
            .Close
            .Open "SELECT * FROM Qty_Adjustment WHERE QtyAdjustmentID=" & PK, CN, adOpenStatic, adLockOptimistic
            
            ![DateModified] = Now
            ![LastUserFK] = CurrUser.USER_PK
        End If
        
        !Reason = cboReason.Text
        !Date = dtpDate.Value
        ![Status] = IIf(cboStatus.Text = "Adjusted", True, False)
        ![Notes] = txtNotes.Text

        .Update
    End With
    
    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![QtyAdjustmentID] = PK
                RSDetails![StockID] = toNumber(.TextMatrix(c, 5))
                RSDetails![NewQty] = toNumber(.TextMatrix(c, 2))
                RSDetails![OldQty] = toNumber(.TextMatrix(c, 3))
                RSDetails![UnitID] = getUnitID(.TextMatrix(c, 4))
                
                RSDetails.Update
                
            ElseIf State = adStateEditMode Then
                RSDetails.Filter = "StockID = " & toNumber(.TextMatrix(c, 5))
            
                If RSDetails.RecordCount = 0 Then GoTo AddNew
                
'                RSDetails![QtyAdjustmentID] = PK
'                RSDetails![StockID] = toNumber(.TextMatrix(c, 5))
                RSDetails![NewQty] = toNumber(.TextMatrix(c, 2))
                RSDetails![OldQty] = toNumber(.TextMatrix(c, 3))
                RSDetails![UnitID] = getUnitID(.TextMatrix(c, 4))
                
                RSDetails.Update
                
            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 Then
        MsgBox "New record has been successfully saved.", vbInformation
        
        If MsgBox("Do you want to add another new record?", vbQuestion + vbYesNo) = vbYes Then
            ResetFields
            GeneratePK
        Else
            Unload Me
        End If
    Else
        MsgBox "Changes in 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 dcUnit_Click(Area As Integer)
    Call GetQty
End Sub

Private Sub Form_Activate()
    On Error Resume Next
    If CloseMe = True Then
        Unload Me
    Else
        cboReason.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
    
    'Check the form state
    If State = adStateAddMode Or State = adStatePopupMode Then
        InitNSD
    
    'Set the recordset
    If rs.State = 1 Then rs.Close
        rs.Open "SELECT * FROM Qty_Adjustment WHERE QtyAdjustmentID=" & PK, CN, adOpenStatic, adLockOptimistic
        dtpDate.Value = Date
        
        CN.BeginTrans

        GeneratePK
    Else
        Screen.MousePointer = vbHourglass
        'Set the recordset
        rs.Open "SELECT * FROM qry_Qty_Adjustment WHERE QtyAdjustmentID=" & PK, CN, adOpenStatic, adLockOptimistic
        
        If State = adStateViewMode Then
            cmdCancel.Caption = "Close"
                   
            DisplayForViewing
        Else
            InitNSD
            
            CN.BeginTrans
            
            DisplayForEditing
        End If
    
        If ForCusAcc = True Then
            Me.Icon = frmSalesReceipts.Icon
        End If