Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 28th, 2013, 03:32 PM
Registered User
 
Join Date: Oct 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Writing out text file

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

Last edited by maxidog; October 28th, 2013 at 03:35 PM..
 
Old October 28th, 2013, 08:43 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old October 28th, 2013, 09:26 PM
Registered User
 
Join Date: Oct 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

I get a loop without a do error
 
Old October 29th, 2013, 01:18 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old October 29th, 2013, 08:45 AM
Registered User
 
Join Date: Oct 2013
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default write file to text file

Boyd,

Thank you... This is just what I needed.
 
Old October 30th, 2013, 01:37 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

You're welcome. Glad we could assist.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015





Similar Threads
Thread Thread Starter Forum Replies Last Post
Writing to Text File Gary108 BOOK: Visual Basic 2010 Programmer's Reference 5 December 31st, 2011 05:13 PM
Writing to Text File striker9 ASP.NET 2.0 Basics 1 May 12th, 2006 05:17 AM
writing text file but in an order specifed... stone XSLT 2 October 27th, 2005 05:17 AM
Reading from a text file and writing to a Binary 1 scott01 C++ Programming 0 July 28th, 2003 03:59 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.