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?
Calulations not displaying
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calulations not displaying
What happens if you use
=Sum([StockIn])-Sum([StockOut])
=Sum([StockIn])-Sum([StockOut])
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Calulations not displaying
It shows a 0 if there is no activity in the activity table
But where there is actually stock its displaying nothing at all
But where there is actually stock its displaying nothing at all
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calulations not displaying
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.
The event procedure forces recalculation of all calculated controls when the first tab (value = 0) is activated.
Code: Select all
Private Sub ctlTab_Change()
If Me.ctlTab.Value = 0 Then
Me.Recalc
End If
End Sub
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Calulations not displaying
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
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calulations not displaying
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.
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.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Calulations not displaying
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
So cant attach the form can I email it to you?
Are you on msn? I am keiath4@hotmail.com
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calulations not displaying
You can send an e-mail to hans dot vogelaar at gmail dot com.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Calulations not displaying
Thanks very much for taking a look just sent it
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Calulations not displaying
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)
=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)
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 73
- Joined: 08 Jan 2012, 15:07
Re: Calulations not displaying
Thank you thank you thank you!
Worked perfectly
Worked perfectly