Option Compare Database
Option Explicit
'------------------------------------------------------------
' UPDATE()
'
' DESC: This function updates the IMF2 table's field Qty_on_Hand and
' possibly the Exception_Code field if there is an exception i.e.
' if the Qty_on_Hand is bigger than Maximum Inventory or lower than
' Safety Stock.  The information is obtained from the Transaction_File table
' that was populated with the Forms from assignment seven.  Two logs are created:
' an Update_Log that has the transaction data plus the before and after Qty_Filled field and
' a status of whether it was successful or not and there is
' a Transaction Log that has the transaction data plus a log date and time.
'
' INPUT: Transaction_File table
' TABLES/REFERENCE: IMF2 table
' OUTPUTS: Update_Log table, Transaction_Log table (Updated IMF2 table)
'
' Written by:
'   Shawn McKenna       Chief Big Daddy Programmer      CO
'------------------------------------------------------------
Function Update()

'Keep an extra copy of the IMF2 table for safekeeping.
DoCmd.CopyObject "", "COPY_IMF2", acTable, "IMF2"

Dim dbUPDATE As Database
Dim rcdIMF2, rcdTRANS, rcdTRANSLOG, rcdUPDATELOG As Recordset
Dim Qty_Before, Qty_After As Long

Set dbUPDATE = CurrentDb()
Set rcdIMF2 = dbUPDATE.OpenRecordset("IMF2")
Set rcdTRANS = dbUPDATE.OpenRecordset("Transaction_File")
Set rcdTRANSLOG = dbUPDATE.OpenRecordset("Transaction_Log")
Set rcdUPDATELOG = dbUPDATE.OpenRecordset("Update_Log")

While Not rcdTRANS.EOF

'----------------------------------------------
' Shawn McKenna's Part
'----------------------------------------------

    If rcdTRANS![Transaction_Type] = "CO" Then
        
    rcdIMF2.MoveFirst   ' Move back to the first record
    While Not rcdIMF2.EOF
        If rcdIMF2![Product_Num] = rcdTRANS![Product_Num] And rcdIMF2![Store_ID] = rcdTRANS![Origin] Then
            ' this statement saves the old Qty_on_Hand
            Qty_Before = rcdIMF2![Qty_on_Hand]
            ' then Qty_on_Hand is subtracted by Qty_Filled for Customers
            rcdIMF2.Edit
            rcdIMF2![Qty_on_Hand] = Qty_Before - rcdTRANS![Qty_Filled]
            ' this if statement checks to see if the exception code need to be changed in case of
            ' maximum inventory, , under safety level, under or equal to zero.
            If rcdIMF2![Qty_on_Hand] >= rcdIMF2![Safety_Level] + (rcdIMF2![Econ_Order_Quantity] * 1.1) Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= (rcdIMF2![Sales_Rate] * rcdIMF2![Leadtime]) + rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= 0 Then
                rcdIMF2![Exception_Code] = "#"
            End If
            rcdIMF2.Update
            Qty_After = rcdIMF2![Qty_on_Hand]
                         
            
        End If
    rcdIMF2.MoveNext
    Wend
        
        'The following statements add a record to the Transaction_Log table
        rcdTRANSLOG.AddNew
        rcdTRANSLOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdTRANSLOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdTRANSLOG![Origin] = rcdTRANS![Origin]
        rcdTRANSLOG![Destination] = rcdTRANS![Destination]
        rcdTRANSLOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdTRANSLOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdTRANSLOG![Product_Num] = rcdTRANS![Product_Num]
        rcdTRANSLOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdTRANSLOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdTRANSLOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdTRANSLOG![Trans_Log_Date] = Date
        rcdTRANSLOG![Trans_Log_Time] = Time
        rcdTRANSLOG.Update

        'The following statements add a record to the Update_Log table
        rcdUPDATELOG.AddNew
        rcdUPDATELOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdUPDATELOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdUPDATELOG![Origin] = rcdTRANS![Origin]
        rcdUPDATELOG![Destination] = rcdTRANS![Destination]
        rcdUPDATELOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdUPDATELOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdUPDATELOG![Product_Num] = rcdTRANS![Product_Num]
        rcdUPDATELOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdUPDATELOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdUPDATELOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdUPDATELOG![Qty_on_Hand_Before] = Qty_Before
        rcdUPDATELOG![Qty_on_Hand_After] = Qty_After
        rcdUPDATELOG![Status] = "Success"
        rcdUPDATELOG.Update

'----------------------------------------------
'
'----------------------------------------------
ElseIf rcdTRANS![Transaction_Type] = "PO" Then

    rcdIMF2.MoveFirst   ' Move back to the first record
    While Not rcdIMF2.EOF
        If rcdIMF2![Product_Num] = rcdTRANS![Product_Num] And _
        rcdIMF2![Store_ID] = rcdTRANS![Destination] Then
            ' this statement saves the old Qty_on_Hand
            Qty_Before = rcdIMF2![Qty_on_Hand]
            rcdIMF2.Edit
            'this statement adds the incoming vendor order to the IMF!Qty_on_Hand
            rcdIMF2![Qty_on_Hand] = Qty_Before + rcdTRANS![Qty_Filled]
            ' this if statement checks to see if the exception code need to be changed in case of
            ' maximum inventory, , under safety level, under or equal to zero.
            If rcdIMF2![Qty_on_Hand] >= rcdIMF2![Safety_Level] + (rcdIMF2![Econ_Order_Quantity] * 1.1) Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= (rcdIMF2![Sales_Rate] * rcdIMF2![Leadtime]) + rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= 0 Then
                rcdIMF2![Exception_Code] = "#"
            End If
            rcdIMF2.Update
            Qty_After = rcdIMF2![Qty_on_Hand]
            
                        
        End If
    rcdIMF2.MoveNext
    Wend
        
        'The following statements add a record to the Transaction_Log table
        rcdTRANSLOG.AddNew
        rcdTRANSLOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdTRANSLOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdTRANSLOG![Origin] = rcdTRANS![Origin]
        rcdTRANSLOG![Destination] = rcdTRANS![Destination]
        rcdTRANSLOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdTRANSLOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdTRANSLOG![Product_Num] = rcdTRANS![Product_Num]
        rcdTRANSLOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdTRANSLOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdTRANSLOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdTRANSLOG![Trans_Log_Date] = Date
        rcdTRANSLOG![Trans_Log_Time] = Time
        rcdTRANSLOG.Update

        'The following statements add a record to the Update_Log table
        rcdUPDATELOG.AddNew
        rcdUPDATELOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdUPDATELOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdUPDATELOG![Origin] = rcdTRANS![Origin]
        rcdUPDATELOG![Destination] = rcdTRANS![Destination]
        rcdUPDATELOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdUPDATELOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdUPDATELOG![Product_Num] = rcdTRANS![Product_Num]
        rcdUPDATELOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdUPDATELOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdUPDATELOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdUPDATELOG![Qty_on_Hand_Before] = Qty_Before
        rcdUPDATELOG![Qty_on_Hand_After] = Qty_After
        rcdUPDATELOG![Status] = "Success"
        rcdUPDATELOG.Update
'----------------------------------------------
'
'----------------------------------------------
ElseIf rcdTRANS![Transaction_Type] = "OT" Then

    rcdIMF2.MoveFirst   ' Move back to the first record
    While Not rcdIMF2.EOF
        If rcdIMF2![Product_Num] = rcdTRANS![Product_Num] And _
        rcdIMF2![Store_ID] = rcdTRANS![Origin] Then
            ' this statement saves the old Qty_on_Hand
            Qty_Before = rcdIMF2![Qty_on_Hand]
            rcdIMF2.Edit
            'this statement subtracts the outgoing transfer order to the IMF!Qty_on_Hand
            rcdIMF2![Qty_on_Hand] = Qty_Before - rcdTRANS![Qty_Filled]
            ' this if statement checks to see if the exception code need to be changed in case of
            ' maximum inventory, , under safety level, under or equal to zero.
            If rcdIMF2![Qty_on_Hand] >= rcdIMF2![Safety_Level] + (rcdIMF2![Econ_Order_Quantity] * 1.1) Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= (rcdIMF2![Sales_Rate] * rcdIMF2![Leadtime]) + rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= 0 Then
                rcdIMF2![Exception_Code] = "#"
            End If
            rcdIMF2.Update
            Qty_After = rcdIMF2![Qty_on_Hand]
            
                        
        End If
    rcdIMF2.MoveNext
    Wend
        
        'The following statements add a record to the Transaction_Log table
        rcdTRANSLOG.AddNew
        rcdTRANSLOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdTRANSLOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdTRANSLOG![Origin] = rcdTRANS![Origin]
        rcdTRANSLOG![Destination] = rcdTRANS![Destination]
        rcdTRANSLOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdTRANSLOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdTRANSLOG![Product_Num] = rcdTRANS![Product_Num]
        rcdTRANSLOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdTRANSLOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdTRANSLOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdTRANSLOG![Trans_Log_Date] = Date
        rcdTRANSLOG![Trans_Log_Time] = Time
        rcdTRANSLOG.Update

        'The following statements add a record to the Update_Log table
        rcdUPDATELOG.AddNew
        rcdUPDATELOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdUPDATELOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdUPDATELOG![Origin] = rcdTRANS![Origin]
        rcdUPDATELOG![Destination] = rcdTRANS![Destination]
        rcdUPDATELOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdUPDATELOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdUPDATELOG![Product_Num] = rcdTRANS![Product_Num]
        rcdUPDATELOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdUPDATELOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdUPDATELOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdUPDATELOG![Qty_on_Hand_Before] = Qty_Before
        rcdUPDATELOG![Qty_on_Hand_After] = Qty_After
        rcdUPDATELOG![Status] = "Success"
        rcdUPDATELOG.Update

'----------------------------------------------
'
'----------------------------------------------
ElseIf rcdTRANS![Transaction_Type] = "IT" Then
    
    rcdIMF2.MoveFirst   ' Move back to the first record
    While Not rcdIMF2.EOF
        If rcdIMF2![Product_Num] = rcdTRANS![Product_Num] And _
        rcdIMF2![Store_ID] = rcdTRANS![Destination] Then
            ' this statement saves the old Qty_on_Hand
            Qty_Before = rcdIMF2![Qty_on_Hand]
            rcdIMF2.Edit
            'this statement adds the incoming transfer order to the IMF!Qty_on_Hand
            rcdIMF2![Qty_on_Hand] = Qty_Before + rcdTRANS![Qty_Filled]
            ' this if statement checks to see if the exception code need to be changed in case of
            ' maximum inventory, , under safety level, under or equal to zero.
            If rcdIMF2![Qty_on_Hand] >= rcdIMF2![Safety_Level] + (rcdIMF2![Econ_Order_Quantity] * 1.1) Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= (rcdIMF2![Sales_Rate] * rcdIMF2![Leadtime]) + rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= rcdIMF2![Safety_Level] Then
                rcdIMF2![Exception_Code] = "#"
            ElseIf rcdIMF2![Qty_on_Hand] <= 0 Then
                rcdIMF2![Exception_Code] = "#"
            End If
            rcdIMF2.Update
            Qty_After = rcdIMF2![Qty_on_Hand]
            
                       
        End If
    rcdIMF2.MoveNext
    Wend
        
        'The following statements add a record to the Transaction_Log table
        rcdTRANSLOG.AddNew
        rcdTRANSLOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdTRANSLOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdTRANSLOG![Origin] = rcdTRANS![Origin]
        rcdTRANSLOG![Destination] = rcdTRANS![Destination]
        rcdTRANSLOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdTRANSLOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdTRANSLOG![Product_Num] = rcdTRANS![Product_Num]
        rcdTRANSLOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdTRANSLOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdTRANSLOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdTRANSLOG![Trans_Log_Date] = Date
        rcdTRANSLOG![Trans_Log_Time] = Time
        rcdTRANSLOG.Update

        'The following statements add a record to the Update_Log table
        rcdUPDATELOG.AddNew
        rcdUPDATELOG![Transaction_Num] = rcdTRANS![Transaction_Num]
        rcdUPDATELOG![Transaction_Type] = rcdTRANS![Transaction_Type]
        rcdUPDATELOG![Origin] = rcdTRANS![Origin]
        rcdUPDATELOG![Destination] = rcdTRANS![Destination]
        rcdUPDATELOG![Trans_Date] = rcdTRANS![Trans_Date]
        rcdUPDATELOG![Trans_Time] = rcdTRANS![Trans_Time]
        rcdUPDATELOG![Product_Num] = rcdTRANS![Product_Num]
        rcdUPDATELOG![Cost_per_Unit] = rcdTRANS![Cost_per_Unit]
        rcdUPDATELOG![Qty_Ordered] = rcdTRANS![Qty_Ordered]
        rcdUPDATELOG![Qty_Filled] = rcdTRANS![Qty_Filled]
        rcdUPDATELOG![Qty_on_Hand_Before] = Qty_Before
        rcdUPDATELOG![Qty_on_Hand_After] = Qty_After
        rcdUPDATELOG![Status] = "Success"
        rcdUPDATELOG.Update

End If

rcdTRANS.MoveNext
Wend

End Function