no data on subreports 1

[Lhuffst]

I have a report with several different subreports. What I need to do is keep the header and column titles but show "No data found for this timeframe" (dates are passed in)
and close up the white space if there are not any records for that subreport. I've tried the suggestions here and while I don't get an error, I don't see the statement nor is the white space closing up. This is what I have.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim Ctrl As Control
Dim Shrink As Double
Dim NewTop As Double


For Each Ctrl In Me.Controls


If Ctrl.ControlType = acTextBox Then
       If IsNull(Ctrl.Value) Or Ctrl.Value = "" Or Ctrl.Value = 0 Then
         Ctrl.Visible = False
         Shrink = Shrink + Ctrl.Height
        Else
         Ctrl.Visible = True
        End If

End If

NewTop = Ctrl.top - Shrink
If NewTop > 0 Then Ctrl.top = NewTop Else Ctrl.top = 0

Next Ctrl

lblNoData.Visible = True
lblNoData.Caption = "There wasn't any new Meters tested during this timeframe"
End Sub

I have 5 text boxes where 3 hold text and 2 hold numbers.
What I see when I run the report is a zero for both text boxes that have numbers. This is why I added the Ctrl.Value = 0
I plan on adding the code to each subreport
Thanks for any help
lhuffst

 

[dhookom]

If a supreport has no records, it normally doesn't appear. You could "keep the header and column titles" by moving them to the main report. Add a label box behind the subreport and set it's caption to:"No data found for this timeframe".

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

dhookom,
for no records, how do I suppress the group and report subtotals? That is what is showing up as zeros
I moved the report and column headers as you suggested
thanks
lhuffst

 

[Lhuffst]

one more thing. I have this filter that I'm using

[b]Private Sub Report_Filter(Cancel As Integer, FilterType As Integer[/b])
Me.Filter = "dateAdded >= " & g_RptStartDate & " And dateadded < " & g_RptEndDate
Me.FilterOn = True
[b]End Sub[/b]



[b]Private Sub Report_Load()[/b]Me.Filter = "dateAdded >= " & g_RptStartDate & " And dateadded < " & g_RptEndDate
 Me.FilterOn = True
[b]End Sub[/b]

I pass the start/end dates from the reports menu. g_RptStartDate and g_RptEndDate are both public variables.
When I run the subreport by itself, I see the subtotals that I mentioned in previous post and the filter works
However, when I run it from the report menu then it seems to skip the filter totally and I get all records.
Where should I place the filter so when the entire report runs, it will filter that subreport? I figure I have to do this for each subreport
thanks
lhuffst

 

[dhookom]

I don't know where your subtotals are located or their control sources.

I use saved queries as the record sources of the main and subreports. You can use DAO code to change the SQL property of the saved queries.

Duane
Hook'D on Access
MS Access MVP

 

[Lhuffst]

YIPPEE. Figured out the filter issue. Had to change

CurrentDb.QueryDefs("qryMthyRpt_NewMetersTested_1").sql = "SELECT " & _
......
[\code]

the label behind the subreport works great!!! 
thanks all