LEARN MS-ACCESS TIPS AND TRICKS: msaccessprocess

Showing posts with label msaccessprocess. Show all posts

Indexing and Sorting with VBA

a.p.r. pillaiJanuary 28, 2010msaccessprocess, Process Controls No comments

Introduction

A Table is normally created with a Primary Key or Index to arrange the records in a certain order to view or process. Primary Key or Index can have one or more fields, in order to make the Key values Unique, if this is not possible with a single field value.

If you open the Employees Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample Database in the design view you can see that the EmployeeID Field is defined as the Primary Key.

To create an Index manually and define it as a Primary Key:

Open the Table in Design View.
Click on the left side of the Field Name to select it.
Click on the Indexes Toolbar Button.
You may give any suitable name in the Index Name Field, replacing the PrimaryKey text, if you would like to do so.

If the Record Values in the selected field are not unique then you can select more data fields (up to a maximum of ten Fields) to form Unique Key for the Primary Key.

You may click and drag over the Fields to select them (if they are adjoining fields) or click on each field by holding the Ctrl Key to select fields randomly.

The above procedure is creating a Primary Key Index for the Table. We can create more than one Index for a Table. But, only one Index can be active at one time.

Creating Index with VBA.

We can activate an existing Index of a Table or create a new Index through VBA and use it for data processing. We will learn here how to create a new Index with the name myIndex for a Table through VBA, activate it, use it for data processing and delete it at the end of the process.

We must validate the presence of myIndex in the Indexes collection of the Table if found, then activate it, otherwise create myIndex and activate it for data processing.

We will use the Orders and Order details Table from the Northwind.mdb sample database. We will organize the Order Details Table in the Order Number sequence so that the Order-wise Total Value of all items can be calculated and updated on the same Order record in Orders Table.

The Data Processing Steps

Following are the data processing steps, which we follow in the VBA Routine to update the Orders Table with order-wise Total Value from Order Details Table:

Open Orders Table for Update Mode.
Open Orders Details Table, for Input.
Check for the presence of myIndex in the Order Details Table, if found, then activate it, otherwise create myIndex and activate it as the current Index.
Initialize the Total to Zero.
Read the first record from the Order details Table.
Calculate the Total Value of the item using the Expression: Quantity * ((1-Discount%)*UnitPrice).
Add the Value to the Total.
Read the next record and compare it with the earlier Order Number, if same then repeat step 6 and 7 until the Order Number changes or no more records to process from the Order Details Table.
Find the record with the Order Number in Orders Table.
If found, then edit and update the total into the total value Field in Orders Table.
Check for the End Of File (EOF) condition of the Order Details Table.
If False then repeats the Process from Step 4 onwards, otherwise Close files, and stop Run.

Prepare for a Trial Run.

To try the above method Import Orders and Order Details Tables from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb (Access 2003) or C:\Users\User\My documents\Northwind 2007.accdb (Access 2007, if not available then you must create from Local Templates)
Open Orders Table in Design View.
Add a new Field with the name Total Value with Numeric (Double) data Type in Orders Table.

You may display the Index List of this Table to view its Primary Key Index on the Order ID field.
Save the Orders Table.
Open the VBA Editing Window (Alt+F11).
Create a new Standard Module from Insert Menu.

Copy and Paste the following VBA Routine and save the Module.

The CreateIndex() Function.

Public Function CreateIndex()
Dim db As Database, fld As Field, tbldef As TableDef
Dim idx As Index, rst As Recordset, PreviousOrderID As Long
Dim CurrentOrderID As LongDim xQuantity As Long, xUnitPrice As Double
Dim xDiscount As Double, Total As Double, rst2 As Recordset

On Error Resume Next

Set db = CurrentDb
Set rst = db.OpenRecordset("Order Details", dbOpenTable)
'Check for presence of myIndex, if found set as current
rst.Index = "myIndex"
If Err = 3800 Then
'myIndex not found
    Err.Clear
    GoSub myNewIndex
End If

On Error GoTo CreateIndex_Err

Set rst2 = db.OpenRecordset("Orders", dbOpenTable)
rst2.Index = "PrimaryKey"
PreviousOrderID = rst![Order ID]
CurrentOrderID = PreviousOrderID
Do Until rst.EOF
    Total = 0
    Do While CurrentOrderID = PreviousOrderID
        xQuantity = rst![quantity]
        xUnitPrice = rst![unit price]
        xDiscount = rst![discount]

        Total = Total + (xQuantity * ((1 - xDiscount) * xUnitPrice))
        rst.MoveNext
        PreviousOrderID = CurrentOrderID
        If Not rst.EOF Then
            CurrentOrderID = rst![Order ID]
        Else
            Exit Do
        End If
    Loop
    rst2.Seek "=", PreviousOrderID
    If Not rst2.NoMatch Then
        rst2.Edit
        rst2![totalvalue] = Total
        rst2.Update
    End If
    PreviousOrderID = CurrentOrderID
Loop

rst.Close
rst2.Close

'Delete temporary Index
Set tbldef = db.TableDefs("Order details")
tbldef.Indexes.Delete "myIndex"

CreateIndex_Exit:
Exit Function

myNewIndex:
rst.Close
Set tbldef = db.TableDefs("Order Details")
Set idx = tbldef.CreateIndex("myIndex")

Set fld = tbldef.CreateField("Order ID", dbLong)
idx.Fields.Append fld
Set fld = tbldef.CreateField("Product ID", dbLong)
idx.Fields.Append fld
tbldef.Indexes.Append idx
tbldef.Indexes.Refresh
Set rst = db.OpenRecordset("Order Details", dbOpenTable)
rst.Index = "myIndex"
Return

CreateIndex_Err:
MsgBox Err.Description, , "CreateIndex()"
Resume CreateIndex_Exit

End Function

Click somewhere in the middle of the VBA Routine and press F5 or click Run Command Button to execute the Code and update the Orders Table.

At the beginning part of the Code, we are attempting to make one of the Indexes (myIndex) of the Order Details Table active. Since myIndex is not yet created in the Table this action runs into an Error condition. We are trapping this Error Code and passing control to the Sub-Routine to create myIndex and to add it to the Indexes collection. The new Index is activated in preparation for data processing.

The next steps calculate Order-wise Total Values and updates on Orders Table.

At the end of the process, myIndex is deleted from the Indexes Collection of Order Details Table.

Earlier Post Link References:

MS-Access And Data Processing-2

a.p.r. pillaiNovember 23, 2009msaccessprocess, Process Controls 5 comments

Continued from Last Week's Post.

This is the continuation of an earlier article published on this subject last week. Click here to visit that page.

Last week we explored the sample data processing methods and tried to approach the same problem in different ways to arrive at the same result. Reports are the main output component that goes to the User with critical information for analysis of business activities and for making serious business decisions. Transforming raw data into a meaningful form and providing them on Reports is a real challenge of any Project.

If you attain some working knowledge of different types of Queries available in MS-Access you can do most of these tasks without touching the VBA Code. Depending upon the complexity of processing steps you can use several Queries, create intermediate temporary Tables, and use those tables as the source for other Queries to overcome issues that may arise as hurdles in the processing steps.

We will look into such an issue here so that you will know what I meant by hurdles in creating the final Report. Such complex data processing steps can be automated, sequencing each step in a Macro, and running that Macro from a Command Button Click or from VBA Sub-Routines.

Process Flowcharts.

It is absolutely necessary to create and maintain Flow Charts, of processes that involve several Queries and Tables, clearly indicating the Input and Output in each step, arriving at the final Report Data. You may create hundreds of Queries in a Database for different Reports. After some time we may forget what we did for a particular Report. If the User points out any flaw in the output, then we can easily backtrack the steps using the Flow Chart and debug the problem.

Last week I raised a question as to how we will show Revenue, Expenses, and Profit/Loss month-wise if the sample data are added with Year and Month Fields. The image of the sample Table (Transactions2) Source data is given below:

The image of the Report Output Created and presented to you last week is shown below:

We can transform the sample data given in the first image above into the Report output form in the second image in two steps. The numbers appearing as Suffix to the Column headings represent the Month Value. For example, Revenue1 is January Revenue and Profit/Loss2 is in February.

We can arrive at the above result in two steps and the SQL String of those two Queries are given below:

Query Name: Method2_1

TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
SELECT Transactions2.Location,
 Transactions2.Year
FROM Transactions2
GROUP BY Transactions2.Location,
 Transactions2.Year
PIVOT IIf([type]="R","Revenue","Expenses") & [Month];

Copy and Paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_1.

Open the Query and view the output as how it is transformed with the Cross-Tab Query.

Query Name: Method2_2

SELECT Method2_1.Location,
 Method2_1.Year,
 Method2_1.Revenue1,
 Method2_1.Expenses1,
 [Revenue1]-[Expenses1] AS [Profit/Loss1],
 Method2_1.Revenue2,
 Method2_1.Expenses2,
 [Revenue2]-[Expenses2] AS [Profit/Loss2]
FROM Method2_1;

Copy and Paste the above SQL String into the SQL Editing Window of a new Query and save it with the name Method2_2.
We are using the first Query as input to the second Query for the final Report output.
Open Method2_2 Query and view the output.

Even though we could arrive at the sample result with the above two Queries we have to modify the second Query every time to create a Profit/Loss Column when new data records are added for subsequent months. The P & L Report is created using the second Query then that also has to undergo changes to add Revenue, Expenses, and Profit Columns for the new period.

This cannot be a good method when we are expected to automate every process in the Database so that the User can prepare Reports with the click of a button.

We can automate this data processing task permanently with the following few simple steps:

Create a second Report Table with Revenue and Expenses Fields for all twelve months.
Change the second Query created above (Method2_2) as an append query and add the output data of available months into the Report Table.
Create a SELECT Query, using the Report Table as the source to calculate Profit/Loss Values, for all twelve months only once. This is possible because we have all twelve month's data fields in the Report Table, even if some of them will have only zero values till December.
Design the P&L; Report with all twelve months Revenue, Expenses&Profit/Loss Fields using the Query created in Step-3 as the source.

Once you implement this method you don't have to make any changes to the Queries or Reports when new data records are added to the Source Table. All you have to do is to automate this process, like deleting the old data (for this action we will need a Delete type Query) from the Report Table and bringing in fresh Report data from source table Transactions2.

So, let us get to work and do it.

Designing a Report Table

Create a Table with the following Field Structure and save it with the name PandLReportTable.
The Data Fields R1 to R12 and E1 to E12 will hold Revenue and Expenses Values for the period from January to December respectively.
NB: Don't forget to set the Default Value Property of all Number Fields with 0 values as shown in the Property Sheet below the Field Structure. This will prevent adding data fields with Null Values when data are not available for those fields. Remember, when you write expressions using Numeric Fields with Null values combined with fields with values; the end result will be Null.
We have modified the first Query above for simplifying the data field names.

Copy and paste the following SQL String into a new Query's SQL Editing Window and save it with the name Method3_l.

TRANSFORM Sum(Transactions2.Amount) AS SumOfAmount
SELECT Transactions2.Location,
 Transactions2.Year
FROM Transactions2
GROUP BY Transactions2.Location,
 Transactions2.Year
PIVOT [type]&[Month];

Copy and paste the SQL string given below into a new Query and save it with the name Method3_2.
```
INSERT INTO PandLReportTable
SELECT Method3_1.*
FROM Method3_1;
```

Copy and paste the following SQL String into a new Query and save it with the name PandLReportQ.

SELECT PandLReportTable.Location,
 PandLReportTable.Year,
 PandLReportTable.R1,
 PandLReportTable.E1,
 [R1]-[E1] AS P1,
 PandLReportTable.R2,
 PandLReportTable.E2,
 [R2]-[E2] AS P2,
 PandLReportTable.R3,
 PandLReportTable.E3,
 [R3]-[E3] AS P3,
 PandLReportTable.R4,
 PandLReportTable.E4,
 [R4]-[E4] AS P4,
 PandLReportTable.R5,
 PandLReportTable.E5,
 [R5]-[E5] AS P5,
 PandLReportTable.R6,
 PandLReportTable.E6,
 [R6]-[E6] AS P6,
 PandLReportTable.R7,
 PandLReportTable.E7,
 [R7]-[E7] AS P7,
 PandLReportTable.R8,
 PandLReportTable.E8,
 [R8]-[E8] AS P8,
 PandLReportTable.R9,
 PandLReportTable.E9,
 [R9]-[E9] AS P9,
 PandLReportTable.R10,
 PandLReportTable.E10,
 [R10]-[E10] AS P10,
 PandLReportTable.R11,
 PandLReportTable.E11,
 [R11]-[E11] AS P11,
 PandLReportTable.R12,
 PandLReportTable.E12,
 [R12]-[E12] AS P12
FROM PandLReportTable;

Design a Report using PandLReportQ as Source File, like the sample design image given below.
The sample image shows the Columns in January and February only. But, you may design the Report for all twelve months in a similar way. The Value of Year field is used for creating headings so that it automatically changes when the Report is printed next year without modification to the Report.

The Report in Print Preview.
We will automate the P&L; Report preparation procedure to get updated data on the Report when new data of Revenue and Expenses are added to the Source Table. As part of the automation procedure, we need a Delete Query to remove the earlier data from the PandLReportTable before adding revised data into it.
Create a new Query with the following SQL String and name the Query as PandLReportTable_Init.

DELETE PandLReportTable.*
FROM PandLReportTable;

Isn't it easy enough to prepare the P&L; Report with the above simple Queries and with a supporting Report Table for any number of Locations that you add to your main Source Table Transactions2. As you can see how you don't need any complicated programs to prepare this Report.

Actions Queries in Macro.

If you look at the Queries we have created we can see that there are only two Actions Queries among them (Delete and Append Queries). We can put these two Queries into a Macro to automate the P&L; Report preparation easily. But first, let us examine the logical arrangement of this Report preparation procedure with a Process Flow Chart.

In Step-1 the PandLReportTable_Init Query removes earlier Report Data from the PandLReportTable.

In Step-3 the Append Query (Method3_2) takes the Cross-Tab Query output from Step-2 and adds them to the Report Table PandLReportTable.

We have already written expressions in PandLReportQ SELECT Query to calculate Profit/Loss Values. The Report will automatically get all available data from this Query and other Columns on the Report will remain empty till fresh data Records are added in the Source Table Transactions2.

If we can add both the Action Queries into a Macro (or VBA Subroutine) then the user can click on a Command Button to run it every month to create the Report with added data within seconds.

The sample image of the Macro with the Action Queries in the sequence is given below for reference:

If you can further simplify this procedure, please share that idea with me too?

MS-Access and Data Processing

a.p.r. pillaiNovember 16, 2009msaccessprocess, Process Controls 8 comments

Introduction

Designing Forms or Reports can be learned quickly by mastering the use of Design Tools available in MS-Access, keeping aside the programming aspects. But, data processing is something that demands diversity in each Project and cannot be standardized. The data Table design is very important and these must be carefully planned and created for easier retrieval of information as well as to avoid duplication. Proper relationships must be established between Tables to join related information together.

Ignoring the importance of these considerations, designing with a casual approach, and filling up data with them like you do in Microsoft Excel will land you in trouble when you attempt to prepare reports out of them.

You can see a good example of database design in the C:\Program Files\Microsoft Office11\Samples\Northwind.mdb sample Database.

Open this sample database and select Relationships from Tools Menu to view the structure of various Tables and how they are organized and related to one another. Use it as a reference point and guide when you plan for a new Project.

Each Field Name in bold is defined as the Primary Key in their respective Table and established One-to-many Relationships with one another. This will ensure the availability of required information when needed from related tables for Reports.

The above lines were only a reminder of your future projects. You can see an example image of a bad Table design below. The Location names and Description values should have been in the Tables of their own with appropriate Codes. Two Combo Boxes can be created in the Transactions Table Structure to insert those Codes into the fields to avoid duplication of information as shown below.

Approaching the Data Processing Task.

But here, we are going to concentrate on learning the data processing steps using the above Table.

The second data field Type contains transaction type Codes. R stands for Revenue and E for Expenses. These category Codes are introduced in the Table keeping in mind that we must be able to Group the transactions on Category and Tabulate the Revenue and Expenses Values separately. The Description field shows the actual Account Heads under which each transaction is recorded.

We have been asked to prepare a Location-wise Profit/Loss Statement. Subtracting the Total of all Expenses from the Total of all Revenue figures will give us the required result. How many Queries or steps do you require to solve this problem, any idea? We require only the final Profit/Loss value with the Location Name in the Report, like the image below:

The first thought in your mind, I presume maybe, is how you can subtract the value of one Row from the other. Then you are thinking in the right direction.

If you say in four steps I will not accept that as a good approach, but if you can solve the problem and come out with the result then that is OK with me. After all, the correct end result is all that matters as far as the User is concerned.

If you say in three steps I will be happy to see how you do it. If you say in two steps, then I know you have some grip on things around here. If you say in one step, then I know you are somebody with MS-Access.

If you are really interested in taking up this simple challenge then stop reading further down from here and start trying out this in a database of your own. Come back with your own solution and compare it to the examples given here. If you do it differently, but arrive at the same result, then share that idea with me too.

Create the Transactions Table with the Structure and sample data given above.

One Step solution

Copy the following SQL String into the SQL Editing Window of a new Query and save it with a name you prefer.

SELECT Transactions.Location,
 Sum(IIf([type]="E",-[Amount],[Amount])) AS Profit
FROM Transactions
GROUP BY Transactions.Location;

Open the Query in Normal View and you will see the result of the Query as shown in the second Image given above.

Two Steps Solution.

Create a Query with the following SQL String and name the Query as Query_Step1.
```
SELECT Transactions.*,
 IIf([Type]="E",-[Amount],[Amount]) AS Amt
FROM Transactions;
```
The Query output will look like the image given below:
Tip: The Query Amt Column is formatted to display Negative Values in Color and in brackets. Open the Query in Design View. Highlight the Column and click on the Properties Toolbar Button or select Properties from View Menu to display the Property Sheet of the Column. Type 0.00;[Red](0.00);0.00;0.00 into the Format Property and save the Query. If you open the Query now the output will appear in color.

The Format Property Values are expressed in four segments separated by semi-colons. The first segment dictates how to display positive values, the second segment stands for Negative values, the third segment says what to display when the field value is Zero and the fourth segment displays zero when the Field/Column contains Null. The third and fourth segments can be set with a literal string like 0.00;[Red](0.00); "Zero"; "Null" to display these values rather than 0.00. You can set the Field Format Property values on the Table Structure, on Forms, or on Reports. It is not necessary that you should use all four segments of the Format Property Values all the time.
Create another Query, with the following SQL String, using Query_Step1 as Source Data, and save the Query with the name PL_Report:
```
SELECT Query_Step1.Location,
 Sum(Query_Step1.Amt) AS Amount
FROM Query_Step1
GROUP BY Query_Step1.Location;
```
Open the PL_Report Query in the normal view, and the result will be the same as the second image given above.

Three-Step Solution

If you need more clarity on how the results are being formed in the final report then try this method.

You can use the first Query under the two-step solution as the first step here.

Use the following SQL String, that uses the first step Query's output as source data, and create the second step Query with the name Query_Step2:

SELECT Query_Step1.Location,
 Query_Step1.Type,
 Sum(Query_Step1.Amt) AS Amt
FROM Query_Step1
GROUP BY Query_Step1.Location, Query_Step1.Type
ORDER BY Query_Step1.Location, Sum(Query_Step1.Amt) DESC;

The output of the second Query is given below.

Create a third Query for the final result, with the SQL String given below, using the second step Query (Query_Step2) as Input:

SELECT Query_Step2.Location,
 "Profit/Loss" AS Description,
 Sum(Query_Step2.Amt) AS Amt
FROM Query_Step2
GROUP BY Query_Step2.Location;

The output of the above Query is given below with a three Column result replacing Type Column with Description.

Doing It Differently

How about doing it differently and arriving at the following Result with Queries in two Steps?

Create the first Query Method2_1 with the following SQL String:

TRANSFORM Sum(Transactions.Amount) AS SumOfAmount
SELECT Transactions.Location
FROM Transactions
GROUP BY Transactions.Location
PIVOT IIf([Type]="R","Revenue","Expenses");

Create the Report Query Method2_2 with the following SQL String that uses Method2_1 as Source:

SELECT Method2_1.Location,
 Method2_1.Revenue,
 Method2_1.Expenses,
 [Revenue]-[Expenses] AS [Profit/Loss]
FROM Method2_1;

Open Method2_2 Query in Normal View and check the output.
As you have seen in the above examples you can approach a problem in MS-Access differently and arrive at the same result. If you have to create several steps to get the final Report output, then it is a good idea to create a Flow Chart of the Process Steps. Later on, if you find something is not right with the Report you can always follow this path and backtrack to find the Error.

A sample Flow Chart of the Three-Step Solution is given below:

If the Transactions Table has Year and Month Fields too, and both locations have January and February 2009 data in them, then how you will create the Report Month-wise?

Try it out on your own and check it out with my examples next week. A sample image of the output is given below for reference.

Seriality Control Finding Missing Numbers

a.p.r. pillaiOctober 11, 2008msaccessprocess, Process Controls No comments

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.