Access Cookbook - Ken Getz [82]
On Error Resume Next
DoCmd.DeleteObject acReport, acbcTemp
On Error GoTo HandleErr
DoCmd.CopyObject , acbcTemp, acReport, strReport
This is necessary because the code makes design-time changes to the report. Making a copy eliminates the chance that the user will save the modified report over the original, which could mess things up the next time the report is run. The code completes its work by saving the new report and opening the report in the requested mode:
' Save changes to the new report, then open the temporary report:
DoCmd.Save acReport, acbcTemp
DoCmd.OpenReport acbcTemp, View:=intPrintOption
Making a temporary copy of the report eliminates the possibility of the original report being left in a state that makes it unusable the next time the report is run. This is important because there is no programmatic way to remove sort fields—you can't make a report that has been saved with two sort fields into a report with one sort field. If the user is allowed to save a modified version of the report, this is exactly what might happen. Therefore, we made the decision to use a temporary copy of the report (but only after trying numerous other workarounds).
The sample report and accompanying code assume that you want only one grouping field. We did this to simplify the example, but you could extend it by including code to make additional grouping fields (just like the code that now makes the sorting fields). If you do this, you'll have to deal with creating controls and placing them in the headers of the groups. You can create controls using the CreateReportControl function, which is described in the Access online help.
Any technique that relies on programmatically making changes to a report (or a form) while it's open in design view won't work in an Access MDE or ADE, where design changes aren't permitted. In those cases, however, you can use a modified version of this solution. In a report's Open event, you can't add new grouping and sorting levels, but you can change the control sources of existing ones. So, as long as you have enough grouping and sorting levels in the saved report, you can modify them at runtime rather than at design time with code like this:
rpt.GroupLevel(0).ControlSource = avarFields(0)
If necessary, you can create "dummy" grouping levels in your report, using a control source like =1, to make it possible to avoid having to open the report in design view.
Chapter 4. Applications
This chapter is a compendium of tips and suggestions for making your application development go more smoothly and your applications look more professional. You'll learn how to convert queries into embedded SQL strings providing data for forms or reports. You'll learn how to build an object inventory so you can document your applications better, how to ensure that properties for objects that should match up actually do, and how to disable screen output more effectively than the methods Access provides internally can. You'll find tips on discerning the current language version of Access and modifying text in error messages and on forms and reports to accommodate the current language. You'll see how to set and restore the Access caption and how to set startup options for your application. You'll also see how to use the Windows File Open/Save dialogs and how to clear out test data before shipping your application. The final topic explains how to implement user-level Access security.
WARNING
Some of the topics in this chapter take advantage of the MicrosoftData Access Objects (DAO) library. By default, when you create a newapplication in Access 2000 or later, Access doesn't includea reference to this library. Although each of the samples for thischapter includes this reference, if you create a new application andimport modules from the samples, your code won't work. In order to be able to use imported code that uses DAO objects, you'll need to select Tools → References... to display the References dialog box, and select the Microsoft DAO library.
4.1. Convert Queries into Embedded SQL Statements