locked
Need to find a way to use VBA to get the value or Sum the deposits in my small database, then I want to display this in a MsgBox RRS feed

  • Question

  • Windows 7 64 bit

    Access 2013

    I am new to access and VBA!

    I have a simple database with 5 field, Two are currency fields.

    one is called Deposits the other is called Spending. This is used to

    track a budget; my first database. I have made the form for data

    entry so I can enter money into the database, that is working. The

    challenge now is that after I enter a Deposit value or enter Spending

    amount I need to see the total. The code below won’t work but I think

    it shows the logic I am applying. I need help with the syntax and use

    of VBA.

     Private Sub Deposits_LostFocus()

    Dim NewDeposit As Currency

    NewDeposit = Sum(Deposits, Groceries)  ‘groceries is the name of the table!

    MsgBox NewDeposit ‘display the value in NewDeposit

    End Sub

    Thank You!

    Harry Nash

    Thursday, March 21, 2013 7:42 PM

Answers

  • If you create a text box and set its Control Source property to =Sum([Deposits]), the text box will display the sum of all records in the form. But if the form has Data Entry set to Yes, so that the user can only enter new records, that won't work. Change the Control Source of the totals text box for deposits to

    =DSum("Deposits", "Groceries")

    and that for Spending:

    =DSum("Spending", "Groceries")

    You have to recalculate these text boxes:

    Private Sub Deposits_AfterUpdate()
        Me.Dirty = False
        Me.Recalc
    End Sub
    
    Private Sub Spending_AfterUpdate()
        Me.Dirty = False
        Me.Recalc
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Dummy yoyo Friday, March 29, 2013 1:10 PM
    Friday, March 22, 2013 11:01 AM

All replies

  • You can place a text box in the form header or form footer with Control Source

    =Sum([Deposits])

    and another one with Control Source

    =Sum([Spending])

    However, when you enter a value in a text box and move to the next one, the value is stored in memory, but the record has not yet been saved to the table, so Access can't calculate the updated sum yet. When you move to another record, Access will automatically save the modified record, and the two text boxes displaying the totals will be updated.

    If you don't want to wait for that, you can save the record immediately after Deposits or Spending has been updated:

    Private Sub Deposits_AfterUpdate()
        Me.Dirty = False
    End Sub
    
    Private Sub Spending_AfterUpdate()
        Me.Dirty = False
    End Sub

    Setting the Dirty property of the form (indicating whether the record has been modified) to False saves the record. I used the After Update event instead of the On Lost Focus event, for if the user merely exits the control without having changed the value, there is no reason to save the record.

    The totals text boxes will now be updated automatically.


    Regards, Hans Vogelaar

    Thursday, March 21, 2013 8:06 PM
  • Thank you for your help; I tried to get an understanding of how this works but was unable to get it to work.

    The new test box would only be to display a total balance, no data entry. Because this is a data entry form

    the record would be a new record so the on Dirty would not work from what I can read about Dirty.

    If I use this command =Sum([Deposits]) where does the output go? 

    Friday, March 22, 2013 9:36 AM
  • If you create a text box and set its Control Source property to =Sum([Deposits]), the text box will display the sum of all records in the form. But if the form has Data Entry set to Yes, so that the user can only enter new records, that won't work. Change the Control Source of the totals text box for deposits to

    =DSum("Deposits", "Groceries")

    and that for Spending:

    =DSum("Spending", "Groceries")

    You have to recalculate these text boxes:

    Private Sub Deposits_AfterUpdate()
        Me.Dirty = False
        Me.Recalc
    End Sub
    
    Private Sub Spending_AfterUpdate()
        Me.Dirty = False
        Me.Recalc
    End Sub


    Regards, Hans Vogelaar

    • Marked as answer by Dummy yoyo Friday, March 29, 2013 1:10 PM
    Friday, March 22, 2013 11:01 AM
  • Thank you SO much; I now have at least a good start, I created two test boxes

    One for Deposits and one for Spending and I can see the total of each field and

    each will update! Wa hooo!  Now I will play a bit and see if I can create a test box with

    a value of total But I will see if I can do that on my own.

    Thank you for helping

    Harry Nash

    Friday, March 22, 2013 2:45 PM