Debit, Credit & Balance calculation in MS Access

**beacon** · Sep 1 '10, 06:08 AM

Hi Zalfa,

For #1, here's a link that talks about running totals, which you can use for your balances: http://articles.techrepublic.com.com...1-6140569.html.

You will likely need a form to select a specific person and then have the ID associated with that person passed to a query to filter the records when the form you're using to display the balances opens.

So, two forms, one to select the person you want to view the balances for and another to display the balances. The first form passes the ID for the person into the query and calls the second form, which opens with the records filtered for the person selected.

For help on using a form to pass custom parameters, view this link: http://www.fontstuff.com/access/acctut08.htm

For #2, in the Load event of the form, use DAO to create a recordset clone and traverse to the first record, then set a textbox on the form equal to the pointer.

Here's an example...it assumes that you have a textbox on your form called txtMyBalance and a field in your query called Balance:

Code:

Private Sub Form_Load()
    
    'Make sure you have DAO 3.6 selected in your references
    Dim rst as DAO.RecordSet
    Dim myRecord as Long

    Set rst = Me.RecordSetClone

    With rst
        .MoveFirst
        myRecord = .RecordCount
    End With

    txtMyBalance = = rst![Balance]

End Sub

For #3, you'll have to do a combination of #1 and #2. You'll need to create a form that accepts date parameters and use this to filter the query that is the record source for your form that displays the balances. If the opening balance may not be included in the date range, you may need to create a separate query that returns all transactions for a person, so that you can grab the opening balance.

Hope this helps...

beacon

**Zalfa Arif** · Sep 2 '10, 10:53 PM

Dear Beacon,
For #1: As per your instruction I have successed in finding balance of Credit&debit in query. But still there is problem when I'm filtering. I have another filed also in this query which is called as 'Account Type'. The filed 'Balance' is calculating hidden records also when I'll filter 'Account Type' filed

**beacon** · Sep 3 '10, 02:32 AM

This may or may not be true, but I think you will likely need a separate query for each 'Account Type' or you will need to make the query parameter-based as the records will have to be filtered by the account type prior to compiling the records.

If your goal is to ultimately output the data to a form, the parameter idea (that I provided as a link in my first post) would be ideal.

Debit, Credit & Balance calculation in MS Access

Debit, Credit & Balance calculation in MS Access

Comment

Comment

Comment