How do I access other fields in the current row in a query from a custom VBA function?

Question

I would like to write some custom VBA functions to perform calculations on particular fields in a set of queries. This is what I currently have:

Public Function TVM(LEN_SEC As Double, ADT_CUR As Integer, TRK_PCT As Integer)
    TVM = LEN_SEC * ADT_CUR * TRK_PCT / 100#
End Function

This works fine, but when I create the query I have to do something like this:

SELECT TVM([LEN_SEC], [ADT_CUR], [TRK_PCT]) AS TRK_VMT
FROM System;

I would prefer to do this instead:

SELECT TVM()
FROM System;

Is there a way to access the current row being output by the query from within the VBA function? Something like this:

Public Function TVM(LEN_SEC As Double, ADT_CUR As Integer, TRK_PCT As Integer)
    TVM = CurrentRow.Fields("LEN_SEC") * CurrentRow.Fields("ADT_CUR") * CurrentRow.Fields("TRK_PCT") / 100#
End Function

These calculations are frequently used in time-sensitive custom generated reports, by several users of varying skill level and attention spans - the simpler I can make the interface for them, the better it is for our QA team.

Thanks!

Answer 1

There is no way to do what your example demonstrates.

Probably the most performance approach would be to avoid the function altogether since it only uses simple operators.

For example:

SELECT [LEN_SEC] * [ADT_CUR] * [TRK_PCT] / 100 as TVM
FROM System

Answer 2

Users with limited skills would be better off creating their reports from queries than tables. Put your function with all the parameters in the query.

Select TVM from qrySystem;

Or teach them how to use the Expression Builder in the Query Designer and make sure they replace the <> with actual field names.