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

• Microsoft Access 7.0, 97

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.

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" (without the quotation marks) 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.

Keywords          : kbusage PgmHowTo RptOthr
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto