Passing Recordset 1

[GROKING]

Hello all,

I am having some trouble passing a recordset to a sub. I want to pass the recordset to Sub Patient, then pass the string PAT back up and write out the results, then loop through the recordset and do the same for all rows. I can do this if I list each datafield in the Call Patient () function, but I actually have 12 datafields to pass so I thought it would be easier to pass the entire recordset.
Here is some of the code as an example. Also, I just want to write out each row from the recordset on a different line one because I have three other Subs just like this one to write out as I loop through.

Public Sub Main()
Dim rsClaim as new adodb.recordset
Dim PAT as String
Dim Qst1 as String

Qst1 = "select firstname, lastname, ID, Region from Clients;"

rsClaim.MoveFirst

Do While not rsClaim.EOF

Call Patient(rsClaim, PAT)
Outfile.write PAT
rsClaim.MoveNext

Loop
End Sub

Public sub Patient(rsClaim as adodb.recordset, PAT)
Dim FName as String, LName, State, ZIP, PAT as String

FName = trim$(rsClaim!firstname)
LName = trim$(rsClaim!lastname)
State = "CA"
ZIP = "11155"

PAT = FName & LName & State & ZIP & "~"

End Sub

Can anyone tell me if this is possible without listing 12 datafields in the call like this:

Call Patient(rsClaim!firstname, rsClaim!lastname, rsClaim!City, rsClaim!Divison, rsClaim!Phone, rsClaim!Cell) etc...

Also, is there a limit to the number of variables you can pass?

Thanks for the help!! Have a good day at work too!

 

[Sheco]

Yes it is possible.

But.....

Its not such a good idea to be passing around the recordset that also happens to be your loop control variable.

To simplify even more, you can just go thru the .Fields collection of the recordset to build your output string so that you don't even have to call any sub at all. Like this:

    Dim oField As ADODB.Field
    Do While Not rsClaim.EOF
        'clear PAT each time thru the loop:
        PAT = ""
        
        'build PAT so that each field is separated by a comma
        For Each oField In rsClaim.Fields
          PAT = PAT & oField.Value & ","
        Next
        
        Outfile.write PAT
        rsClaim.MoveNext
    Loop

Well in the above code I cut off the first part of your code so you would still need that. This suggestion is only a change to your loop that lets you remove the function call and also handles all the fields in your recordset.