Reusable Code Question

[David M C]

This recursive code works perfectly, and is almost entirely generic, except
for the line:

SumNodesBelow = SumNodesBelow + SumNodesBelow(rs!WorksID, TreeTable,
SumTable, SumField, LinkField)

where obviously, the reference to WorksID would depend on it existing in the
recordset open. Any ideas how I can alter this to make it entirely reusable?
LinkField is a String identifying the Primary Key - Foreign Key field in a
related table, and it is this field that is currently identified by
rs!WorksID.

Basically, I'm asking how dynamically reference fields in a recordset at
runtime, based on a parameter passed ina function. Also, if there's anything
in the code that could be made more efficient, I'd like to know. It gets
called recursively and could ptotentially operate on 1000+ records.

Here's the full code, beware of line breaks:


Function SumNodesBelow(ByVal TreeNode As Integer, ByVal TreeTable As String,
ByVal SumTable As String, ByVal SumField As String, ByVal LinkField As
String) As Currency

Dim db As Database
Dim rs As Recordset

If (HasChild(TreeNode, TreeTable)) Then

Set db = CurrentDb()
Set rs = db.OpenRecordset( _
"SELECT * " & _
"FROM JobsContWorks " & _
"WHERE JobsContWorks.ParentID=" & TreeNode & " " & _
"ORDER BY JobsContWorks.OrderID", dbOpenSnapshot, dbReadOnly Or
dbForwardOnly)

Do While Not rs.EOF
SumNodesBelow = SumNodesBelow + SumNodesBelow(rs!WorksID,
TreeTable, SumTable, SumField, LinkField)
rs.MoveNext
Loop

Else
SumNodesBelow = SumNodesBelow + Nz(DSum(SumField, SumTable,
LinkField & " = " & TreeNode), 0)
End If

End Function


Thanks,

Dave

 

[Brendan Reynolds]

rs.Fields(FieldName)

Where 'FieldName' is the name of a string variable containing the name of
the field.

 

[David M C]

Thanks, didn't think it would be that simple.

Dave