Click here to Skip to main content
16,021,041 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Here is my code, I tried to print multiple text file corresponding to the months in the database ( eg, if 1-> January.TXT, if 2-> February.TXT, and so on.. ). There are 3 lines in the database table, where the outputs should be 3 files with data, one is January.TXT, February.TXT and March.TXT.The error "File is already open" came up.It supposed to print 3 textfiles with data, all the 3 are printed but, only the first one has data, the rest two is empty, with the error "File is already open". Please help thank you.


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 soa_month FROM DMF_SocsoArrears WHERE SOA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
        "SOA_PAYYEAR = " & Year(dtpSocsoDate.Value) & " AND (SOA_SOAMTEE+SOA_SOAMTER) > 0 "
    
    RS1.Open vSQL, Conn, adOpenStatic, adLockReadOnly
    RS2.Open uSQL, Conn, adOpenStatic, adLockReadOnly
    
    While Not RS2.EOF
    Select Case RS2!soa_month
        Case "1"
            tFileName = "January.TXT"
        Case "2"
            tFileName = "February.TXT"
        Case "3"
           tFileName = "March.TXT"
        Case "4"
           tFileName = "April.TXT"
        Case "5"
           tFileName = "May.TXT"
        Case "6"
           tFileName = "June.TXT"
        Case "7"
           tFileName = "July.TXT"
        Case "8"
           tFileName = "August.TXT"
        Case "9"
           tFileName = "September.TXT"
        Case "10"
           tFileName = "October.TXT"
        Case "11"
           tFileName = "November.TXT"
        Case "12"
           tFileName = "December.TXT"
    End Select
        
            intFileHandle2 = FreeFile
    
            Dim sPath As String
            sPath = txtSocsoLoc.Text & tFileName
            Open sPath For Output As #intFileHandle2
            If RS1.RecordCount > 0 Then
                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_socso, 14) & strJoinResign & strStatus
                    
                    
                    Print #intFileHandle2, StrInput2
                    
                    RS1.MoveNext
                Wend
            End If
            
            RS2.MoveNext
    Wend
    

    Close #intFileHandle2
    Set RS1 = Nothing
    Set RS2 = Nothing
    Call SDefault(Me)

    MsgBox "Monthly Socso Arrear Files have successfully saved.", vbInformation, Me.Caption


What I have tried:

I have tried to change the position of the open, and "close intfile"at many places, non worked. 
Posted
Updated 27-May-18 21:28pm

Indentation is here to help understand your code structure, so a careful usage is mandatory:
VB
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 soa_month FROM DMF_SocsoArrears WHERE SOA_PAYMONTH = " & Month(dtpSocsoDate.Value) & " AND " & _
"SOA_PAYYEAR = " & Year(dtpSocsoDate.Value) & " AND (SOA_SOAMTEE+SOA_SOAMTER) > 0 "

RS1.Open vSQL, Conn, adOpenStatic, adLockReadOnly
RS2.Open uSQL, Conn, adOpenStatic, adLockReadOnly

While Not RS2.EOF
  Select Case RS2!soa_month
    Case "1"
    tFileName = "January.TXT"
    Case "2"
    tFileName = "February.TXT"
    Case "3"
    tFileName = "March.TXT"
    Case "4"
    tFileName = "April.TXT"
    Case "5"
    tFileName = "May.TXT"
    Case "6"
    tFileName = "June.TXT"
    Case "7"
    tFileName = "July.TXT"
    Case "8"
    tFileName = "August.TXT"
    Case "9"
    tFileName = "September.TXT"
    Case "10"
    tFileName = "October.TXT"
    Case "11"
    tFileName = "November.TXT"
    Case "12"
    tFileName = "December.TXT"
  End Select

  intFileHandle2 = FreeFile

  Dim sPath As String
  sPath = txtSocsoLoc.Text & tFileName
  Open sPath For Output As #intFileHandle2
  If RS1.RecordCount > 0 Then
    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_socso, 14) & strJoinResign & strStatus


      Print #intFileHandle2, StrInput2

      RS1.MoveNext
    Wend
  End If

  Close #intFileHandle2

  RS2.MoveNext
Wend


Close #intFileHandle2
Set RS1 = Nothing
Set RS2 = Nothing
Call SDefault(Me)

MsgBox "Monthly Socso Arrear Files have successfully saved.", vbInformation, Me.Caption

Pa attention to the change I done to at the end to the 'close' command.
The 'open' is in a loop, the 'close' must be too.
 
Share this answer
 
Comments
Member 12609377 25-May-18 0:37am    
Hi! thanks a lot for replying, I have tried that already, and I tried again just now, all the 3 outputs are empty, somehow the "close #intFileHandle2" is preventing from writing data into the file. Something else somewhere else is wrong.. :'(
Please help thank you.
Patrice T 25-May-18 0:51am    
Use the debugger to see what is done exactly. Check that your code actually try to print something.
Member 12609377 28-May-18 3:30am    
Thank you! I found the problem, all the data is inserted into one file, hence the rest of the files appeared empty, hence had to switch to another method.
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900