Passing current row as an argument?

[Laurel]

I want to write a complex function that references a bunch of columns in the
database for a given row. I would like not to have to do a second select on
that row inside the function (using the key as an argument). Is it possible
to pass the whole current row as a parameter? I could do it either from a
query or from a form/report.

I tried looking up the definitions for likely candidates in the list you see
after you type "as" in the function definition, but it was bewildering.

 

[Klatuu]

I don't know if there is an object type that will do that for your. The
easiest way I can think of is to pass each field value ByVal in the
function's arguments.

 

[Laurel]

Why byVal instead of the default by reference?

I don't know if there is an object type that will do that for your. The
easiest way I can think of is to pass each field value ByVal in the
function's arguments.

 

[Klatuu]

Safer. By reference points to the existing item. Byval passes a value not
attached to the item.
I am not sure of this, and have not tested it, but since the data item is a
field in a row of a recordset, it may confuse Access and throw an error.

 

[J. Goddard]

Hi -

I have not tried this, but it might work -

Create a recordset of the row you want, and then pass the reference to
that recordset as a function parameter:

Set rst=db.openrecordset(...)
x=MyFunction(rst)

and the function header is:

Function MyFunction(RstIn as recordset) as FunctionType

Can't see why that would not work, except that the function would be
restricted to recordsets containing the same field names used by the
function.

I'd be interested to know if that works!

John

 

[Klatuu]

Out of curiousity I did a little testing.
It does, in fact work, but has to be ByVal

Public Function TestForRst(ByVal rst As Recordset)

What happens is you are passing a reference to the recordset object.

 

[Laurel]

Thanks to all of you. In the end, it turned out I needed to do it in the
Query, so this approach won't work for me here, but it's a good thing to
know. Now. Once you get the dataset passed to your function, what is the
best way to choose the row? Pass the row number?

 

[Klatuu]

Row numbers in Access are meaningless.
When you pass the recordset to the function, the recordset is positioned
where it is when you call the query. You can either position it before
calling the query or pass a value to the query and use the FindFirst method
to position it within the query. This means, of course, that when the
function has completed, it will be positioned where the function put it. You
are working with the exact same recordset in both places.