Seriality Control Finding Missing Numbers
It is part of the Accounting/Auditing activity to maintain control over 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:
- 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.
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 into it.
At 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 are Dimensioning 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, opens the Transaction Table and scans 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 any of 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 checks for the entries with chqSeries().Flag = False cases and writes it out in the Missing_List.
This process continues for the all the records in the Parameter Table.
NB: This method is not the most efficient one in terms of speed when large volume of transactions involved. In that case the data in the Transaction Table must be filtered with a Query using Parameter Values and use it 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.