How to display report headers on a subreport with multiple pages.
Q: I'm using Access 97 and I have a report with a subreport that has multiple pages. I would like to display report headers on the subsequent pages of the subreport whenever there is more than one page in the subreport. How?
A: Here's a link to Microsoft's quick solution that uses macros with the report and subreport:
http://support.microsoft.com/?id=120907
Download the sample database file to see the solution in action. In case you have the time and motivation, you might want to convert the macros to VBA code for future software maintenance on your application. (Hints: Instead of calling macros in the events in the "Properties" dialog box of the report and subreport, you would call public functions -- and they must be functions, not subroutines -- that you have defined in a module.)
Top
How to limit the number of records displayed in a report.
Q: How do I limit the number of records so that my report only shows 100 records from the table?
A: If one bases the report on a query, not the table itself, then one can limit the number of records displayed. To do so, create a new query and select the table as the source. While the query is open in Design View, select all fields for this new query. Right-click in the upper pane and select "Properties" from the pop-up menu. Select "100" from the "Top Values" combo box. Save the query.
Open the report in Design View and open the Properties dialog window. Select the "Data" tab and then select the name of the new query from the "Record Source" combo box. Save the report and close the Properties dialog window.
Now whenever the report is run, only 100 records will be displayed.
Top
How to prevent a blank report from displaying.
Q: Occasionally, a report has no records. To prevent a blank report from displaying (and prevent my users from worrying that something went wrong), how can I prevent the empty report from even opening in the first place?
A: You can display a custom message for the user in the report's NoData( ) event, like this:
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrHandler
MsgBox "Sorry. There are no records" & vbCrLf & vbCrLf & _
"to display for this report.", vbInformation + vbOKOnly, _
" No Records"
Cancel = True
Exit Sub
ErrHandler:
MsgBox "Error in Report_NoData( )" & vbCrLf & _
"in " & Me.Name & " report." & vbCrLf & vbCrLf & _
Err.Number & vbCrLf & Err.Description
End If
End Sub
But wait. There's more. If you are opening your report from a button on a form, then cancelling the opening of the report will return an error to the button's click event, so your code's error handler needs to handle this, too. Here is an example:
Private Sub OpenReportBtn_Click( )
On Error GoTo ErrHandler
Dim sRptName As String
sRptName = "rptMonthlySales"
DoCmd.OpenReport sRptName, acPreview
Exit Sub
ErrHandler:
If (Err.Number <> 2501) Then ' Ignore if no records to display.
MsgBox "Error in OpenReportBtn_Click( )" & vbCrLf & _
"in " & Me.Name & " form." & vbCrLf & vbCrLf & _
Err.Number & vbCrLf & Err.Description
End If
End Sub
. . . where OpenReportBtn is the name of the button on the form, and rptMonthlySales is the name of the report.
Top
How to print only one page of a multipage report.
Q: My report pages print only one entry at a time, which I need. I can view report pages one at a time also, but when I print ONE out, Access wants to print them ALL out. Kind of like if you had a 15 page report in Word, and you wanted to view page 10 and ONLY print that page. Is there an easier way to do this rather than having to remember to switch my print settings to "current page" every time I print? I am always going to print these reports this way. I only need the reports when I create a new entry, and I only need that CURRENT entry. I could see it causing me a jam if I keep forgetting to change my printer settings every time.
A: (Courtesy of Tom Wickerath) Yes, you can print only one report sheet at a time, as long as you can uniquely identify the record in question. Primary keys are used in databases to uniquely identify records. The idea is to open up a report that only contains the information you wish to print (i.e., not 14 other pages).
You can use Docmd.OpenReport in VBA code and include the WhereCondition argument. Create a new subroutine. Enter "Docmd.OpenReport" (without the quotes). Select this with your mouse and then press F1. You should see the Access "Help" window.
For Access XP & 2003, use the following syntax:
OpenReport (ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
For Access 97 & 2000, use the following syntax:
OpenReport (ReportName, View, FilterName, WhereCondition)
FilterName Optional Variant. A string expression that's the valid name of a query in the current database.
WhereCondition Optional Variant. A string expression that's a valid SQL WHERE clause without the word WHERE.
You can also use the FilterName argument instead of the WhereCondition. Here is a step-by-step procedure, using the WhereCondition argument, that will hopefully get you going in the right direction:
1.) Open your form in design view.
2.) Click on View > Toolbox. Make sure the control wizards are activated. The background color of the "control wizards" button should be the same as the "select objects" button.
3.) Click the command button icon and drag a new button onto your form, with your left mouse button depressed. Release the left mouse button when you have the desired shape of the button.
4.) Select Report Operations and Preview Report in the next screen. Click Next.
5.) Select the name of the report that you wish to use in the next screen. Click Next.
6.) I suggest selecting Text in the next screen, and entering something like: &Print Record (or &Preview Record). The ampersand is a "hot key," which will allow you to print the report by pressing the <ALT> key and the <P> key simultaneously. Note: Choose a different letter if you've already used P as a hot key somewhere else on this form. Click Next.
7.) Give the command button a meaningful name. I strongly encourage you to use a naming convention, such as lower case "cmd" (without the quotes) as the prefix for command buttons on your form. You might name the command button something like cmdPreviewRecord
8.) Click on Finish to close the wizard. Click on View > Code to open up the code module associated with this form. You should see VBA code that the wizard just wrote for you. It should look something like this:
Private Sub cmdPreviewRecord_Click()
On Error GoTo Err_cmdPreviewRecord_Click
Dim stDocName As String
stDocName = "rptYourReportName"
DoCmd.OpenReport stDocName, acPreview
Exit_cmdPreviewRecord_Click:
Exit Sub
Err_cmdPreviewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewRecord_Click
End Sub
9.) Look at the top of your new code module. Do you see the words "Option Explicit" as the second line of code? If not, type this line of code in (without the quotes). We need to modify the line of code that reads:
DoCmd.OpenReport stDocName, acPreview
by adding the optional WhereCondition argument.
10.) Determine the field name and data type for the primary key on your form. Make a note of this information. The WhereCondition argument will be slightly different, depending upon whether you are using a text based primary key or an autonumber (ie. long integer) -- or other numerical data type -- for the primary key.
11.) For an autonumber primary key, add the following lines of code:
Dim lngRecNum As Long
lngRecNum = Me![PKFieldName]
For a text based primary key, add the following lines of code:
Dim strRecID As String
strRecID = "[PKFieldName]='" & Me![PKFieldName] & "'"
where PKFieldName is the name of the field that you determined in step 10.
Important note for a text based primary key:
For clarity, that's a single quote ( ' ) + double quote ( " ) after the equal sign, and a double quote ( " ) + single quote ( ' ) + double quote ( " ) after the last ampersand ( & ) symbol.
12.) Now, we will add the optional WhereCondition argument to the line of code that starts with Docmd.OpenReport:
For an autonumber primary key, use:
DoCmd.OpenReport stDocName, acPreview, , "[PKFieldName] = " & lngRecNum
For a text based primary key, use:
DoCmd.OpenReport stDocName, acPreview, , strRecID
13.) Click on Debug > Compile DatabaseName. Hopefully, your code will compile without any errors. Click on the Save toolbar button. Close the VBE editor.
14.) Open your form in normal preview mode. Test your new command button.
Top
How to read Access reports without having Microsoft Access installed on a computer.
Q: I have a Microsoft Access database that produces many reports, but only people who have Access loaded on their computers can read these reports. It's too expensive to buy copies of Access for every computer just to read these reports, because Access will not be needed for any other activity on the other computers. Is there a cheaper way?
A: Yes. One can automate Access to export each report as a Snapshot file, which can be read by the free Snapshot Viewer utility. The Snapshot Viewer utility can read reports created in Access 97 through 2003. Download Microsoft's free Snapshot Viewer utility and the necessary patches from the links on this Web page, and then install them on each computer that needs to read these reports:
http://office.microsoft.com/en-us/results.aspx?Scope=DC&Query=snapshot+view er
Top
How to reference the "Group Header" section on a report that groups on a column.
Q: I'm using Access 97 and I have a report with a subreport that occasionally has multiple pages. I would like to display information in a text box on the subsequent pages of the subreport when there is more than one page in the subreport. No matter how I try to reference the group header section, I get Run-time error 2465. The message says: "...can't find the field 'Section' referred to in your expression." What is the correct syntax for referencing the group header section?
A: The syntax is not very intuitive, nor is the event where you need to place the code. When you write your own code, don't use the name that you assigned to the section for grouping the report on. Open up the report in design view and left-click on the header you are interested in. Then right-click on the header to open the pop-up menu and select "Properties" to open the "Properties" dialog box. Select the "Other" tab and read the "Name" property that you will use in your code. You'll see something like GroupHeader0, GroupHeader1, or GroupHeader2. The syntax you need would look like this:
If (Reports!rptMonthlySales.GroupHeader0.HasContinued) Then
txtDisplayThis.Visible = True
Else
txtDisplayThis.Visible = False
End if
. . . where rptMonthlySales is the name of the report, txtDisplayThis is the name of the text box where you need to display your message on subsequent pages of the subreport, and GroupHeader0 is the first section to be grouped on in the report. Note that the above code works on the report in the GroupHeader0_Format( ) and Report_Page( ) subroutines, and in the subreport's Report_Page( ) subroutine, but not in the subreport's GroupHeader0_Format( ) subroutine.
Top
How to save multiple reports to Snapshot files at once.
Q: I have multiple reports that I'd like to save as Snapshot files. It's tedius to open each report, then use the File -> Export menu, then select the Snapshot file format, then press the "Export" button to save each file. Is there a way I can just press a single button and all of the open reports will be saved as Snapshot files?
A: Yes. Place the following code in a standard module:
Public Function saveRptsToSNP()
On Error GoTo ErrHandler
Dim rpt As Report
Dim sPath As String
Dim idx As Long
sPath = CurrentProject.Path & "\"
For idx = 0 To (Reports.Count - 1)
Set rpt = Reports(idx)
DoCmd.OutputTo acOutputReport, rpt.Name, acFormatSNP, _
sPath & rpt.Name & ".SNP", False
Next idx
Exit Function
ErrHandler:
MsgBox "Error in saveRptsToSNP( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
End Function
Call the saveRptsToSNP( ) function from the OnClick( ) event of a button on a form or the On Action Property of a custom button on a toolbar. When the button is selected, every report that is currently open will be saved as a Snapshot file in the same directory as the current database file.
Top
What the Revert item on the File menu is for. See Q-Built Solutions' Form tips.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
Visitors since Apr. '04:
|
