Force subreport visible

[handlebarry]

Hi,

I'm running a report with several subforms. Each of these subforms is based on a query that counts the instance of something.

However when the query returns no data the subreport does not show.

I been working with the subreports no data property:

Private Sub Report_NoData(Cancel As Integer)
Me.Visible
Me.txtcountexample = "0"

This is not working at all, I'm I on the right lines or should I be using on open, or the master reports on open

thanks in advance

 

[Remou]

Hi
This thread may help:
Subreport not showing on Main Report when empty
thread703-1096228

 

[handlebarry]

thanks but unfortunately it doesn't really help

I cannot use the solution where another table is included in the query because my query does not show any records (it's just a count). Also setting Can Shrink to No has no effect.

This is the example of the type of query

SELECT Count([Query Contract Properties Report].Lease_Type) AS CountOfLease_Type, [Query Contract Properties Report].Block_Code, [Query Contract Properties Report].Lease_Type
FROM [Query Contract Properties Report]
GROUP BY [Query Contract Properties Report].Block_Code, [Query Contract Properties Report].Lease_Type
HAVING ((([Query Contract Properties Report].Block_Code)=[reports]![Contract Block Report]![Block_Code]) AND (([Query Contract Properties Report].Lease_Type)="freehold"));

Only two fields are in the subform count and block code, and block code is not visble. Mayble I will try to get the query to return a 0 when there are no results

 

[CosmoKramer]

handleberry,

Please check out my response in this previous thread: thread703-856382

I think this is what you're looking for.....

Si hoc legere scis, nimis eruditionis habes

 

[handlebarry]

thanks - nice idea
Unfortunatly I'm exporting the report to excel and labels do not show up in excel

I'll try to get a text box to appear on no data

 

[Remou]

Hi
You mention that each subreport contains only a count, so I wonder if you need a subreport? Would it be possible to do something roughly like this?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Query as shown above
strSQL = "SELECT Count([Query Contract Properties Report].Lease_Type) " _
& "AS CountOfLease_Type, [Query Contract Properties Report].Block_Code " _
& "AS Expr1, [Query Contract Properties Report].Lease_Type AS Expr2 " _
& "FROM [Query Contract Properties Report] " _
& "GROUP BY [Query Contract Properties Report].Block_Code, " _
& "[Query Contract Properties Report].Lease_Type " _
& "HAVING [Query Contract Properties Report].Block_Code = " & Me.[Block_Code] _
& " AND [Query Contract Properties Report].Lease_Type='freehold'"
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF() Then
    Me.txtText = rs!CountOfLease_Type
Else
    Me.txtText = 0
End If
End Sub

 

[handlebarry]

sounds like a good idea - is it possible to keep changing the record set?

also what is the rs.EOF() bit do/mean?

thanks
barry

 

[Remou]

rs.EOF is end of file, in other words, no records. Yes, you can change the recordset.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strSQL as String
Dim rs As DAO.Recordset

'Query 1 as shown above
strSQL = "SELECT Count ..."
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF() Then
    Me.txtQuery1 = rs!CountOfLease_Type
Else
    Me.txtQuery1 = 0
End If
rs.Close

'Query 2 
strSQL = "SELECT Count ..."
Set rs = CurrentDb.OpenRecordset(strSQL)
If Not rs.EOF() Then
    Me.txtQuery2 = rs!CountOfSomething
Else
    Me.txtQuery2 = 0
End If
'And so on
End Sub

The above is only a very rough outline.