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

Introduction.

It is part of the Accounting/Auditing activity to maintain control over the usage of important Documents like Cheque Books, Receipt Vouchers, Cheque Payment Vouchers, Local Purchase Orders, and so on. Usage of these Documents is monitored very closely in business activities so that they are not misused in any way affecting the business and reputation of the Company.

These Documents are mostly printed in Books of 20, 50, or 100 sheets with running Serial Numbers and the transactions involving these documents are recorded with the Serial Numbers appearing on the Documents.

A periodical check is performed on the actual transactions with the physical documents to ensure that the usage of these documents is in proper order and anything missing by cancellation or any other reason is traced out and documented.

We will try out a sample Program to find the missing numbers from the recorded transactions and create a list. We need the following Tables with information for our Program:

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.
Transaction Table: where the actual transaction details of the Documents are recorded and our program should check and bring out the missing cases.
Missing_List Table: where the missing list of Numbers will be created.
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, 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 given 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, picks the first record and calculates the number of records, which comes within the range, and re-dimensions the array to hold all the numbers between 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 found not marked as True then the Serial Number corresponding to that entry found missing in the Transaction Table. In the next step, we scan through the Array and check for the entries with chqSeries().Flag = False cases and writes it out in the Missing_List.

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

NB: This method is not the most efficient one in terms of speed when a large volume of transactions is involved in processing. In that case, the data in the Transaction Table must be filtered with a Query using Parameter Values and used in place of the 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.