1

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

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

  • Thank you for responding. To answer your question one: 1. There are subsets of certificates that are on the same schedule. 2. In the old system, yes, it can take up to a minute or longer for all records to update, but it is also a 20 year old pc running Win95. Might not be as noticeable on something from the 21st century. Haven't gotten far enough into this project to determine. 3. Automating calculations overnight could be a workable solution. 4 & 5 I don't normally run into problems with outdated values and there is a recalculate button when I do. No big problem though. – megruder79 Aug 11 '17 at 21:06
  • For question two: The form would load all records, which we are now adding an average of 1000 each year. For most purposes, the calculated data from the module is just for viewing but there are cases when that information would change based on new data entered. Our old system required us to click the re-calculate button. With the new system I'm working on I don't know how long it would take as I'm not working with full datasets at this time. – megruder79 Aug 11 '17 at 21:10
  • Are your 13 parameters sufficient to calculate the values without querying other parts of the database or querying external services (like a webservice)? – C Perkins Aug 11 '17 at 21:39
  • I realized that I needed a couple more and one needs to be user entered for the reports, but yes that is all I need from the original table. – megruder79 Aug 11 '17 at 21:42
  • Thank you for your response. I'm finally getting back to this project and I've decided to just send the 14 parameters individually to the function. The call itself will be a little unwieldy but it will make the module more versatile, which is what I'm trying to achieve. Working on another problem resolved the original problem I was having with just passing multiple parameters. Sending null values was giving me errors, resolved that by declaring them as variants in the function definition then handling the conversion within the function. It all seems to be working now. Thank you again. – megruder79 Aug 17 '17 at 21:46
0

Prior to CPerkins reponse I came up with the following solution but I'm still having some problems as follows:

  1. The query is intended to ask for a redemption date but I get an error. I believe the OnLoad Event calls the module before asking for the redemption date. I've ignored that problem by just getting the current date within the module but that will need to be addressed. Suggestions?

  2. The Unbound textbox for all records fills with the same data as it cycles through each record. I don't know how, or if it's even possible, to bind the textbox to a function result.

  3. I will also be calling this from a form that will flip through all records and I'm unsure how to send just the current record to the module.

Below is my code and I fiddled with the formatting but I'm just done for the week. Sorry.

[code]

Private Sub Report_Load()

Dim dbsTrustee As DAO.Database
Dim rstInventory As DAO.Recordset
Dim varRedeemRecord As Variant
Dim curRedemptionEstimate As Currency

Set dbsTrustee = CurrentDb
Set rstInventory = dbsTrustee.OpenRecordset("InventoryDetailQuery", dbOpenSnapshot)

Do Until rstInventory.EOF
    varRedeemRecord = rstInventory.GetRows(1)
    curRedemptionEstimate = RedemptionEstimate(varRedeemRecord)
    Me.txtRedemptionAmt = curRedemptionEstimate
Loop

End Sub [/code]

Function NumMonths(StartDate As Date, EndDate As Date, ExpiredDate As Date) As Long

'-------------------------------------
' Test for redemption after expiration
'-------------------------------------

If DateDiff("d", EndDate, ExpiredDate) >= 0 Then
    ' DateDiff determines number of whole months that have passed
    ' Second half of expression determines if partial month then adds to NumMonths
    NumMonths = DateDiff("m", StartDate, EndDate) + (Day(StartDate) >= Day(EndDate))
Else
    ' Set date to expiration if redemption after expire date
    NumMonths = DateDiff("m", StartDate, ExpiredDate) + (Day(StartDate) >= Day(ExpiredDate))
End If

End Function

Function RedemptionEstimate(RedeemRecord As Variant) As Currency

' For values from passed recordset
Dim FaceAmount As Currency
Dim DateSold As Date
Dim CertRate As Single
Dim Sub1Amount As Currency
Dim Sub1TempDate As Variant 'Variant to handle null date values
Dim Sub1Rate As Single
Dim Sub2Amount As Currency
Dim Sub2TempDate As Variant 'Variant to handle null date values
Dim Sub2Rate As Single
Dim Sub3Amount As Currency
Dim Sub3TempDate As Variant 'Variant to handle null date values
Dim Sub3Rate As Single
Dim ExpTempDate As Variant 'Variant to handle null date values

' Storage for dates after testing for null
Dim Sub1Date As Date
Dim Sub2Date As Date
Dim Sub3Date As Date
Dim ExpDate As Date

' Calculated values
Dim RedeemDate As Date
Dim CertNumMonths As Long
Dim CertIntervals As Integer
Dim CertPenalty As Currency
Dim Sub1NumMonths As Long
Dim Sub1Intervals As Integer
Dim Sub1Penalty As Currency
Dim Sub2NumMonths As Long
Dim Sub2Intervals As Integer
Dim Sub2Penalty As Currency
Dim Sub3NumMonths As Long
Dim Sub3Intervals As Integer
Dim Sub3Penalty As Currency

' Assignment of passed values
FaceAmount = RedeemRecord(3, 0)
DateSold = RedeemRecord(4, 0)
CertRate = RedeemRecord(5, 0)
Sub1Amount = RedeemRecord(6, 0)
Sub1TempDate = RedeemRecord(7, 0)
' Test for null date and assign default value
If (Not IsNull(Sub1TempDate)) Then
    Sub1Date = CDate(Sub1TempDate)
Else
    Sub1Date = CDate("1/1/1901")
End If
Sub1Rate = RedeemRecord(8, 0)
Sub2Amount = RedeemRecord(9, 0)
Sub2TempDate = RedeemRecord(10, 0)
' Test for null date and assign default value
If (Not IsNull(Sub2TempDate)) Then
    Sub2Date = CDate(Sub2TempDate)
Else
    Sub2Date = CDate("1/1/1901")
End If
Sub2Rate = RedeemRecord(11, 0)
Sub3Amount = RedeemRecord(12, 0)
Sub3TempDate = RedeemRecord(13, 0)
' Test for null date and assign default value
If (Not IsNull(Sub3TempDate)) Then
    Sub3Date = CDate(Sub3TempDate)
Else
    Sub3Date = CDate("1/1/1901")
End If
Sub3Rate = RedeemRecord(14, 0)
ExpTempDate = RedeemRecord(15, 0)
' Test for null date and assign default value
If (Not IsNull(ExpTempDate)) Then
    ExpDate = CDate(ExpTempDate)
Else
    ExpDate = CDate("12/12/2099")
End If

RedeemDate = Date

' Calculate number of rollovers since purchase and round up
CertNumMonths = NumMonths(DateSold, RedeemDate, ExpDate)
CertIntervals = -Int(-((CertNumMonths + 1) / 6))

' Test for rollovers within normal range
If (CertIntervals <= 6) And (CertIntervals >= 1) Then
    CertPenalty = FaceAmount * CertRate * CertIntervals
' Handles cases that exceed upper bound
ElseIf (CertIntervals > 6) Then
    CertPenalty = FaceAmount * CertRate * 6
Else
    ' Test for redemption on date of sale
    If (DateSold = RedeemDate) Then
        CertPenalty = FaceAmount * CertRate * 1
    Else
        ' Handles errors cases that exceed lower bound
        CertPenalty = 0
    End If
End If

' Check for purchased sub
If (Sub1Amount > 0) Then
    ' Calculate number of rollovers since purchase and round up
    Sub1NumMonths = NumMonths(Sub1Date, RedeemDate, ExpDate)
    Sub1Intervals = -Int(-((Sub1NumMonths + 1) / 12))
    ' Test for rollovers within normal range
    If (Sub1Intervals <= 3) And (Sub1Intervals >= 1) Then
        Sub1Penalty = Sub1Amount * Sub1Rate * Sub1Intervals
    ' Handles cases that exceed upper bound
    ElseIf (Sub1Intervals > 3) Then
        Sub1Penalty = Sub1Amount * Sub1Rate * 3
    Else
        ' Test for redemption on date of sale
        If (Sub1Date = RedeemDate) Then
            Sub1Penalty = Sub1Amount * Sub1Rate * 1
        Else
            ' Handles errors cases that exceed lower bound
            Sub1Penalty = 0
        End If
    End If
Else
    Sub1Penalty = 0 ' No sub purchased
End If


' Check for purchased sub
If (Sub2Amount > 0) Then
    ' Calculate number of rollovers since purchase and round up
    Sub2NumMonths = NumMonths(Sub2Date, RedeemDate, ExpDate)
    Sub2Intervals = -Int(-((Sub2NumMonths + 1) / 12))
    ' Test for rollovers within normal range
    If (Sub2Intervals <= 2) And (Sub2Intervals >= 1) Then
        Sub2Penalty = Sub2Amount * Sub2Rate * Sub2Intervals
    ' Handles cases that exceed upper bound
    ElseIf (Sub2Intervals > 2) Then
        Sub2Penalty = Sub2Amount * Sub2Rate * 2
    Else
        ' Test for redemption on date of sale
        If (Sub2Date = RedeemDate) Then
            Sub2Penalty = Sub2Amount * Sub2Rate * 1
        Else
            ' Handles errors cases that exceed lower bound
            Sub2Penalty = 0
        End If
    End If
Else
    Sub2Penalty = 0 ' No sub purchased
End If
' Check for purchased sub
If (Sub3Amount > 0) Then
    ' Calculate number of rollovers since purchase and round up
    Sub3NumMonths = NumMonths(Sub3Date, RedeemDate, ExpDate)
    Sub3Intervals = -Int(-((Sub3NumMonths + 1) / 12))
    ' Test for rollovers within normal range
    If (Sub3Intervals = 1) Then
        Sub3Penalty = Sub3Amount * Sub3Rate * Sub3Intervals
    ' Handles cases that exceed upper bound
    ElseIf (Sub3Intervals > 1) Then
        Sub3Penalty = Sub3Amount * Sub3Rate * 1
    Else
        ' Test for redemption on date of sale
        If (Sub3Date = RedeemDate) Then
            Sub3Penalty = Sub3Amount * Sub3Rate * 1
        Else
            ' Handles errors cases that exceed lower bound
            Sub3Penalty = 0
        End If
    End If
Else
    Sub3Penalty = 0 ' No sub purchased
End If

RedemptionEstimate = CertPenalty + Sub1Penalty + Sub2Penalty + Sub3Penalty

End Function

[/code]

  • FYI: It's okay to answer your own question... as long as it's an answer. If you have continuing issues, then update the original question with more detail and code. Also realize that some problems may technically be a different question, and if primarily dealing with code issues might be better suited to Stack Overflow. My intention is to be helpful, but I also encourage conforming to standard on the Stack Exchange forums. – C Perkins Aug 11 '17 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.