Ms-access – pass a single record from a recordset to a module

ms access

I am creating a module, RedemptionEstimate, in MS Access 2016 that requires 13 parameters to be passed to it and calculates how much is owed on delinquent property taxes. Some of the parameters can be null and will be handled appropriately within the module. I tried the clunky route of passing all the parameters from a function call within a query, but the null values are resorting in #Error in the returned query field.

I then decided to read up on Recordsets and this seems like a better solution as the module will typically be called from reports and forms based on a query. My understanding is that a recordset can be defined by a query and created within an event call on a report or form. My plan is to create the recordset within an OnLoad Event then parse through the recordset with a Do..Until with a module call within the loop.

Is it possible to pass just one record from the recordset to the module? If not, I'm open to other suggestions.

Other possible solutions I've considered:

  1. Add an additional field to the original table and recalculate the redemption value. – I don't like this solution because the value changes every few months and it's how the old system does it and it's annoying to have the system recalculate 10,000s of records on a regular basis. It also seems unnecessary.
  2. Is is possible to add an empty field to the recordset and just pass the entire recordset to the module then add the calculated value to that field? – Intuitively this feels like I'd have to run through the recordset twice in the OnLoad Event, once to calculate the values and a second time to "print" the data to the report or form.

Best Answer

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:

  1. 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?

  2. 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.)

Related Question