Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

no data on subreports

no data on subreports

no data on subreports

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
         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

RE: no data on subreports

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".

Hook'D on Access
MS Access MVP

RE: no data on subreports

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

RE: no data on subreports

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


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

Private Sub Report_Load()Me.Filter = "dateAdded >= " & g_RptStartDate & " And dateadded < " & g_RptEndDate
 Me.FilterOn = True
End Sub 
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

RE: no data on subreports

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.

Hook'D on Access
MS Access MVP

RE: no data on subreports

YIPPEE. Figured out the filter issue. Had to change
CurrentDb.QueryDefs("qryMthyRpt_NewMetersTested_1").sql = "SELECT " & _

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close