Seriality Control Finding Missing Numbers ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

In accounting and auditing, it is a standard practice to maintain strict control over the use of important documents such as checkbooks, receipt vouchers, payment vouchers, and local purchase orders. The usage of these documents is closely monitored to prevent misuse that could negatively impact the company’s operations or reputation.

These documents are usually printed in books containing 20, 50, or 100 sheets, each bearing sequential serial numbers. All transactions involving these documents are recorded along with their corresponding serial numbers.

Periodic audits are conducted to verify that the serial numbers recorded in the system match the continuity of used documents in hand. Any missing numbers, whether due to cancellation, loss, or other reasons, are investigated and properly documented.

To illustrate this process, we’ll create a sample program that identifies and lists missing serial numbers from recorded transactions. For this, we’ll need the following tables containing the necessary information:

Preparing for Trial Run

Parameter Table: with Start-Number and End-Number values. Uses this number range to find the missing numbers from within the Transaction Table.
Right-click to open Large Image in New Tab/Window.
Transaction Table: where the actual transaction details of the Documents are recorded, and our program should check and bring out the missing cases.
Right-click to open Large Image in New Tab/Window.
Missing_List Table: where the missing list of Numbers will be created.

Right-click to open Large Image in New Tab/Window.
Copy the following VBA Code and paste it into a new Global Module in your Database.

The VBA Code

Option Compare Database
Option Explicit

Type Rec
    lngNum As Long
    flag As Boolean
End Type

Public Function MissingNumbers()
'------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 05/10/2008
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------
Dim db As Database, rst1 As Recordset, rst2 As Recordset
Dim lngStart As Long, lngEnd As Long
Dim ChequeNo As Long, j As Long, ChqSeries() As Rec
Dim NumberOfChqs As Long, k As Integer, bank As String
Dim strSeries As String

On Error GoTo MissingNumbers_Err

'initialize the Report Table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE Missing_List.* FROM Missing_List;"
DoCmd.SetWarnings True

Set db = CurrentDb
'Load Cheque Book Start and End Numbers
'from parameter table
Set rst1 = db.OpenRecordset("Parameter", dbOpenDynaset)
Do While Not rst1.EOF
    bank = rst1!bank
    lngStart = rst1!StartNumber
    lngEnd = rst1!EndNumber
' calculate number of cheques
    NumberOfChqs = lngEnd - lngStart + 1
    strSeries = "Range: " & lngStart & " To " & lngEnd

'redimention array to hold all the cheque Numbers
'between Start and End numbers
    ReDim ChqSeries(1 To NumberOfChqs) As Rec

'Generate All cheque Numbers between
'Start and End Numbers and load into Array
    k = 0
    For j = lngStart To lngEnd
        k = k + 1
        ChqSeries(k).lngNum = j
        ChqSeries(k).flag = False
    Next

'Open Cheque Payment Transaction Records
    Set rst2 = db.OpenRecordset("Transactions", dbOpenDynaset)

'Flag all matching cheque Numbers in Array
    k = 0
    rst2.MoveFirst
    Do While Not rst2.EOF
        ChequeNo = rst2![chqNo]
        If ChequeNo >= lngStart And ChequeNo <= lngEnd And rst2![bnkCode] = bank Then
            j = (ChequeNo - lngStart) + 1
            ChqSeries(j).flag = True
        End If
        rst2.MoveNext
    Loop
    rst2.Close

'create records for unmatched items in Report Table
    Set rst2 = db.OpenRecordset("Missing_List", dbOpenDynaset)
    k = 0
    For j = lngStart To lngEnd
        k = k + 1
        If ChqSeries(k).flag = False Then
            With rst2
                .AddNew
                !bnk = bank
                ![MISSING_NUMBER] = ChqSeries(k).lngNum
                ![REMARKS] = "** missing **"
                ![CHECKED_SERIES] = strSeries
                .Update
            End With
        End If
    Next
    rst2.Close

rst1.MoveNext
Loop
rst1.Close

Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

MissingNumbers_Exit:
Exit Function

MissingNumbers_Err:
MsgBox Err & " : " & Err.Description, , "MissingNumbers()"
Resume MissingNumbers_Exit
End Function

To try out the above program, create the first two tables with the same Field Names and data type, as suggested by the sample data given above, and enter the same data or similar records of your choice, excluding some serial numbers from the range of values in the Parameter Table.

Create the third Table (Missing_List) with the same Field Names and data type of the sample records shown above, but without adding any records to it.

VBA Code Analysis

In the global area of the Module, we have created a User-Defined Data Type Rec with two elements, lngNum to hold the Serial Number and Flag to mark when a match is found in the Transaction Table, with Long Integer and Boolean data types, respectively. After creating the new data type in the Global area, we have declared an empty array variable ChqSeries() as Rec with the newly created data type within the Program.

The program opens the Parameter Table, starts with the first record, calculates the number of records that come within the given range, and re-dimensions the array to hold all the numbers between the lngStart and lngEnd parameter values.

In the next step, the program generates all the serial numbers between lngStart and lngEnd and fills the chqSeries().lngNum array. The Flag element value is set as False.

Next, open the Transaction Table and scan through it for matching Bank Code, and for Cheque Numbers between lngStart and lngEnd, and when a match is found, the 'chqSeries().Flag' is marked as True for that entry within the array, and continues this process till the end of the file is reached.

In this process, if the 'chqSeries().Flag' is not marked as True, then the Serial Number corresponding to that entry is found missing in the Transaction Table. In the next step, we scan through the Array and check for entries with 'chqSeries().Flag' = False, and write them out in the Missing_List.

This process continues for all the records in the Parameter Table.

Note: This method is not the most efficient in terms of processing speed when handling a large volume of transactions. In such cases, it is advisable to filter the data in the Transaction table using a parameterized query and use the filtered dataset in place of the full Transaction table.

This needs extra steps in the program to create a Dynamic Query with SQL Statement just before the following statement: Set rst2 = db.OpenRecordset("Transactions", dbOpenDynaset), replacing Transactions with the Query name.