ACC: How to Flag Out-of-Sequence Numbers on a Report

MORE INFORMATION

1. Open the sample database Northwind.mdb.
2. Create a new report based on the Current Product List query.
3. On the View menu, click Code.
4. Type the following code in the module's Declarations section:

          Dim GlobalFlag as Variant

5. On the Insert menu, click Procedure. Type SetFlag in the Name box, and then click OK.
6. Modify the SetFlag function as follows:

          Function SetFlag(x as Variant)
             If GlobalFlag <> x - 1 then
                SetFlag = "*"
             Else
                SetFlag = ""
             End If
   
             GlobalFlag = x
          End Function
   						

7. Close the Code window.
8. On the View menu, click Sorting and Grouping to display the Sorting and Grouping dialog box.
9. In the first row of the Sorting and Grouping dialog box, select ProductID for the Field/Expression column and select Ascending for the Sort Order column.
10. Close the dialog box.
11. Add the following three text box controls to the detail section of the report:

           Textbox:
              Name: Product ID
              ControlSource: ProductID
           Textbox:
              Name: OutOfSequence
              ControlSource: =SetFlag([ProductID])
           Textbox:
              Name: Product Name
              ControlSource: ProductName

12. Place the controls so that they are aligned horizontally and that the OutOfSequence control is flush against the Product ID control.
13. To view the results, click Print Preview on the File menu. In the Preview window, note that an asterisk is placed next to each ProductID where the preceding ProductID is not in sequence.