Code:
Option Compare Database
Private Sub Command0_Click()
Dim con As Object
Dim RS As Object
Dim RSs As Object
Dim Sql1 As String
Dim Sql2 As String
Dim rsi1 As Object
Set rsi1 = CreateObject("ADODB.Recordset")
Set con = Application.CurrentProject.Connection
Sql1 = "SELECT DISTINCT [PRF-User Entry].SUN_DB, (select val(HIGH_JRNAL) from dbo_SALFMSC where dbo_SALFMSC.D_Base=[PRF-User Entry].SUN_DB and dbo_SALFMSC.A_B='A') AS GtIniRef, (SELECT Val(Right(Trim([dbo_SSRFMSC].[SUN_DATA]),7)) AS SUN_DATA FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)=[PRF-User Entry].[SUN_DB]) AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([dbo_SSRFMSC].[KEY_FIELDS]))='PI A'))) AS GtT9Rf FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));"
'SELECT DISTINCT [PRF-User Entry].SUN_DB, (select HIGH_JRNAL from dbo_SALFMSC where dbo_SALFMSC.D_Base=[PRF-User Entry].SUN_DB and dbo_SALFMSC.A_B="A") AS GtHJRf, (SELECT Val(Right(Trim([dbo_SSRFMSC].[SUN_DATA]),7)) AS SUN_DATA FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)=[PRF-User Entry].[SUN_DB]) AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([dbo_SSRFMSC].[KEY_FIELDS]))='PI A'))) AS GtT9Rf, [PRF-User Entry].TrnsRefNo FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));"
Set RS = CreateObject("ADODB.Recordset")
Set RSs = CreateObject("ADODB.Recordset")
RS.Open Sql1, con, 1
Dim i As Integer
For i = 1 To RS.RecordCount
Dim rs1 As Object
Dim rs3 As Object
Dim rsu1 As Object
Dim rsu2 As Object
Dim rsu3 As Object
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
Set rs3 = CreateObject("ADODB.Recordset")
Set rsu1 = CreateObject("ADODB.Recordset")
Set rsu2 = CreateObject("ADODB.Recordset")
Set rsu3 = CreateObject("ADODB.Recordset")
rs1.Open "SELECT [PRF-User Entry].SUN_DB, [PRF-User Entry].JRNAL_NO, [PRF-User Entry].TrnsRefNo FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)='" & RS!SUN_DB & "') AND (([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));", con, 1
Dim j As Integer
Dim k As Integer
j = 1
k = 0
Do While rs1.EOF = False
rsu1.Open "UPDATE [PRF-User Entry] SET [PRF-User Entry].JRNAL_NO =" & RS!GtIniRef + j & ",[PRF-User Entry].ANAL_T9 ='PI" & Right("00000" & RS!GtT9Rf + k, 5) & "' WHERE ((([PRF-User Entry].TrnsRefNo)=" & rs1!TrnsRefNo & "));", con, 2
j = j + 1
k = k + 1
rs1.MoveNext
Loop
rs2.Open "SELECT Right('0000000' & Max([JRNAL_NO]),7) AS LstJrnRef, Max(Right([ANAL_T9],5))+1 AS LstAnl9 FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)='" & RS!SUN_DB & "'));", con, 1
rsu2.Open "UPDATE dbo_SALFMSC SET dbo_SALFMSC.HIGH_JRNAL ='" & rs2!LstJrnRef & "' WHERE (((dbo_SALFMSC.D_BASE)='" & RS!SUN_DB & "') AND ((dbo_SALFMSC.A_B)='A'));", con, 2
rs3.Open "SELECT Replace([SUN_DATA],Mid([SUN_DATA],31,38),(SELECT 'A00' & Right('0' & Max(Right([ANAL_T9],5)+1),5) AS AnT9 FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUN_DB)=dbo_SSRFMSC.SUN_DB)))) AS SD FROM dbo_SSRFMSC WHERE (((dbo_SSRFMSC.SUN_DB)='" & RS!SUN_DB & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI A'));", con, 1
rsu3.Open "UPDATE dbo_SSRFMSC SET dbo_SSRFMSC.SUN_DATA ='" & [rs3]![SD] & "' WHERE (((dbo_SSRFMSC.SUN_DB)='" & [RS]![SUN_DB] & "') AND ((dbo_SSRFMSC.SUN_TB)='SQN') AND ((Trim([KEY_FIELDS]))='PI A'));", con, 2
RS.MoveNext
Next i
Sql2 = "SELECT [PRF-User Entry].TrnsRefNo,[PRF-User Entry].SUN_DB FROM [PRF-User Entry] WHERE ((([PRF-User Entry].SUPP_CODE) Is Not Null) AND (([PRF-User Entry].[GROSS AMOUNT]) Is Not Null) AND (([PRF-User Entry].READY)=True));"
RSs.Open Sql2, con, 1
Do While RSs.EOF = False
sqla = " SELECT Left([PRF-User Entry].[SUPP_CODE] & Space(15),15) AS ACCNT_CODE, Val(DatePart('yyyy',[PRF-User Entry].PERIOD) & Right('000' & DatePart('m',[PRF-User Entry].PERIOD),3)) AS PERIOD, Val(DatePart('yyyy',[PRF-User Entry].[INV_DATE]) & Right('00' & DatePart('m',[PRF-User Entry].[INV_DATE]),2) & Right('00' & DatePart('d',[PRF-User Entry].[INV_DATE]),2)) AS TRANS_DATE, [PRF-User Entry].[JRNAL_NO] AS JRNAL_NO, 1 AS JRNAL_LINE, [PRF-User Entry].[GROSS AMOUNT] AS AMOUNT, 'C' AS D_C, ' ' AS ALLOCATION, 'PI ' AS JRNAL_TYPE, 'OZG ' AS JRNAL_SRCE, Left([PRF-User Entry].[TREFERENCE] & Space(15),15) AS TREFERENCE, Left([PRF-User Entry].[DESCRIPTN] & Space(15),15) AS " & vbCrLf & _
"DESCRIPTN, Val(DatePart('yyyy',Now()) & Right('00' & DatePart('m',Now()),2) & Right('00' & DatePart('d',Now()),2)) AS ENTRY_DATE, Val(DatePart('yyyy',Now()) & Right('000' & DatePart('m',Now()),3)) AS ENTRY_PRD, 0 AS DUE_DATE, 0 AS ALLOC_REF, 0 AS ALLOC_DATE, 0 AS ALLOC_PERIOD, Space(1) AS ASSET_IND, Space(10) AS ASSET_CODE, Space(5) AS ASSET_SUB, Left([PRF-User Entry].[CONV_CODE] & Space(5),5) AS CONV_CODE, 0 AS CONV_RATE, 0 AS OTHER_AMT, Left([PRF-User Entry].[OTHER_DP] & Space(1),1) AS OTHER_DP, Space(5) AS CLEARDOWN, Space(1) AS REVERSAL, Space(1) AS LOSS_GAIN, Space(1) AS ROUGH_FLAG, Space(1) AS IN_USE_FLAG, Space(15) AS ANAL_T0, Left([PRF-User Entry].[ANAL_T1] & Space(15),15) AS ANAL_T1, Left([PRF-User Entry].[ANAL_T2] & Space(15),15) AS " & vbCrLf & _
"ANAL_T2, Left([PRF-User Entry].[ANAL_T3] & Space(15),15) AS ANAL_T3, Left(Right(Trim([PRF-User Entry].[SUPP_CODE]),3) & Space(15),15) AS ANAL_T4, Left([PRF-User Entry].[ANAL_T5].[value] & Space(15),15) AS ANAL_T5, Left([PRF-User Entry].[ANAL_T6] & Space(15),15) AS ANAL_T6, Left([PRF-User Entry].[ANAL_T7].[value] & Space(15),15) AS ANAL_T7, Left([PRF-User Entry].[ANAL_T8] & Space(15),15) AS ANAL_T8, Left([PRF-User Entry].[ANAL_T9] & Space(15),15) AS ANAL_T9, 0 AS POSTING_DATE, '' AS ALLOC_IN_PROGRESS, 0 AS HOLD_REF, Space(3) AS HOLD_OP_ID, Space(3) AS LAST_CHANGE_USER_ID, 0 AS LAST_CHANGE_DATE, Space(3) AS ORIGINATOR_ID FROM [PRF-User Entry] where [PRF-User Entry].TrnsRefNo=" & RSs!TrnsRefNo
rsi1.Open "INSERT INTO dbo_SALFLDG" & RSs!SUN_DB & sqla, con, 2
sqlb = " SELECT Left('271' & Space(15),15) AS ACCNT_CODE, Val(DatePart('yyyy',[PRF-User Entry].PERIOD) & Right('000' & DatePart('m',[PRF-User Entry].PERIOD),3)) AS PERIOD, Val(DatePart('yyyy',[PRF-User Entry].[INV_DATE]) & Right('00' & DatePart('m',[PRF-User Entry].[INV_DATE]),2) & Right('00' & DatePart('d',[PRF-User Entry].[INV_DATE]),2)) AS TRANS_DATE, [PRF-User Entry].[JRNAL_NO] AS JRNAL_NO, 2 AS JRNAL_LINE, -1*[PRF-User Entry].[VAT] AS AMOUNT, 'D' AS D_C, ' ' AS ALLOCATION, 'PI ' AS JRNAL_TYPE, 'OZG ' AS JRNAL_SRCE, Left([PRF-User Entry].[TREFERENCE] & Space(15),15) AS TREFERENCE, Left([PRF-User Entry].[DESCRIPTN] & Space(15),15) AS " & vbCrLf & _
"DESCRIPTN, Val(DatePart('yyyy',Now()) & Right('00' & DatePart('m',Now()),2) & Right('00' & DatePart('d',Now()),2)) AS ENTRY_DATE, Val(DatePart('yyyy',Now()) & Right('000' & DatePart('m',Now()),3)) AS ENTRY_PRD, 0 AS DUE_DATE, 0 AS ALLOC_REF, 0 AS ALLOC_DATE, 0 AS ALLOC_PERIOD, Space(1) AS ASSET_IND, Space(10) AS ASSET_CODE, Space(5) AS ASSET_SUB, Left([PRF-User Entry].[CONV_CODE] & Space(5),5) AS CONV_CODE, 0 AS CONV_RATE, 0 AS OTHER_AMT, Left([PRF-User Entry].[OTHER_DP] & Space(1),1) AS OTHER_DP, Space(5) AS CLEARDOWN, Space(1) AS REVERSAL, Space(1) AS LOSS_GAIN, Space(1) AS ROUGH_FLAG, Space(1) AS IN_USE_FLAG, Space(15) AS ANAL_T0, Left([PRF-User Entry].[ANAL_T1] & Space(15),15) AS ANAL_T1, Left([PRF-User Entry].[ANAL_T2] & Space(15),15) AS " & vbCrLf & _
"ANAL_T2, Left([PRF-User Entry].[ANAL_T3] & Space(15),15) AS ANAL_T3, Left(Right(Trim([PRF-User Entry].[SUPP_CODE]),3) & Space(15),15) AS ANAL_T4, Left([PRF-User Entry].[ANAL_T5].[value] & Space(15),15) AS ANAL_T5, Left([PRF-User Entry].[ANAL_T6] & Space(15),15) AS ANAL_T6, Left([PRF-User Entry].[ANAL_T7].[value] & Space(15),15) AS ANAL_T7, Left([PRF-User Entry].[ANAL_T8] & Space(15),15) AS ANAL_T8, Left([PRF-User Entry].[ANAL_T9] & Space(15),15) AS ANAL_T9, 0 AS POSTING_DATE, '' AS ALLOC_IN_PROGRESS, 0 AS HOLD_REF, Space(3) AS HOLD_OP_ID, Space(3) AS LAST_CHANGE_USER_ID, 0 AS LAST_CHANGE_DATE, Space(3) AS ORIGINATOR_ID FROM [PRF-User Entry] where [PRF-User Entry].TrnsRefNo=" & RSs!TrnsRefNo
rsi1.Open "INSERT INTO dbo_SALFLDG" & RSs!SUN_DB & sqlb, con, 2
sqlc = " SELECT Left([PRF-User Entry].[ACCNT_CODE] & Space(15),15) AS ACCNT_CODE, Val(DatePart('yyyy',[PRF-User Entry].PERIOD) & Right('000' & DatePart('m',[PRF-User Entry].PERIOD),3)) AS PERIOD, Val(DatePart('yyyy',[PRF-User Entry].[INV_DATE]) & Right('00' & DatePart('m',[PRF-User Entry].[INV_DATE]),2) & Right('00' & DatePart('d',[PRF-User Entry].[INV_DATE]),2)) AS TRANS_DATE, [PRF-User Entry].[JRNAL_NO] AS JRNAL_NO, 3 AS JRNAL_LINE, -1*[PRF-User Entry].[NET AMOUNT] AS AMOUNT, 'D' AS D_C, ' ' AS ALLOCATION, 'PI ' AS JRNAL_TYPE, 'OZG ' AS JRNAL_SRCE, Left([PRF-User Entry].[TREFERENCE] & Space(15),15) AS TREFERENCE, Left([PRF-User Entry].[DESCRIPTN] & Space(15),15) AS " & vbCrLf & _
"DESCRIPTN, Val(DatePart('yyyy',Now()) & Right('00' & DatePart('m',Now()),2) & Right('00' & DatePart('d',Now()),2)) AS ENTRY_DATE, Val(DatePart('yyyy',Now()) & Right('000' & DatePart('m',Now()),3)) AS ENTRY_PRD, 0 AS DUE_DATE, 0 AS ALLOC_REF, 0 AS ALLOC_DATE, 0 AS ALLOC_PERIOD, Space(1) AS ASSET_IND, Space(10) AS ASSET_CODE, Space(5) AS ASSET_SUB, Left([PRF-User Entry].[CONV_CODE] & Space(5),5) AS CONV_CODE, 0 AS CONV_RATE, 0 AS OTHER_AMT, Left([PRF-User Entry].[OTHER_DP] & Space(1),1) AS OTHER_DP, Space(5) AS CLEARDOWN, Space(1) AS REVERSAL, Space(1) AS LOSS_GAIN, Space(1) AS ROUGH_FLAG, Space(1) AS IN_USE_FLAG, Space(15) AS ANAL_T0, Left([PRF-User Entry].[ANAL_T1] & Space(15),15) AS ANAL_T1, Left([PRF-User Entry].[ANAL_T2] & Space(15),15) AS " & vbCrLf & _
"ANAL_T2, Left([PRF-User Entry].[ANAL_T3] & Space(15),15) AS ANAL_T3, Left(Right(Trim([PRF-User Entry].[SUPP_CODE]),3) & Space(15),15) AS ANAL_T4, Left([PRF-User Entry].[ANAL_T5].[value] & Space(15),15) AS ANAL_T5, Left([PRF-User Entry].[ANAL_T6] & Space(15),15) AS ANAL_T6, Left([PRF-User Entry].[ANAL_T7].[value] & Space(15),15) AS ANAL_T7, Left([PRF-User Entry].[ANAL_T8] & Space(15),15) AS ANAL_T8, Left([PRF-User Entry].[ANAL_T9] & Space(15),15) AS ANAL_T9, 0 AS POSTING_DATE, '' AS ALLOC_IN_PROGRESS, 0 AS HOLD_REF, Space(3) AS HOLD_OP_ID, Space(3) AS LAST_CHANGE_USER_ID, 0 AS LAST_CHANGE_DATE, Space(3) AS ORIGINATOR_ID FROM [PRF-User Entry] where [PRF-User Entry].TrnsRefNo=" & RSs!TrnsRefNo
rsi1.Open "INSERT INTO dbo_SALFLDG" & RSs!SUN_DB & sqlc, con, 2
rsu1.Open "UPDATE [PRF-User Entry] SET [PRF-User Entry].READY = False, [PRF-User Entry].POSTED_TO_SUN = True WHERE ((([PRF-User Entry].TrnsRefNo)=" & RSs!TrnsRefNo & "));", con, 2
RSs.MoveNext
Loop
End Sub