Direct answer regarding Recordsets:
For Recordset objects used by Access (DOA -the default used by Access, or ADO) the short answer is "No". Regrettably there is no concept of a "Row" object in these older data models, so you cannot pass all values of a particular row independently of the Recordset.
You could pass an entire Recordset object once it is positioned at a particular record. A function could retrieve the necessary values (via the Fields collection) then leave the Recordset unaltered so that the calling function could continue looping through all the rows.
UPDATED Recommendation after comments
Honestly, I would try eliminating the stored calculated values if the system proves efficient enough to do all calculations dynamically. The only way to determine this is to try it. If it proves much too slow (which can honestly become much more annoying than the current system), then I would revert back to a stored, calculated value with a recalculate button and all.
Since the calculated value is not dependent upon other database or external queries, it should be efficient enough to use a parameterized function as you have already written. The benefit to such a function is its flexibility: It can be called from another function and method, from within a query, or from a form control element.
(FYI: Consideration of additional database and/or external queries is important, because if it were dependent upon other query services, these can often require proper connection management, and/or take a long time, etc. This often complicates the solution and can justify the alternatives.)
You could also create another function that takes just the primary key (e.g. ID) for a given record. It then queries the database tables directly just for the given ID value, then calls the original function using values retrieved from the recordset. Such a function can be useful when the full record is not immediately available or convenient. But be careful since using this in a query of thousand or records can freeze Access since it is repeatedly opening and closing a recordset for each function call, often much slower than passing parameters.
'* Assume the original function is RedemptionEstimate(p1, p2, ...)
Public Function RedemptionEstimateFromID(vID as Variant)
If Not IsNumeric(vID) Then
'* Avoid problems with null (and SQL injection concerns)
'* Consider other error handling code
RedemptionEstimateFromID = Null
Exit Sub
End If
Dim db As Database
Dim rs As Recordset2
On Error Goto catch
Set db = CurrentDb '* Not critical for a single recordset, but good habit if performing multiple queries
Set rs = db.OpenRecordset("SELECT * FROM DataTable WHERE ID=" & vID)
If rs.EOF Then
'* Assuming ID is unique primary key, rs has only one record
'* Call original function, passing values from query as parameters
RedemptionEstimateFromID = RedemptionEstimateFrom(rs!Col1, rs!Col2, ...)
Else
'* ID not found... Consider other error handling code
RedemptionEstimateFromID = Null
End If
rs.Close '* Would be closed automatically, but good habit to close explicitly
Exit Function
catch:
'* ID not found... Consider other error handling code
Debug.Print "Error " & err.number & ": " & err.Description
RedemptionEstimateFromID = Null
End Function
When displaying on a form with thousands of records, it can be a serious waste to calculate dynamic values for all records in the query. Instead, the value can be calculated just for the displayed record(s). One drawback to this can be that such a field can't be used to filter the query. To calculate the value for the currently-displayed record, add a new control to the form and its Control Source property, add a call to your function passing from values as parameters: = RedemptionEstimate([Col1], [Col2], ...).
Regarding alternatives:
Indeed, this approach is discouraged for volatile values. However, it can also be a valid technique if it is known that values seldom change. In that case, a static table field can be much more efficient and easier to work with than repeatedly recalculating values dynamically. This technique requires a well-established and reliable update process and schedule. It might seem annoying now, but if you could automate such a process that runs outside of normal times it could still be a reasonable approach. Consider answering these questions rather than basing it off the "annoyance" factor :)
- Do these values change for all records on the same schedule?
- Does recalculating values dynamically slow down queries and related user interfaces?
- Could an update process be automated to run outside times that effect other workflows?
- Do you often experience outdated values for particular records with the current process?
- Do such outdated values have seriously negative effects?
To answer this question fully and properly requires that you first specify the requirements and behavior of the form. Do you need to update the source-table values or only view them? Will the form load only a few records at a time or all 10,000 that you mentioned? How long does it take to calculate the "RedemptionEstimates"--for a single row, for the entire set of records?
Creating a temporary table is a viable alternative, but if you plan on updating values back to the original table, you must ensure proper indexes and relationships are defined, etc. Worst case you need to then write extra code to update the tables. But I'd wait to recommend details until you answer the other questions.
Other Comments:
Passing values into a function from a query is a legitimate technique. Perhaps 13 parameters is a lot and it is good that you're exploring possibilities. However, you should be able to resolve the #Error values with proper null handling and debugging. Don't give up on this technique since it might prove to be the best solution.
Sometimes it is necessary to explicitly loop through each row of a recordset, but don't get caught up in loops. The proper technique is to bind a record source to a form or report. There is no need to loop through and explicitly "print" each record on a form. Access is built to do such tasks for you. If you specify a Row Source for the form (or report), the form will automatically open the query and create its own Recordset behind the scenes.
(I discourage explicitly creating a Recordset then binding it to the form yourself. Access technically allows this, but it is buggy. Trying to create and update an empty field in an existing recordset also complicates things and I think requires ADO Recordsets instead of the default DAO. In other words, I suggest not going down this path.)
