Functions and Recordsets

[Guest]

Can I pass a recordset to a function?

 

[Marshall Barton]

Can I pass a recordset to a function?

Sure.

Create the function:

Function MyFunc(rs As Recordset)
. . .
End Function

The call it with something like:

Set rsOrders = OpenRecordset("Orders", dbOpenDynaset)
xx = MyFunc(rsOrders)

 

[Dirk Goldgar]

In

Can I pass a recordset to a function?

I don't see why not, though usually the problem can be solved more
easily by a carefully crafted query. But you could do something like
this:

'----- start of meaningless example code -----
Sub TestSub()

Dim rs As DAO.Recordset
Dim lngResult As Long

Set rs = CurrentDb.OpenRecordset("MyTable")

lngResult = MyFunction(rs)

MsgBox "Result = " & lngResult

End Sub

Function MyFunction(prs As DAO.Recordset)

Dim lngValue As Long

With prs
Do Until .EOF
If rs!SomeField > 0 Then
lngResult = lngResult + rs!SomeField
End If
.MoveNext
Loop
End With

MyFunction = lngResult

End Function
'----- end of meaningless example code -----

 

[A a r o n K e m p f]

you need A c c e s s D a t a P r o j e c t s

 

[Guest]

Thanks Dirk,
I thought a lot about the query route, but couldn't get it to work for my
problem. I have a query that returns a number of components to build a
project (electronic stuff). I have a table with a price break for the
components, and I need to generate a field in the query which gives the
correct price for the quanity I need. I tried domain aggregate functions,
subqueries as criteria, et cetera, but I never could figure it out...