Calulations not displaying

[keiath]

I am having a problem in my form,

I have a stock control that has tabs, on the tab (Activity) I have a subform that is populated by a table called Stock Activity and simply shows all the stock in and stock out.

In the footer I created a text box (text17) have this sum

=Sum([StockIn]-[StockOut]) to calculate the actual stock

On the First tab (Stock Data) I have a field called Total Stock and in that I have this:-

=[Stock Activity subform2].[Form]![Text17]

But it doesn't show the results. But if I change the =Sum([StockIn]-[StockOut]) to just =Sum([StockIn]) or visa-a-versa it displays the number

Why is this happening? Any ideas?

[HansV]

What happens if you use

=Sum([StockIn])-Sum([StockOut])

[keiath]

It shows a 0 if there is no activity in the activity table

But where there is actually stock its displaying nothing at all

[HansV]

Does the text box text17 in the subform display the correct value? If so, try creating an event procedure for the On Change event of the tab control. Let's say this tab control is named ctlTab.

Code: Select all

Private Sub ctlTab_Change()
    If Me.ctlTab.Value = 0 Then
        Me.Recalc
    End If
End Sub

The event procedure forces recalculation of all calculated controls when the first tab (value = 0) is activated.

[keiath]

Cant see if the txt17 is calculating correctly as its in the footer

And I can't see where you would force that in the tab command

If you dont mind I can send the database to you maybe you can figure out whats going on I'm pulling my hair out - lol

[HansV]

Create a copy of the database.
Remove or alter all sensitive information.
Remove database objects that are not relevant to the problem.
Zip the copy.
Attach the zip file to a reply. The maximum file size is 250 KB.
See Tip: adding an attachment using the prosilver skin for instructions, if you need them.

[keiath]

The most I can zip it up is 1.2mb

So cant attach the form can I email it to you?

Are you on msn? I am keiath4@hotmail.com

[HansV]

You can send an e-mail to hans dot vogelaar at gmail dot com.

[keiath]

Thanks very much for taking a look just sent it

[HansV]

I have received your database. The problem is caused by Null values. Try changing the control source of Text19 on the subform to:

=Sum(Nz([StockIn],0)-Nz([StockOut],0))

or if you want to display 0 if there are no activity records:

=Nz(Sum([StockIn]),0)-Nz(Sum([StockOut]),0)

[keiath]

Thank you thank you thank you!

Worked perfectly