Report Grand Totals

new balance per row?

Or, net balance of totals?

I think you want the second one. If so, in the on format event of the area you want to have txtBalance and have:

Me!txtBalance  = Me!txtTotalIncome - Me!txtTotalExpense

Me!txtBalance is an unbound text box.

Also try butting in the control source of txtBalance  the following:

 = Me!txtTotalIncome - Me!txtTotalExpense

force it to be

=nz([txtTblIncome]) - nz([txtTblExpenses])

btw, what are the names of text boxes showing expense and income numbers?

Could you have your report in design view and upload screen shot of it?

I want to see the location(s) of txtTblIncome, txtTblExpenses, and txtBalance text boxes.

While the property sheet is open, select report footer area (in design view). The property sheet on the top should also say Report Footer. Now, click on Event tab on the property sheet. Double click in "On Format" event. click on [...] on the far right of it. This will take you to OnFormat event where you will put the following code:

me!txtBalance =nz(me![txtTblIncome]) - nz(me![txtTblExpenses])

but make sure to clear the control source of txtBalance. Then save and test it.

Ok, here we need to do some test. Copy and paste the following portion in bold:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

  MsgBox "The income is: " & Nz(Me![txtTblIncome]) & vbnewline & _
                  "The expense is: " & Nz(Me![txtTblExpenses])
    Me!txtBalance = 55 'Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])

End Sub

Let me know what you get.

Mike

Please change the event:

Private Sub Detail_Paint()
    Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
End Sub

Note that it is using Detail not footer. And the event name is Paint not OnFormat.

I am in Los Angeles. This line of code shouldn't cause that kind of error. To be fair, lets say 50% this line is the cause and 50% some other condition is causing it.

test 1:

Private Sub Detail_Paint()
    ' this is the line ought to work temporarily removed.
    'Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
   
    ' this line is to test to see if the code could insert some value to Balance text box
    Me!txtBalance = 55
End Sub

Select all Open in new window

test 2, if one works:

Private Sub Detail_Paint()
    ' this is the line ought to work temporarily removed.
    'Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
   
    ' this line is to test to see if the code could insert some value to Balance text box
    Me!txtBalance = Nz(Me![txtTblIncome])
End Sub

Select all Open in new window

for last test try below again:
Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])

BTW, I have tested this solution and it did work for me. You may also want to try:

Private Sub Detail_Paint()
    ' this is the line ought to work temporarily removed.
   On Error Resume Next
    Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
   
End Sub

Select all Open in new window

Explanation:  The values in txtTblIncome and txtTblExpenses maybe not available when the code runs thus causing some errors. And, because some events run a few times, on the last try, these boxes are bound to receive some values and all should work fine.

In my test version, I have =5 and =4 in the control sources of txtTblIncome and txtTblExpenses. So, they always had some value in them but in your case, these boxes don't have the necessary values and they need to be calculated perhaps.

Mike

re:> You asked me to clear the contents in the text box which exists in my footer where the total balance should be displayed.

There are two ways you can show a value in a text box like txtBalance in the footer:

1. Have to value in the control source property of txtBalance like
= 5     ' this will show 5 in txtBalance when you run the report, or
= Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses]) ' this will insert the result in txtBalance

2. Using VBA. In this case, the control source property has to be empty (cleared). It has to be unbound in another word.
When the event fires, the code want to insert the result obtained from Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses]) into txtBalance. This requires txtBalance to be unbound.

Please forgive me if you have already discussed this.  I got lost reading the thread.

Grand totals go in the report footer.  In the footer, you will have two controls already.  One to sum expenses and the other to sum income.

=Sum(Expenses)

=Sum(Income)

The third box can be:

= Nz(txtSumIncome,0) - Nz(txtSumExpenses,0)

Or
=Sum(Income) - Sum(Expenses)

The only time you can refer to a control name in an expression is if the control is in the current section.  Otherwise, you must repeat the calculation.

I am sorry you are so discouraged.  Without seeing the database, it can be difficult to diagnose a problem.  I would probably not have created separate tables for income and expenses if this is an accounting system.  Generally, they can be stored in the same table and the chart of accounts would distinguish them.  It could be that the problem is occurring because the two are on separate subreports and that is harder to net than if everything is in the same recordset.  If you can post a stripped down version of the database, we can probably help more.  Only a few records are probably necessary to identify the problem.

Personally, I have used several other desktop database engines and Access is head and shoulders better than any other option on the market within the past 20 years.  It is a true relational database as well as being object oriented.  That makes it work differently from the other databases such as dBase (which is the one I am most familiar with).  It is a mind-set.  I came to Access from COBOL and adjusting to event driven logic was traumatic in the beginning and even now I sometimes would prefer the more linear approach of a third-generation language.  But all-in-all, Access is a superior RAD tool once you get comfortable with events and functions and property settings and queries.