Option Compare Database
Public Sub Command61_Click()
MsgBox ("Update Complete")
Call prUpdate
End Sub
Sub prClearOutstanding(iDB As Database)
Dim MySQL As String
MySQL = "Update tbProductOutstanding" 'Define SQL Statement for update
MySQL = MySQL & "Set Inbound = 0, Outbound = 0"
MySQL = MySQL & "Where Period = '" & tCurrentPeriod & "'"
iDB.Execute MySQL
End Sub
Public Sub prUpdate()
Dim MyDB As Database 'Declare Database Connect Variable
Dim MyRec As Recordset 'Declare Recordset Connect Variable
Dim MySQL As String 'Declare String for SQL Statement
Set MyDB = CurrentDb() 'Open Database Connection
Rem ***** Clear Outstanding
Call prClearOutstanding(MyDB) 'Call Sub Procedure to reset Inbound/Outbound
Rem ***** Get In/Out from transaction and update outstanding data
MySQL = "Select* From tbInventoryMain" ' Define SQL Command
MySQL = MySQL & "Order By DocDate"
Set MyRec = MyDB.OpenRecordset(MySQL) ' Open Recordset by MySQL
Do While Not MyRec.EOF 'Repeating when end of recordset
Call prReadSub(MyDB, MyRec) 'Call Sub Procedure to read tbInventorySub
MyRec.MoveNext 'Next Record
Loop 'End of Do While Statement
MyRec.Close 'Close Recordset
MyDB.Close 'Close Database Connection
End Sub
Sub prReadSub(iDB As Database, iRec As Recordset)
Rem ***** Parameter #1 is Database Connection
Rem ***** Parameter #2 is send from main procedure
Dim MyRec As Recordset
Dim MySQL As String
Dim SQLUpdate As String
MySQL = "Select * From tbInventorySub" 'Define SQl Command for read by DocNo
MySQL = MySQL & "Where Period = '" & iRec!DocNo & "'"
MySQL = MySQL & "Order by DocLine"
Set MyRec = iDB.OpenRecordset(MySQL) ' Open Recordset by MySQL
Do While Not MyRec.EOF '
Call prUpdateOutstanding(iDB, iRec, MyRec) 'Call Sub Procedure
MyRec.MoveNext 'Next Record
Loop 'End of do while
MyRec.Close
End Sub
Sub prUpdateOutstanding(iDB As Database, iRecMain As Recordset, iRecSub As Recordset)
Rem ***** This Procedure will update in condition form iRecMain and iRecSub
Dim MyRec As Recordset
Dim MySQL As String
MySQL = "Select * From tbProductOutstanding" 'Define SQl Statement
MySQL = MySQL & "Where Period = '" & tCurrentPeriod & "'"
MySQL = MySQL & "And Serial_number = '" & iRecSub!Serial_Number & "'"
Set MyRec = iDB.OpenRecordset(MySQL) 'Open REcordset
If MyRec.EOF Then
Rem ***** Not Found Add new Product and reset Outstanding
MyRec.AddNew
MyRec!Period = tCurrentPeriod
MyRec!Serial_Number = iRecSub!Serial_Number
Call prAccumulateOutstanding(iRecMain!DocType, iRecSub!Quantity, MyRec)
MyRec.Update
Else
MyRec.Edit
Call prAccumulateOutstanding(iRecMain!DocType, iRecSub!Quantity, MyRec)
MyRec.Update
Rem ***** Found-Accumulate Outstanding
End If
MyRec.Close
End Sub
Sub prAccumulateOutstanding(iDocType As String, iQty As Integer, iTargetRec As Recordset)
Dim tOldQty As Integer
If iDocType = "I" Then
tOldQty = iTargetRec!Inbound
iTargetRec!Inbound = tOldQty + iQty
Else
tOldQty = iTargetRec!Outbound
iTargetRec!Outbound = tOldQty - iQty
End If
End Sub
Load balance : Server 04
