Results 1 to 5 of 5

Show Running Balance on Data Entry Form

  1. #1
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    57

    Show Running Balance on Data Entry Form

    I have a form that updates to my table and I would like to have the form show my new balance after data entry. The table contains all of the accounts that are credits and/or debits.

    My table was created from an Excel file that has totals for all credits and debits as well as my previous balance.
    Example:
    Balance from last entry = $1000
    New Credits (+ #) = $ 200
    New Debits (- #) = $ 300 shown as ($300)
    New Balance = $ 900

    I was successful in creating the Access Table. However, as you know the formulas are not created in Access.

    I used "Got Focus Event" on my data entry form to add all of the credit fields and Debit fields with no problem. The codes are -

    Private Sub Credit_GotFocus()
    Credit = [BS] + [BFD] + [Dues] + [DON] + [CSMS] + [TAB]
    End Sub


    and


    Private Sub Debit_GotFocus()
    Debit = [WAT] + [Rent] + [WPS] + [EHS] + [OPOX]
    End Sub


    Now I want to show my running balance which would be - [Old Balance] + [New Credits] + [New Debits] = [New Balance].

    Then, next entry becomes [Old Balance] + [New Cred...], well, I think you get the picture.

    Private Sub NewBalance_GotFocus()
    NewBalance = [Credit] + [Debit] + [????]
    End Sub


    Where [????] equals the previous balance.

    I can get the Credits and Debits added ok but how do I get the previous balance into the VBA. In my example I have reduced the balance by $100 (Credits + Debits = -$100)

    I am not a complete noob but I am pretty close.

    Any help please.

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,993
    I do this a little differently. I ignore the last balance and have a starting balance record in the table as either your debit or credit.

    In the footer of your form, create an unbound text box and place a formula similar to this:

    =DSum("Debits","tblName")-DSum("Credits","tblName")

    This will sum all debits and and credits and subtract the credits from the debits. I use this in a checkbook I have developed. If you need to see the checkbook, I can attach a template.

    Alan
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  3. #3
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    57
    I think I'll try this. Please attach. Thanx

  4. #4
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Location
    Lake County, IL
    Posts
    1,993
    See attached. You will need to download both files and place them both in the same folder.

    Good luck and post back with any questions you have.
    Attached Files Attached Files
    If an answer has "resolved" your issue, then make sure to mark your thread as "solved." Did my answer help? Click on the '*' below.
    Relational Database Principles
    DataPig Access Tutorials
    Debug your Code​

  5. #5
    hawzmolly is offline Advanced Beginner
    Windows 7 Access 2003
    Join Date
    Oct 2008
    Location
    Green Bay, WI
    Posts
    57
    Exactly what I needed. Problem solved Thanx again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-19-2011, 12:53 PM
  2. Data entry form
    By adept in forum Forms
    Replies: 1
    Last Post: 08-20-2010, 09:13 PM
  3. Sub Form Data Entry
    By OldBloke in forum Forms
    Replies: 1
    Last Post: 08-09-2010, 05:58 AM
  4. Can't get new field to show in my data entry form
    By Suzie in forum Database Design
    Replies: 19
    Last Post: 12-21-2009, 01:48 PM
  5. Adding Running Balance from Form to Reports
    By KIDGEO3 in forum Reports
    Replies: 1
    Last Post: 01-18-2006, 06:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums