Here is the method that worked
Dim RS1 As New ADODB.Recordset
Dim RS2 As New ADODB.Recordset
Dim RS3 As New ADODB.Recordset
Dim RS4 As New ADODB.Recordset
Dim RS5 As New ADODB.Recordset
Dim RS6 As New ADODB.Recordset
Dim RS7 As New ADODB.Recordset
Dim RS8 As New ADODB.Recordset
Dim RS9 As New ADODB.Recordset
Dim RS10 As New ADODB.Recordset
Dim RS11 As New ADODB.Recordset
Dim RS12 As New ADODB.Recordset
Dim RS13 As New ADODB.Recordset
Dim intFileHandle2 As Integer
Dim StrInput2 As String
Dim tFileName As String
Dim uSQL As String
Dim vSQL As String
Dim janSQL As String
Dim febSQL As String
Dim marSQL As String
Dim aprSQL As String
Dim maySQL As String
Dim junSQL As String
Dim julSQL As String
Dim augSQL As String
Dim sepSQL As String
Dim octSQL As String
Dim novSQL As String
Dim decSQL As String
Dim sPath As String
'Dim i As Integer
vSQL = "SELECT ISNULL((select ead_Number from DMF_Address where ead_CompanyNo = eia_compno and ead_factory = eia_fac and ead_AddrID = '" & sAddr & "'), '') AS KOD, " & _
"CAST((EIA_EISAMTER+EIA_EISAMTEE) * 100 AS INT) AS AMOUNT_EIS, " & _
"CAST(EEP_NAME AS NVARCHAR(45)) AS EEP_NAME, " & _
"CAST(EEP_IC AS NVARCHAR(12)) AS EEP_IC, " & _
"CAST(EEP_EISNO AS NVARCHAR(9)) AS EEP_EISNO, dtjoin, dtresign " & _
"FROM DMF_EISArrears (NOLOCK) RIGHT JOIN " & _
"(SELECT EEP_SECURITYLVL, EEP_COMPANY, EEP_TYPE, EEP_NO, EEP_NAME, EEP_FACTORY, CASE WHEN EEP_EISIC = 0 THEN (CASE WHEN LEN(EEP_OLDIC) = 0 THEN '' WHEN LEFT(EEP_OLDIC,1) <> 'A' THEN 'A' + EEP_OLDIC ELSE EEP_OLDIC END) " & _
"WHEN EEP_EISIC = 1 THEN REPLACE(EEP_NEWIC,'-','') " & _
"WHEN EEP_EISIC = 2 THEN EEP_PASSPORT " & _
"WHEN EEP_EISIC = 3 THEN EEP_PERMIT END " & _
"AS EEP_IC, EEP_EISNO, CASE WHEN MONTH(eep_dtjoin)=" & Month(dtpSocsoDate.Value) & " AND YEAR(eep_dtjoin)=" & Year(dtpSocsoDate.Value) & " THEN eep_dtjoin END AS dtjoin, " & _
"CASE WHEN MONTH(eep_dtresign)=" & Month(dtpSocsoDate.Value) & " AND YEAR(eep_dtresign)=" & Year(dtpSocsoDate.Value) & " THEN eep_dtresign END AS dtresign " & _
"FROM DMF_EMPLOYEE (NOLOCK) WHERE EEP_EIS = 'Y') AS EMPLOYEE " & _
"ON (EIA_EMPNO = EEP_NO) WHERE EIA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
"EIA_PAYYEAR = " & Year(dtpSocsoDate.Value) & " "
janSQL = vSQL & " AND " & " eia_month ='1' "
febSQL = vSQL & " AND " & " eia_month = '2' "
marSQL = vSQL & " AND " & " eia_month = '3' "
aprSQL = vSQL & " AND " & " eia_month = '4' "
maySQL = vSQL & " AND " & " eia_month = '5' "
junSQL = vSQL & " AND " & " eia_month = '6' "
julSQL = vSQL & " AND " & " eia_month = '7' "
augSQL = vSQL & " AND " & " eia_month = '8' "
sepSQL = vSQL & " AND " & " eia_month = '9' "
octSQL = vSQL & " AND " & " eia_month = '10' "
novSQL = vSQL & " AND " & " eia_month = '11' "
decSQL = vSQL & " AND " & " eia_month = '12' "
'If sCond <> "" Then
' vSQL = vSQL & sCond
'End If
' ssql = ssql & " ORDER BY EEP_NAME "
'vSQL = vSQL & " GROUP BY SOA_COMPNO, SOA_FAC, EEP_NAME, EEP_IC, EEP_SOCSONO, dtjoin, dtresign ORDER BY SOA_COMPNO, SOA_FAC, EEP_NAME "
uSQL = "SELECT eia_month FROM DMF_EISArrears WHERE EIA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
"EIA_PAYYEAR = " & Year(dtpSocsoDate.Value) & ""
RS1.Open janSQL, Conn, adOpenStatic, adLockReadOnly
RS2.Open febSQL, Conn, adOpenStatic, adLockReadOnly
RS3.Open marSQL, Conn, adOpenStatic, adLockReadOnly
RS4.Open aprSQL, Conn, adOpenStatic, adLockReadOnly
RS5.Open maySQL, Conn, adOpenStatic, adLockReadOnly
RS6.Open junSQL, Conn, adOpenStatic, adLockReadOnly
RS7.Open julSQL, Conn, adOpenStatic, adLockReadOnly
RS8.Open augSQL, Conn, adOpenStatic, adLockReadOnly
RS9.Open sepSQL, Conn, adOpenStatic, adLockReadOnly
RS10.Open octSQL, Conn, adOpenStatic, adLockReadOnly
RS11.Open novSQL, Conn, adOpenStatic, adLockReadOnly
RS12.Open decSQL, Conn, adOpenStatic, adLockReadOnly
RS13.Open uSQL, Conn, adOpenStatic, adLockReadOnly
If RS13.RecordCount > 0 Then
While Not RS13.EOF
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS13!EIA_MONTH = 1 Then
tFileName = "BRG8A_EIS(Januari).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS1.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS1.EOF
strJoin = IIf("" & RS1!dtjoin = "", "", Format("" & RS1!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS1!dtresign = "", "", Format("" & RS1!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS1!KOD & Space(12), 12) & Space(20) & Left(RS1!eep_ic & Space(12), 12) & Left(RS1!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS1!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS1.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 2 Then
tFileName = "BRG8A_EIS(Febuari).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS2.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS2.EOF
strJoin = IIf("" & RS2!dtjoin = "", "", Format("" & RS2!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS2!dtresign = "", "", Format("" & RS2!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS2!KOD & Space(12), 12) & Space(20) & Left(RS2!eep_ic & Space(12), 12) & Left(RS2!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS2!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS2.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 3 Then
tFileName = "BRG8A_EIS(Mac).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS3.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS3.EOF
strJoin = IIf("" & RS3!dtjoin = "", "", Format("" & RS3!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS3!dtresign = "", "", Format("" & RS3!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS3!KOD & Space(12), 12) & Space(20) & Left(RS3!eep_ic & Space(12), 12) & Left(RS3!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS3!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS3.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 4 Then
tFileName = "BRG8A_EIS(April).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS4.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS4.EOF
strJoin = IIf("" & RS4!dtjoin = "", "", Format("" & RS4!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS4!dtresign = "", "", Format("" & RS4!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS4!KOD & Space(12), 12) & Space(20) & Left(RS4!eep_ic & Space(12), 12) & Left(RS4!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS4!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS4.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 5 Then
tFileName = "BRG8A_EIS(Mei).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS5.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS5.EOF
strJoin = IIf("" & RS5!dtjoin = "", "", Format("" & RS5!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS5!dtresign = "", "", Format("" & RS5!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS5!KOD & Space(12), 12) & Space(20) & Left(RS5!eep_ic & Space(12), 12) & Left(RS5!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS5!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS5.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 6 Then
tFileName = "BRG8A_EIS(Jun).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS6.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS6.EOF
strJoin = IIf("" & RS6!dtjoin = "", "", Format("" & RS6!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS6!dtresign = "", "", Format("" & RS6!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS6!KOD & Space(12), 12) & Space(20) & Left(RS6!eep_ic & Space(12), 12) & Left(RS6!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS6!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS6.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 7 Then
tFileName = "BRG8A_EIS(Julai).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS7.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS7.EOF
strJoin = IIf("" & RS7!dtjoin = "", "", Format("" & RS7!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS7!dtresign = "", "", Format("" & RS7!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS7!KOD & Space(12), 12) & Space(20) & Left(RS7!eep_ic & Space(12), 12) & Left(RS7!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS7!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS7.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 8 Then
tFileName = "BRG8A_EIS(Ogos).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS8.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS8.EOF
strJoin = IIf("" & RS8!dtjoin = "", "", Format("" & RS8!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS8!dtresign = "", "", Format("" & RS8!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS8!KOD & Space(12), 12) & Space(20) & Left(RS8!eep_ic & Space(12), 12) & Left(RS8!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS8!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS8.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 9 Then
tFileName = "BRG8A_EIS(September).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS9.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS9.EOF
strJoin = IIf("" & RS9!dtjoin = "", "", Format("" & RS9!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS9!dtresign = "", "", Format("" & RS9!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS9!KOD & Space(12), 12) & Space(20) & Left(RS9!eep_ic & Space(12), 12) & Left(RS9!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS9!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS9.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 10 Then
tFileName = "BRG8A_EIS(Oktober).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS10.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS10.EOF
strJoin = IIf("" & RS10!dtjoin = "", "", Format("" & RS10!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS10!dtresign = "", "", Format("" & RS10!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS10!KOD & Space(12), 12) & Space(20) & Left(RS10!eep_ic & Space(12), 12) & Left(RS10!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS10!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS10.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 11 Then
tFileName = "BRG8A_EIS(November).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS11.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS11.EOF
strJoin = IIf("" & RS11!dtjoin = "", "", Format("" & RS11!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS11!dtresign = "", "", Format("" & RS11!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS11!KOD & Space(12), 12) & Space(20) & Left(RS11!eep_ic & Space(12), 12) & Left(RS11!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS11!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS11.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
If RS13!EIA_MONTH = 12 Then
tFileName = "BRG8A_EIS(Disember).TXT"
intFileHandle2 = FreeFile
sPath = txtSocsoLoc.Text & tFileName
If RS12.RecordCount > 0 Then
Open sPath For Append Shared As #intFileHandle2
While Not RS12.EOF
strJoin = IIf("" & RS12!dtjoin = "", "", Format("" & RS12!dtjoin, "ddMMyyyy"))
strResign = IIf("" & RS12!dtresign = "", "", Format("" & RS12!dtresign, "ddMMyyyy"))
strJoinResign = Left(strJoin & Space(8), 8)
If strResign <> "" Then strJoinResign = strResign
strStatus = IIf(strJoin <> "", "B", " ")
strStatus = IIf(strResign <> "", "H", strStatus)
StrInput2 = ""
StrInput2 = Left(RS12!KOD & Space(12), 12) & Space(20) & Left(RS12!eep_ic & Space(12), 12) & Left(RS12!eep_name & Space(150), 150) & Format(dtpSocsoDate.Value, "MMyyyy") & Right(Replace(Space(14), " ", "0") & RS12!amount_eis, 14) & strJoinResign & strStatus
'Open sPath For Append Shared As #intFileHandle2
Print #intFileHandle2, StrInput2
'Close #intFileHandle2
StrInput2 = ""
RS12.MoveNext
Wend
'RS1.Close
Close #intFileHandle2
End If
End If
RS13.MoveNext
Wend
End If
'Set RS1 = Nothing
'Set RS2 = Nothing
Call SDefault(Me)
MsgBox "Monthly EIS Arrear Files have successfully saved.", vbInformation, Me.Caption