Pass Recordset to Sub 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!

 

[Quehay]

This shouldn't give you trouble (assuming that you've just omitted the code that opens the recordset) --I pass RST's all the time as parameters. You might want to clarify though with (ByRef RsClaim as rst, ByRef Pat as string).

But think about it--you're not really passing the recordset, but rather the fields for a current record (you're looping through RS in calling sub). So it would be better practice to get field values into variables and pass those as arguments--I think if you pass a Rst to a sub, the sub has no bookmark to know what current record is.

If you're using the same Rst fields in multiple calls, getting them into variables instead of repetitive reads of Rst.Fields will be more efficient and make for easier-to-read code.

I typically declare as function with a boolean return in this type of usage so that I can respond/handle errors in the function at the call level.


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[VBslammer]

A function can hold 60 arguments:

Function MaxArgs(strArg01, strArg02, strArg03, strArg04, strArg05, _
                strArg06, strArg07, strArg08, strArg09, strArg10, _
                strArg11, strArg12, strArg13, strArg14, strArg15, _
                strArg16, strArg17, strArg18, strArg19, strArg20, _
                strArg21, strArg22, strArg23, strArg24, strArg25, _
                strArg26, strArg27, strArg28, strArg29, strArg30, _
                strArg31, strArg32, strArg33, strArg34, strArg35, _
                strArg36, strArg37, strArg38, strArg39, strArg40, _
                strArg41, strArg42, strArg43, strArg44, strArg45, _
                strArg46, strArg47, strArg48, strArg49, strArg50, _
                strArg51, strArg52, strArg53, strArg54, strArg55, _
                strArg56, strArg57, strArg58, strArg59, strArg60)
      
End Function

But, if I use a ParamArray() I can pass 2037 args before an error occurs:

Function paramArgs(ParamArray myArray() As Variant)
    Debug.Print UBound(myArray) & " arguments."
End Function

Passing the recordset ByRef will work fine, and you could use a function instead of a sub to return the string:

Public Function Patient(ByRef rsClaim As ADODB.Recordset) As String
  With rsClaim
    Patient = Trim(.Fields("firstname")) & _
              Trim(.Fields("lastname")) & _
              "CA11155~"
  End With
End Function

Sample usage:

  Do While not rsClaim.EOF
    Outfile.write Patient(rsClaim)
    rsClaim.MoveNext
  Loop

VBSlammer

"You just have to know which screws to turn." - Professor Bob