Click here to Skip to main content
16,016,134 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 two lines in the database table, where the outputs should be 2 files, one is January.TXT, and another oe is February.TXT. But when I execute the code,only the February.TXT file created. What should I do obtain multiple text files at one click?




  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
    If RS2.RecordCount > 0 Then
     While Not RS2.EOF
        If RS2!soa_month = "1" Then
           tFileName = "January.TXT"
        ElseIf RS2!soa_month = "2" Then
           tFileName = "February.TXT"
        ElseIf RS2!soa_month = "3" Then
           tFileName = "March.TXT"
        ElseIf RS2!soa_month = "4" Then
           tFileName = "April.TXT"
        ElseIf RS2!soa_month = "5" Then
           tFileName = "May.TXT"
        ElseIf RS2!soa_month = "6" Then
           tFileName = "June.TXT"
        ElseIf RS2!soa_month = "7" Then
           tFileName = "July.TXT"
        ElseIf RS2!soa_month = "8" Then
           tFileName = "August.TXT"
        ElseIf RS2!soa_month = "9" Then
           tFileName = "September.TXT"
        ElseIf RS2!soa_month = "10" Then
           tFileName = "October.TXT"
        ElseIf RS2!soa_month = "11" Then
           tFileName = "November.TXT"
        ElseIf RS2!soa_month = "12" Then
           tFileName = "December.TXT"
        End If
     RS2.MoveNext
     Wend
    End If
    'tFileName = "January.TXT"
    intFileHandle2 = FreeFile
    
    Dim sPath As String
    sPath = txtSocsoLoc.Text & tFileName
    'MsgBox "" & tFileName, vbInformation, Me.Caption
    'If Dir(spath) <> "" Then spath = txtSocsoLoc.Text & "NOMANTH.TXT"
    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 = ""
'            StrInput = rs!KOD & Space(9 - Len(rs!KOD)) & Space(12 - Len(rs!eep_ic)) & rs!eep_ic & _
'                rs!eep_socsono & Space(9 - Len(rs!eep_socsono)) & _
'                Format(dtpSocsoDate, "MMyy") & rs!eep_name & Space(45 - Len(rs!eep_name)) & _
'                Replace(Space(4 - Len(rs!amount)), " ", "0") & rs!amount

            'For tSQL = 2003 To 2004
              
            'Next tSQL
            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


What I have tried:

Please help I have no idea on (PRINTING MULTIPLE FILES)
Posted
Updated 27-May-18 21:27pm

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
 
Method #1 - Select Case ... End Select[^]
VB
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

    Call PrintingSubroutine(tFileName)

    RS2.MoveNext
Wend

'somehwere after
Public Sub PrintingSubroutine(ByVal txtFileName As String)

   'here print data
End Sub


Method #2 - using Dictionary[^] object (simplified)
VB
'at the beginning of...
Dim oDict As Object

Set oDict = CreateObject("Scripting.Dictionary")
oDict.Add "1", "January.txt"
oDict.Add "2", "February.txt"
'.... till
oDict.Add "12", "December.txt"

'
'your code here
'

While RS2.EOF
    Call PrintingSubroutine (oDict(RS2!soa_month))
    RS2.MoveNext
Wend
 
Share this answer
 
v2
Comments
Member 12609377 24-May-18 22:45pm    
Hi! Thanks a lot! it worked! but there i think there is another problem in the code, where 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.

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

Exit Sub
Maciej Los 25-May-18 1:54am    
Take a look at your code... WHere is an instruction to open file and where is an instruction to close it? In other words, you're opening file inside a loop, but you're closing it outside the loop... That's why i suggested you to move priint instructions into another subroutine.
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.

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