Record Set Question

[Sash]

I am currently creating a recordset as follows:

strSQL = "SELECT * from CHospital ORDER BY ID"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Close #1
Open "u:\interfaces\uif\" & Format(Time, "HHMMSS") & "_" & strOrigFile
For Output As #1
strClientNum = Right(rs.Fields("ClientCode"), 3)

I then have a program that takes the recordset and I create a file
containing a file header row with client information, a header row with
patient identifier and detailed patient information on the lines following.
I loop through creating a separate header row for each patient.

I just received and will be receiving in the future a file with two clients
in the same file. I'm wondering the best way around this.

I had thought about adding and if statement, but it seems to clumsy.

If strClient = strClient then
**do everything**
Else
Close the output file, open a new output and proceed


Maybe a loop until strClient <> strClient
then close the outputfile and open a new output and proceed

Here's the file header code and any suggestions would be appreciated.

'************* FH - FILE HEADER **************************
Dim FH As String
FH = "FH" & Chr(9) & Date & Chr(9) & Time() & Chr(9) & strClient &
Chr(9) & "0"
Print #1, FH
rs.MoveFirst
Do While Not rs.EOF
On Error Resume Next

** this is followed by the Header Row with Patient data and many other
segments**

 

[Steve Sanford]

Hi Sash,

Below is some code I threw together - a little of mine and a little of
yours. I wish I could have done a better job, but without examples of the
input recordset records, what the output file (is it a text file?) should
look like, what the differences are between a file header row with client
information, a header row with
patient identifier and detailed patient information, I am flying blind.

Maybe the following will get you moving again:
(watch for line wrap)

'************* FH - FILE HEADER **************************
Dim db As Database
Dim rs As DAO.Recordset

Dim FH As String
Dim WF As Integer 'write file
Dim strCurrentClient As String

'open a recordset
strSQL = "SELECT * from CHospital ORDER BY ID"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

'check for records
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Else
'no records
MsgBox "NO REcords"
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
End If

Do While Not rs.EOF
'gets the next file handle
WF = FreeFile
Open "u:\interfaces\uif\" & Format(Time, "HHMMSS") & "_" & strOrigFile
For Output As #WF

strClientNum = Right(rs.Fields("ClientCode"), 3)

'write the header line
FH = "FH" & Chr(9) & Date & Chr(9) & Time() & Chr(9) & strClient &
Chr(9) & "0"
Print #WF, FH

strCurrentClient = rs!ClientCode

'loops thru records for each client
Do While strCurrentClient = strClient
'On Error Resume Next

'create client info line here
FH = rs!id & " , " & rs!ClientCode
Print #WF, FH

rs.MoveNext

'Check if strCurrentClient = rs!ClientCode
If Not rs.EOF Then
strCurrentClient = rs!ClientCode
End If

Loop

'close text file
Close #WF

Loop
End Sub
'-------------------------------------------------------

HTH