Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2013
    Posts
    9

    Unanswered: Calculate Value Using Previous Record Data

    Hi Everyone,

    I'm looking for some help with referring to a previous record in a form and performing calculations with that value.

    I've uploaded a sample DB to better understand what I refer to.

    In the form, a user can adjust the "Percent" cell percentage to what they like. The neighboring fields, "Contributions" and "Interest" automatically recalculate new values based on the user input for the "Percent" field.

    The "Fund Balance" value is the SUM of the previous record's "Fund Balance", previous "Contributions" and previous "Interest" fields.

    How can I get the "Fund Balance" field to automatically recalculate using data from the previous record's form calculation???

    I am a noob so any help is appreciated, thanks.
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use:
    Code:
    Dim rst As DAO.RecordSet
    Set rst = Me.RecordSetClone
    rst.bookmark = Me.Bookmark
    rst.MovePrevious
    At this point, rst is a RecordSet with its current row (record) containing the row previous to the current row of the Form. To retrieve the values from this previous row, you can the use:
    Code:
    PreviousFundBalance = rst![Fund Balance]
    PreviousContributions = rst!Contributions
    PreviousInterest = rst!Interest
    Have a nice day!

  3. #3
    Join Date
    Jan 2013
    Posts
    9
    Code:
    Dim rst As DAO.RecordSet
    Set rst = Me.RecordSetClone
    rst.bookmark = Me.Bookmark
    rst.MovePrevious
    Would this code be placed in the "After Update" field?
    If not, where would it go?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can use the code wherever you need to retrieve the values from the previous record. In this case, in the procedure where you want the "Fund Balance" field to be automatically recalculated.
    Have a nice day!

  5. #5
    Join Date
    Jan 2013
    Posts
    9
    So this goes under the "Control Source" on the property sheet?

  6. #6
    Join Date
    Jan 2013
    Posts
    9
    So under the Fund Balance, "After Update", I have the following code:

    Code:
    Private Sub RunningBalance_AfterUpdate()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Bookmark
    rst.MovePrevious
    
    PreviousFundBalance = rst![RunningBalance]
    PreviousContributions = rst!AnnualContribution
    PreviousInterest = rst!InterestIncome
    
    Me = PreviousFundBalance + PreviousContributions + PreviousInterest
    
    End Sub
    Unfortunately, none of the values are updating...please forgive me, I'm not very good with MS Access and am really trying to understand where to put everything to get it to work.

    Thanks again

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No: if you want to perform this calculation for each record, as it appears on the form, use the Form_Current event handler procedure (air code):
    Code:
    Private Sub Form_Current()
    
        Dim rst As DAO.RecordSet
        Dim PreviousFundBalance As Variant
        Dim PreviousContributions As Variant
        Dim PreviousInterest As Variant
    
        Set rst = Me.RecordSetClone
        rst.bookmark = Me.Bookmark
        rst.MovePrevious
        If rst.BOF = False then
            PreviousFundBalance = rst![Fund Balance]
            PreviousContributions = rst!Contributions
            PreviousInterest = rst!Interest
        End If
        rst.Close
        Me![Fund Balance].Value = PreviousFundBalance + PreviousContributions + PreviousInterest
        Set rst = Nothing
    
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Jan 2013
    Posts
    9
    WOW, that works amazing, first time Ive seen the numbers change in the right direction!

    Thank you.

    One last question, the values update, but I need to click on the next records row for the Fund Balance to update. Is there any way to have the next row update when someone enters a different Percent Value as well?

    Thank you so much!

  9. #9
    Join Date
    Jan 2013
    Posts
    9
    The .BOF function sets the first record's value to zero - I think..... Is there a way around this? Usually the balance will start with something, a value provided in another form. Can this be maintained ?

  10. #10
    Join Date
    Jan 2013
    Posts
    9
    Ok, I've tried the following but am getting an error saying Access does not recognize my form? I swear Im putting it in correctly.
    Code:
    Private Sub Form_Current()
    
    
        Dim rst As DAO.Recordset
        Dim PreviousFundBalance As Variant
        Dim PreviousContributions As Variant
        Dim PreviousInterest As Variant
    
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        rst.MovePrevious
        If rst.BOF = True Then
            Me![RunningBalance].Value = Forms!ReserveParameters!StartingBalanceValue
        Else
            PreviousFundBalance = rst![RunningBalance]
            PreviousContributions = rst!AnnualContribution
            PreviousInterest = rst!InterestIncome
        End If
        rst.Close
        Me![RunningBalance].Value = PreviousFundBalance + PreviousContributions + PreviousInterest
        Set rst = Nothing
        
    End Sub

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by umchem View Post
    Ok, I've tried the following but am getting an error saying Access does not recognize my form? I swear Im putting it in correctly.
    Code:
    Private Sub Form_Current()
    
    
        Dim rst As DAO.Recordset
        Dim PreviousFundBalance As Variant
        Dim PreviousContributions As Variant
        Dim PreviousInterest As Variant
    
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        rst.MovePrevious
        If rst.BOF = True Then
            Me![RunningBalance].Value = Forms!ReserveParameters!StartingBalanceValue
        Else
            PreviousFundBalance = rst![RunningBalance]
            PreviousContributions = rst!AnnualContribution
            PreviousInterest = rst!InterestIncome
        End If
        rst.Close
        Me![RunningBalance].Value = PreviousFundBalance + PreviousContributions + PreviousInterest
        Set rst = Nothing
        
    End Sub
    1. This can only work if the form ReserveParameters is open too.

    2. It would be safer to retrieve the value from the table or query that provides the data for Forms!ReserveParameters!StartingBalanceValue. A simple DLookUp function could be used. The sytax is:
    Code:
    Value = DLookUp("Name of the column (or field)", "Name of the Table (or query)", "Criteria (optional, e.g. "RowId = 1")")
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •