Writing out text file

maxidog · October 28th, 2013, 03:32 PM

I am new to Access 2010 and trying to write out a text file.
my text file is like this

pat info
123 a
123 b
123 c
222 b
222 d
333 f
333 g
333 h

i am trying to write a file for each pat with the info so that my file for 123 would contain thre lines.

my code is this and i am only getting the last line (123 c, 333 h). Any help would be appreciated

Public Function TextOutput()

Dim rs As DAO.Recordset
Dim sPath As String
sPath = "C:\temp\" ' just have the directory here
Set rs = CurrentDb.OpenRecordset("HL7 Outbound Final")
Do Until rs.EOF
Open sPath & rs("Patno") & ".hp" For Output As #1
PatNo = rs("Patno")
hl7out = rs("HL7")
Print #1, PatNo, hl7out
Close #1
rs.MoveNext
Loop
Close #1
rs.Close
Set rs = Nothing

End Function

HiTechCoach · October 28th, 2013, 08:43 PM

You're very close. You only want o open and close the output file when you have found the next PatNo.

I also updated the code to use FreeFile to get the next available file handler number. It is better than assuming #1 is available.

Code:

Public Function TextOutput()

Dim rs As DAO.Recordset
Dim sPath As String

Dim FH as Integer

Dim sLastPatNo As String

sPath = "C:\temp\" ' just have the directory here
Set rs = CurrentDb.OpenRecordset("HL7 Outbound Final")

sLastPatNo  = ""

Do Until rs.EOF
    ' test to see if a new patient or the first one

    If sLastPatNo  = "" then 
        ' this is the first time so open the file
          FH = Freefile  ' get available file handle
          Open sPath & rs("Patno") & ".hp" For Output As #FH 
    Else
      If sLastPatNo  <> rs("Patno") then 
         ' next PatNo found 
         ' close file currently opened  and open a New file
          Close #FH
          ' open output file for next PatNo
          FH = Freefile  ' get available file handle
          Open sPath & rs("Patno") & ".hp" For Output As #FH
           

    End IF

    sLastPatNo  = rs("Patno")  ' Save the PatNo
    PatNo = rs("Patno")
    hl7out = rs("HL7")
    Print #FH, PatNo, hl7out
   
    ' store the current PatNo
    sLastPatNo  = rs("Patno")  
    

    rs.MoveNext
Loop

Close #FH
rs.Close
Set rs = Nothing

End Function

maxidog · October 28th, 2013, 09:26 PM

Im sorry, but I dont see the issue with the loop. It looks correct to me.

I get a loop without a do error

HiTechCoach · October 29th, 2013, 01:18 AM

Oops .... I left out an End If.

Try:

Code:

Public Function TextOutput()

Dim rs As DAO.Recordset
Dim sPath As String

Dim FH as Integer

Dim sLastPatNo As String

sPath = "C:\temp\" ' just have the directory here
Set rs = CurrentDb.OpenRecordset("HL7 Outbound Final")

sLastPatNo  = ""

Do Until rs.EOF
    ' test to see if a new patient or the first one

    If sLastPatNo  = "" then 
        ' this is the first time so open the file
          FH = Freefile  ' get available file handle
          Open sPath & rs("Patno") & ".hp" For Output As #FH 
    Else
      If sLastPatNo  <> rs("Patno") then 
         ' next PatNo found 
         ' close file currently opened  and open a New file
          Close #FH
          ' open output file for next PatNo
          FH = Freefile  ' get available file handle
          Open sPath & rs("Patno") & ".hp" For Output As #FH
           
       End If
    End IF

    sLastPatNo  = rs("Patno")  ' Save the PatNo
    PatNo = rs("Patno")
    hl7out = rs("HL7")
    Print #FH, PatNo, hl7out
   
    ' store the current PatNo
    sLastPatNo  = rs("Patno")  
    

    rs.MoveNext
Loop

Close #FH
rs.Close
Set rs = Nothing

End Function

maxidog · October 29th, 2013, 08:45 AM

Boyd,

Thank you... This is just what I needed.

HiTechCoach · October 30th, 2013, 01:37 PM

You're welcome. Glad we could assist.