Share this Page URL

Chapter 17. Using Visual Basic to Manage... > Creating a Custom Report Feature - Pg. 591

Using Visual Basic to Manage Data 591 turn off the echo while the TasksByPriority report is opened in Design view, two of its properties are set, the report is closed and saved, and the report is then opened in the Preview window. After the report is displayed in the Preview window, the echo is turned on again so that the user can view the report. In an operation such as this, you need to open the report in Design view first so that you can set the properties you need to. The report's RecordSource property is set to strSelect, the SQL statement created in the first part of the procedure. Then the code refers to the report's title label ( !lblTitle ), setting its Caption property so that the label reads Tasks for plus the name of the country selected in the form (the value stored in the variable strCountry ). Before the report can be displayed with the changes to its properties, the report needs to be closed and saved ( DoCmd.Close , , acSaveYes ). As you can see, putting together even a relatively straightforward SQL statement such as the one used in this example requires a number of different operations in Visual Basic and requires you to keep track of several variables. The code in this event procedure, however, repeats some variable assignments in the Select Case statement that don't need to be stated twice. For example, if we wanted to change the selection of fields in the report, we would need to update the text string for the SELECT statement (assigned to the variable strSelect) in both cases. In the next set of steps, we'll modify this procedure to present the code more efficiently and make it easier to maintain. Modify the ViewTasks procedure 1. 2. 3. If the TasksReport form isn't open in Design view, select it in the Database window, and then click Design. Right-click the View Tasks button, and then click Build Event to open the Visual Basic Editor. In the Select Case segment of the code, select the first three lines under the line Case Is = 1, as shown here: