If I filter a form, how can I preview a report using the current form's filter?
	How can I prevent the "#Error?" message from showing up in controls when a report is opened?
	How can I create a numbered list for a Report?
	How can I create alternating colored bands to ascent my report’s formatting?
	How can I cancel opening a report when there is no data to display?
	How can I print a border around the entire report?
	Even though I have the CanShrink and CanGrow properties set to Yes, I sometimes find large areas of blank space in my reports.

If I filter a form, how can I preview a report using the current form's filter?
Keywords: Filter Form Report
Posted Apr 24, 1997

You can use a combination of the Filter and FilterOn properties to open the Report. Here is a sample "click" event of a command button on a form that opens a report:

Sub cmdOpenReport_Click()

   If Me.FilterOn And Len(Me.Filter & "") >0 Then
	DoCmd.OpenReport ReportName:="MyReport", WhereCondition:=Me.Filter
   Else
	DoCmd.OpenReport ReportName:="MyReport"
   End If

End Sub

First we check to see if a filter is on using the "FilterOn" property of the Form. If the filter is on, we then check to make sure it's neither Null nor an empty string (which is why we are concatenating an empty string to the "Filter" property).

We then pass the Filter property of the form as the WhereCondition for the Report.

How can I prevent the "#Error?" message from showing up in controls when a report is opened?
Keywords: Error Report
Posted Apr 24, 1997

This message occurs because there are no underlying rows in the report (an empty recordset). You may still want to display the report and substitute a zero (0) for the "#Error?". This would be the case if you needed the subtotal from a subform. This can be done using the HasData property of the Report.

The HasData property returns 3 possible values: -1, Bound report with records; 0, Bound report with no records; 1, Unbound report.

Here is how you could use the HasData property in a control on a Form. In this example, the subtotal is taken from the subform and displayed in a control on the main form. If the subform has no records then the control on the main form defaults to 0:

="SubTotal: " & Iif([SubReport].[Report].[HasData]=-1, [SubReport].[Report]![txtSubTotal], 0)

How can I create a numbered list for a Report?
Keywords: Cancel Report Data
Posted Apr 24, 1997

The easiest way to accomplish this is to place a textbox in the detail section of the report. Set the ControlSource property to "=1" and set the RunningSum property to OverAll.

How can I create alternating colored bands to ascent my report's formatting?
Keywords: Create Bands
Posted Apr 24, 1997

This involves several steps:

1. First, create the band by using the rectangle control. Make the backcolor grey and size it to the width and height of the detail section. Name the rectangle, "recRecord"
2. Next create a textbox in the detail section of the report. Name it "txtCounter" and set the RunningSum property to OverAll and the Visible property to No
3. Lastly, place in the Detail Section's OnFormat event the following procedure:

   Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   On Error Resume Next
   
   Dim iRowCount as integer: iRowCount=3
   
   If (txtCounter Mod iRowCount) = 0 Then
       recRecord.Visible = True
   Else
       recRecord.Visible = False
   End If
   
   End Sub

   I declare a variable "iRowCount" to be equal to 3. This will have the effect of making every 3rd row in the detail section the color grey by making our grey rectangle visible.

   You can modify which lines to be greyed by changing the value of iRowCount.

How can I cancel opening a report when there is no data to display?
Keywords: Cancel Report Data
Posted Apr 24, 1997

The simplest way to determine if the report is empty is to use the NoData property of the Report. In the NoData event of the report module you can use something similar to this:

Private Sub Report_NoData (Cancel As Integer)
	
	Msgbox "There are currently no records available for this report."
	Cancel=True
End Sub

Setting Cancel to True will close the report.

How can I print a border around the entire report?
Keywords: Print Report Border
Posted Apr 24, 1997

One way to do this is to wait until Access finishes formatting the page and then use the OnPage event of the Report. This event is activated after the formatting takes place but before the printing. The following code draws a rectangle surrounding the entire printed page once Access has formatted the page:

Private Sub Report_Page()
    Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), , B
End Sub	
			

Even though I have the CanShrink and CanGrow properties set to Yes, I sometimes find large areas of blank space in my reports.
Keywords: CanShrink CanGrow
Posted Apr 24, 1994

Sometimes the CanShrink and CanGrow properties don’t appear to work for the following reasons:

1. Overlapping controls will not shrink, even when you’ve set the CanShrink property to Yes. If two controls touch at all, even by the smallest amount, they won’t grow or shrink correctly.
2. Controls shrink line by line (vertically). This means, for example, that if a group of controls is placed on the left side of a page and a large control (for example, an OLE picture) on the right side of the page, the controls on the left side will not shrink unless the picture is blank and hidden.
3. Space between controls is not affected by the CanShrink or CanGrow property.

Back to Top

Back to the FAQ Table of Contents

A computer's attention span is as long as it's power cord.