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

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a report that marks (or flags) any items that are missing from a given a list of sequential integers or dates. For example, when you receive your bank statement, your bank has flagged checks that have not cleared by placing an asterisk by the next check in the sequence. This article uses a Visual Basic for Applications function to compare the current item with the previous one in the sequence.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

To create a function that compares the current item with the previous one in a sequence, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Open the sample database Northwind.mdb.
Create a new report based on the Current Product List query.
On the View menu, click Code.
Type the following code in the module's Declarations section:
```
       Dim GlobalFlag as Variant
```
On the Insert menu, click Procedure. Type SetFlag in the Name box, and then click OK.

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

Close the Code window.
On the View menu, click Sorting and Grouping to display the Sorting and Grouping dialog box.
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.
Close the dialog box.

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

Place the controls so that they are aligned horizontally and that the OutOfSequence control is flush against the Product ID control.
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.