Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

Preparing for Trial Run

  1. Parameter Table: with Start-Number and End-Number values. Uses this number range to find the missing numbers from within the Transaction Table.
  2. Transaction Table: where the actual transaction details of the Documents are recorded and our program should check and bring out the missing cases.
  3. Missing_List Table: where the missing list of Numbers will be created.
  4. 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

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

'Flag all matching cheque Numbers in Array
    k = 0
    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

'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
                !bnk = bank
                ![MISSING_NUMBER] = ChqSeries(k).lngNum
                ![REMARKS] = "** missing **"
                ![CHECKED_SERIES] = strSeries
            End With
        End If


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

Exit Function

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.

VBA Code Analysis

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 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, 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.


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...


Blog Archive

Recent Posts