11.4. When Not to Use VBA
There are times when you will attempt to solve a problem with VBA, only to find out that VBA is not the answer. The following example is such a case. Over time, you will learn to identify what can be solved with code, with a query or multiple queries, and also via the use of controls on a report.
When developing a report, there are things that you can do that don't require programming. Instead, with a properly placed control, or with the use of a grouping section, the desired functionality can be achieved. In this example, an index letter can be added to the start of each Alphabetical value in the report. This can be helpful for easy recognition of data by the user. Figure 11-11 demonstrates the desired effect.
Figure 11.11. Figure 11-11
In this report, each product is listed alphabetically. Where one letter ends, and the next begins, the starting letter is printed to assist the reader with locating their desired value. This functionality could be programmed in the report writer, but is easily implemented with just a text box control and a group.
Figure 11-12 displays the report called Alphabetical List of Products, which comes with the Northwind database example.
Figure 11.12. Figure 11-12
In Figure 11-12, notice that there is a report section called ProductName Header. This is the start of a grouping for the field called ProductName. Typically, group headers are a good place to put specific labels that may apply only on the group level. As seen here, the labels for Product Name, Category Name, and the Quantity Per Unit are displayed.
Along with those static labels is a text box. Although it looks the same as the labels, it reveals that it is a textbox by the fact that it displays a "=" sign as the first character. This is a method of populating a text box at the time of generation. So, rather than execute VBA code to populate this text box, the "=" sign instructs Access to evaluate (or calculate) the value before showing the report. The text box displays the following expression:
=Left([ProductName],1)
This expression is used to extract the first character from each Product Name (that is, Left("Apple") = "A"). Thus, every time the group header is printed, the first character is printed for each ProductName.
Now, if you're familiar with reports, at this point you may be a little confused. It would stand to reason that for every product in the table this text box should be displayed. After all, the grouping is by the ProductName. If that were the case, then the report would look like Figure 11-13.
Figure 11.13. Figure 11-13
Figure 11-13 demonstrates what Access should produce, based on the fact that the header should print with every product name. The way to prevent this is to use a property of the Group called Group On. The Group On property has two settings, which are Each Value and Prefix Characters. The value used in the most recent picture is when Each Value is selected, and as you can see, it does not have the desired effect.
When you set the property to Prefix Characters, you can then specify the number of characters that are to be used in the grouping. In the case of the Index letter example, it makes sense that the value of 1 is used. In Figure 11-14, the Sorting and Grouping box is displayed using these settings.
Figure 11.14. Figure 11-14
In this example of the Sorting and Grouping dialog box, the Expression is set to use a Group Header and Footer section in the report. The Group On property is set to Prefix Characters and the Group Intervals is set to 1.