Sub Report to display as a black box if query is empty set

[g-richardson]

I have a report which is comprised of several sub-reports which all format according to a set of variable. There are times when the resulting query produces and empty set.

I need the sub-report to display a black box when the underlying query produces no results.

I then need the black box to be visable in the Report in the location of that sub-report.

Here is the code I'm using:


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

On Error GoTo ErrorHandler

If Me.Ctl45EliteSPremium = 0 Then
Me.[Box4].BackColor = 0

Else
Me.[Box4].BackColor = 16777215

If Me.[% from best] <= 0.07499999999 Or Me.Ctl45EliteSRankID <= 3 Then
Me.[Box4].BackColor = 65280

Else
Me.[Box4].BackColor = 16777215

If Me.[% from best] >= 0.15499999999 Then
Me.[Box4].BackColor = 255

Else
Me.[Box4].BackColor = 16777215

If Me.[% from best] > 0.07499999999 And Me.[% from best] < 0.15499999999 Then
Me.[Box4].BackColor = 65535

Else
Me.[Box4].BackColor = 16777215

End If

End If

End If

End If

ErrorHandler:

Exit Sub

End Sub
Thanks for help.

George

 

[vbaInet]

1. Drop a textbox on your main report
2. Set the Conditional Formatting property of the textbox to:

[COLOR=Blue]Expression Is[/COLOR]
Not [COLOR=Red]SubreportName[/COLOR].Report.HasData

Amend the red part.
3. Set the BackColor of the textbox to Black if the above condition is met
4. Final step is to put the Textbox behind the subreport.

 

[g-richardson]

Thanks vbaInet. So simple and so perfect. Just what I was looking for.

Thanks too for the quick response.

George

 

[vbaInet]

You're welcome Big George!