Label not showing on subform if value is 0 (2 Viewers)

[andymartin3186]

Hi
I have created a small dashboard showing certain statistics of my business. This is comprised of small subform squares on my main menu. These subforms are based off queries, and the forms control performs a count or totals this various data. Attached screenshot 1 showing a blank subform, 2 showing it displaying when there is returned results in the query.

The issue I have is when there is nothing to count i.e no collections for example the form becomes completely blank. I would like the label to still appear and show 0 rather than just be blank.

I tried to add an IsNull IIF statement to the control which would show 0 instead of nothing but this doesn't work.
e.g

=IIf(IsNull(Count(([JobID]))),0,(Count([JobID])))

I've tried to add another label into the form as a test, and even this doesn't show if the query returns no results.

Also I've added an on click event to each of the subforms which loads a particular report to elaborate on the information. If the subform is blank the report opens, but if the subform has data clicking it does nothing. Why would this be?

 

[DHookom]

Is the label on the subform in the header or detail section? I would first make sure it’s in the subform header.

 

[andymartin3186]

Is the label on the subform in the header or detail section? I would first make sure it’s in the subform header.

Legend!

That sorted it. Such a simple fix! Many thanks

 

[Gasman]

I would not have thought Count() would return Null, but zero?

 

[andymartin3186]

I would not have thought Count() would return Null, but zero?

Yes I agree, I was wrong in what I said earlier. I would like it to return 0 if there are no instances but can't get this to happen.

 

[LarryE]

I would like it to return 0 if there are no instances but can't get this to happen.

Try:
=IIf(Count()>0,Count()," ")

 

[andymartin3186]

Try:
=IIf(Count()>0,Count()," ")

Sorry no joy with this

 

[RonPaii]

Put a label behind each sub-form with the message you want when the sub-form is empty. If the sub-form is empty, set it's Visible property to False.

 

[CJ_London]

Why use a subform in the first place? Is the idea that it expands if the user clicks on it?

Why not use a label or button? - I do something similar to show certain data and use color to indicate current status. Eg red might mean invoices to be raised or paid - user clicks on the label/button to open the relevant form

 

[andymartin3186]

Why use a subform in the first place? Is the idea that it expands if the user clicks on it?

Why not use a label or button? - I do something similar to show certain data and use color to indicate current status. Eg red might mean invoices to be raised or paid - user clicks on the label/button to open the relevant form

I wasn't sure how to do this as its on the main menu and there is no control source.
Each subform has an associated control source as the related query

If I create a label on my main menu with control source

=Count([ComponentOrderQuery]![OrderID])

It shows Error

 

[arnelgp]

use Dcount():

a Label can only have a .Caption property

Me.lblCount.Caption = DCount("1", "ComponentOrderQuery", "[OrderID] = " & Nz([OrderID], 0))

 

[CJ_London]

I wasn't sure how to do this as its on the main menu and there is no control source.

doesn't matter - a form does not have to have a recordsource. It can have unbound controls or controls bound to a calculation/function such as dcount. And as Arnel shows, you can assign a value to a label or button by assigning the value to the caption.

The potential problem with having multiple subforms is performance - if each subform takes a second to load and you have 12 subforms, then simplistically it will take 12 seconds for the main form to open.

 

[andymartin3186]

use Dcount():

a Label can only have a .Caption property

Me.lblCount.Caption = DCount("1", "ComponentOrderQuery", "[OrderID] = " & Nz([OrderID], 0))

Sorry I was stupidly mixing it up with thinking it was a text box!

I've created a label on the main menu and put this in the main menu on load

Me.LblOpenOrder.Caption = DCount("1", "TotalOpenPOQuery", "[OrderID] = " & Nz([OrderID], 0))

It errors with 2465 can't find |1 in your expression

 

[tvanstiphout]

I use unbound controls as well.
In Form_Open: Me.TimerInterval = 1. This starts a timer. The form will continue to open.
Form_Timer happens after the form is open and visible. In that event I stop the timer Me.TimerInterval = 0, and then call RefreshKPI procedure.
It calls a stored procedure in SQL Server to get the KPI data, which I then assign to the unbound textboxes using Me.ControlName.ControlSource = "=" & rs!KpiField
It also sets the RowSource for the modern chart: Me.chartMain.RowSource = strQuery, which then loads asynchronously.
The above keeps the app nice and responsive, and typically the user is already interacting with the Ribbon by the time the form is fully populated.

 

[CJ_London]

It errors with 2465 can't find |1 in your expression

usually means you have a spelling issue so first check names - TotalOpenPOQuery and OrderID - and is orderID on your main form?

try using Me.OrderID to verify

and try using "*" rather than "1" in the dcount function

 

[andymartin3186]

usually means you have a spelling issue so first check names - TotalOpenPOQuery and OrderID - and is orderID on your main form?

try using Me.OrderID to verify

and try using "*" rather than "1" in the dcount function

No, its not on the main form as its a dumb form used as a main menu.

This could be the issue.

The TotalOpenPO query is basically returning all orders that "received" = False.

The count is to count the number of lines in the query,

Am I doing this the wrong way? Is there a better way?

 

[CJ_London]

dcount will return the number of lines in the query. If the query already has the criteria '"received" = False' then you just need

DCount("*", "TotalOpenPOQuery")

 

[Gasman]

No, its not on the main form as its a dumb form used as a main menu.

This could be the issue.

The TotalOpenPO query is basically returning all orders that "received" = False.

The count is to count the number of lines in the query,

Am I doing this the wrong way? Is there a better way?

Is [OrderID] actually in that query?

 

[andymartin3186]

Is [OrderID] actually in that query?

Yes
I currently have one result for testing purposes, and OrderID is the first column

 

[andymartin3186]

dcount will return the number of lines in the query. If the query already has the criteria '"received" = False' then you just need

DCount("*", "TotalOpenPOQuery")

Whayhey this worked!!

Its also showing 0 now when there is nothing

Thank you!!!