Multiple Parameters For Query

Introduction.

Queries are an essential element in data processing and we use them a lot in various ways. While creating Queries the main issue we are faced with is how to filter data in a user-friendly manner and make things work effortlessly for the User. We use several methods to facilitate the user to pass values as criteria to the Queries.

1. Creating Parameter Queries and inserting Variables (like [Enter Sales Date]) in Query's Criteria row, so that the Query will prompt for Parameter Values and the User can Key-in them directly and filter records. The Data Type for the Parameter Variable can be defined by selecting the Parameters. . . Option from the Query Menu when you are in the Design view of the Query.

2. Creates TextBoxes or Combo Boxes on the Form and the User fills in values into them and runs the Report or Data views. The underlying Queries will have a reference to the TextBoxes or Combo Boxes on the Form in the Criteria Row, like Forms![MyForm]![myDateCombo], and based on the values in them, the data filtering takes place for Reports or Views.

3. Another way of selecting records is based on a range of Values. For example, filtering Sales records for a certain period and the criteria set in the Query for the Sales Date will be something like Between #01/01/2008# AND #03/31/2008# if the values are used in Constant form. But, these values also can be passed from TextBoxes from a Form too.

   What I prefer to do in these cases, creates a small table (let us call it, a Parameter Table) with one record and two fields for StartDate and EndDate and create a Datasheet Form and place it as a Sub-Form on the Main Form so that theUser can conveniently key in the date range values into the Table.

   This table will be included in the main Query and the StartDate, and EndDate fields are placed in the Criteria row with the expression Between [StartDate] AND [EndDate]. It is important to see that this Parameter table has only one record in it otherwise the records selected from the main table will be doubled if the parameter table has two records. We can control this by setting the Allow Additions Property Value, of the Data Sheet Form, to No so that the user is prevented from adding more records by mistake.

   When the user clicks a button in the Report or for other outputs based on this date ranges we can run the Query after refreshing the Parameter Sub-Form to update the changed value in the table.

4. The above example, asks for all the data between StartDate and Update. But there are times that we need data, the intermittent values like Employee Codes 1, 5, 7, 8, and we are forced to input the Code in the criteria row in one of three ways like the sample image given below:

Query Parameter Input Methods

I would like to present here another method that I use to provide the Users to select Parameter Values for Reports by putting check marks in the Parameter Table.

Assume that our Company has Branch Offices across the Country and the Management may ask for Reports on selected Branches. Since Branch Names are constant values all we need to do is to select the required Branches by putting check marks on their side and the selected cases can be used as criteria for filtering Data.

To have a closer look at this method and for simplicity, we will use the List of Months for our example and see how the selected Months are used in the Criteria of the Main Query. The Image of the List of Months presented to the user in a Datasheet Form (as a Sub-Form on the Main Form) is given below:

We need two Queries, one to filter the selected months from the list and the second the Main Query in which we will use the Values from the first Query as a Parameter to filter Data for the Report. Our first Query must come out with the result values 3,6,9 & 12 as per the Month selection shown on the image above. The following SQL string is used for this purpose:

Query Name: Month_ParamQ

SELECT Month_Parameter.MTH
FROM Month_Parameter
WHERE (((Month_Parameter.[SELECT])=True));

When the User puts check marks on the Parameter screen the selection may not immediately update in the underlying Month_Parameter Table. To update the change we have to Refresh the Month_Parameter Sub-Form before opening the Report that pulls data from the Main Query that uses the above Query as criteria. For that, we have written a statement on the On_Click() Event Procedure of the Print Preview Command Button as below.

Private Sub cmdPreview_Click()
     Me.Month_Parameter.Form.Refresh
     DoCmd.OpenReport "myNewReport", acViewPreview
End Sub

Now, how the selected months filtered in the Month_ParamQ can be used in the Main Query as criteria? It is easy, to look at the third method we have used as criteria in the first Image given above. I will repeat it here below:

IN(1,5,7,8)

Here, we will compare the EmployeeID values, with the number 1,5,7,8 and select records that match any of these numbers as output.

Similarly, all we need to do here in the Main Query is to write this as a Sub_Query in the Criteria Row to use the Month Values from the Month_ParamQ. The above criteria clause, when written in the form of a sub-query will look like the following:

IN(SELECT MTH FROM MONTH_PARAMQ)

The User doesn't have to type the Parameter values for the Report, only put check marks on the required items, click a Button and the Report is ready.

• Database Connection String, Properties
• Opening Excel Database Directly
• Display Excel Value Directly on Form
• Opening dBase Files Directly
• Opening External Data Sources

Read More

Form Menu Bars and Toolbars

Introduction.

In the development process of a Database, we spent most of our time on creating Tables, setting Relationships, Designing Forms, Reports, and planning process steps to bring the data into Reports so that Users can make the right decisions on time. Microsoft Access is loaded with plenty of Menus and Tool Bars to make these designing tasks effortless.

Once that part is over and is in the finishing stage we think about the security issues and how the Users are going to handle the Database in day-to-day activities and what they should do or shouldn't do and so on. We don't want the Users to mess around with the Forms, Report Design, or any other objects and start implementing their own ideas (maybe better than ours) directly, which can be disastrous.

By effectively implementing Microsoft Access Security features we can regulate what a particular User or Group of Users can do or cannot do. Along with that one other aspect we consider is removing the Default Menu Bars, and Toolbars, and replacing them with carefully organized Custom Menus and Tool Bars so that Users can find the necessary work tools conveniently for their everyday tasks.

The Form/Report-Property Sheet.

When you open a Form or Report in Normal View certain settings on the Form/Report's Properties influence the display of Menus or Tool Bars associated with them. An image of the Form's Property Sheet is given below:

When you click on the Drop Down control on the Menu Bar Property it will display the List of Custom Menu Bars you have designed in the Database (or imported from another Database) and you can select to insert it here. Likewise, Custom Tool Bars and Shortcut Menu Bar can be inserted in other Properties as well. You can turn On or Off the Shortcut Menu Bar of a Form by setting the Property Shortcut Menu = Yes or No respectively.

When you open the Form in Normal View the Custom Menus and ToolBar will display based on the settings on the above Properties.

NB: You can go through the following Posts to learn more about designing Custom Menus and Toolbars:

• Calendar and Toolbars
• Custom Menus and Toolbars
• Custom Menus and Toolbars-2

Automating Menus/Toolbars Setting

When you have several Forms and Reports in your Database opening each one of them in Design View and settings these Properties manually take away all the excitement that we had in designing the Database. But, we can give this job to a VBA Routine and it takes hardly a minute to scan through the entire database for Forms, Reports, and to insert the Custom Menu Bar and Tool Bar names into the above Properties.

Copy and Paste the Following VBA Routine into a Global Module in your Database and save the Module.

Public Function MenuToolbarSetup()
'-----------------------------------------------------------
'Author : a.p.r. pillai
'Date   : September, 1998
'URL    : www.msaccesstips.com
'All Rights Reserved by www.msaccesstips.com
'-----------------------------------------------------------
Dim ctr As Container, doc As Document
Dim docName As String, cdb As Database
Dim msg As String, msgbuttons As Long

On Error GoTo MenuToolbarSetup_Err

Set cdb = CurrentDb
Set ctr = cdb.Containers("Forms")
msgbuttons = vbDefaultButton2 + vbYesNo + vbQuestion
' Set MenuBar, toolbar properties of Forms
msg = "Custom Menus/Toobar Setup on Forms. " & vbCr & vbCr _& "Proceed...?"
If MsgBox(msg, msgbuttons, "MenuToolbarSetup()") = vbNo Then
   GoTo NextStep
End If
For Each doc In ctr.Documents
  docName = doc.Name  
'Open the Form in Design View and hidden mode   
DoCmd.OpenForm docName, acDesign, , , , acHidden
   With Forms(docName)
     .MenuBar = "MyMainMenu"
     .Toolbar = "MyMainToolBar"
     .ShortcutMenu = True
     .ShortcutMenuBar = "MyShortCut"
   End With  
'Save and Close the Form after change
   DoCmd.Close acForm, docName, acSaveYes
Next

NextStep:
'MenuBar,Toolbar properties of Reports
msg = "Custom Menus/Toobar Setup on Reports. " & vbCr & vbCr _& "Proceed...? "

If MsgBox(msg, msgbuttons, "MenuToolbarSetup()") = vbNo Then
   GoTo MenuToolbarSetup_Exit
End If

Set ctr = cdb.Containers("Reports")
'Reports cannot be opened in hidden mode
For Each doc In ctr.Documents
 docName = doc.Name
 DoCmd.OpenReport docName, acViewDesign
 Reports(docName).MenuBar = "MyMainMenu" 
Reports(docName).Toolbar = "MyReportToolBar" 
DoCmd.Close acReport, docName, acSaveYes
Next

msg = "Custom Menus/Toobar Setup Completed successfully. "

MsgBox msg

Set ctr = Nothing
Set cdb = Nothing

MenuToolbarSetup_Exit:
Exit Function

MenuToolbarSetup_Err:
MsgBox Err.Description
Resume MenuToolbarSetup_Exit
End Function

Run the Code from Debug Window

Since it is only a design-time task, you can run this Code directly by placing the cursor in the body of the Code and by pressing the F5 Key. Or Call it from the On_Click() Event Procedure of a Command Button on a Form.

But remember this Form (with the Command Button) also will be opened in Design View for setting the Property Values. If you don't need that to happen then write an If . . .Then statement in the Code to bypass this Form.

• Opening Excel Database Directly
• Display Excel Value Directly on Form
• Opening dBase Files Directly
• Opening External Data Sources
• PIE Chart Object and VBA

Read More

Seriality Control Finding Missing Numbers

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

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

• Display Excel Value Directly on Form
• Opening dBase Files Directly
• Opening External Data Sources
• PIE Chart Object and VBA
• Column Chart and VBA

Read More

Wave Shaped Reminder Ticker

Introduction.

We have already seen how to create and install a Reminder Ticker that runs in a straight line on the Main Screen. We could do this with a few lines of VBA Code and a Label on the Main Screen. We will try something different this time. This ticker runs in a Zigzag form. An image of a sample run of this method is given below

To create this Ticker we need a series of labels arranged in a wavelike form and each one of them must be named in such a way that we could address them easily in code. A sample design is given below:

Automating the Labels Creation

There are about 42 identical Labels to create. Even if we create them once manually, arranging them in this fashion is not an easy task. But we can do it with a small Program. The Program creates a new Form and creates all 42 Labels, and gives names like lbl1 to lbl42, changes other Properties as shown above.

1. Copy the following Code into a Global Module of your Database and save it.
   

   Public Function ZIGZAG()
   '-----------------------------------------------------------
   'Author  :  a.p.r. pillai
   'Date    :  01/10/2008
   'URL     :  www.msaccesstips.com
   'All Rights Reserved by www.msaccesstips.com
   '-----------------------------------------------------------
   Dim frm As Form, ctrl As Label, t As Long, lngleft As Long
   Dim lngwidth As Long, lngheight As Long, lngtop As Long
   Dim j As Integer, k As Integer, h As Long, G As Long
   
   h = 30: G = 0: t = 0
   lngwidth = 0.1146 * 1440
   lngheight = 0.2083 * 1440
   lngtop = 1 * 1440
   lngleft = 0.16667 * 1440
   Set frm = CreateForm
   For j = 1 To 42
   Set ctrl = CreateControl(frm.Name, acLabel, acDetail, , , lngleft, lngtop, lngwidth, lngheight)
   lngleft = lngleft + lngwidth
   With ctrl
       .Name = "lbl" & j
       .FontName = "Tahoma"
       .FontSize = 8
       .Caption = ""
       .BackStyle = 0
       .ForeColor = 255
    End With
    Next
   G = 0
   For j = 1 To 3
        For k = 1 To 7
           G = G + 1
           Set ctrl = frm.Controls("lbl" & G)
           With ctrl
               .Top = .Top - (h * k)
           End With
           DoEvents
        Next
       t = frm.Controls("lbl" & G).Top
        For k = 1 To 7
            G = G + 1
           Set ctrl = frm.Controls("lbl" & G)
           With ctrl
               .Top = t + (h * 1)
           End With
           t = frm.Controls("lbl" & G).Top
           DoEvents
       Next
   Next
   End Function
   

2. You can run the above Code directly by placing the cursor in the middle of the Code and pressing the F5 Key, or running from a Command Button's On Click Event Procedure or a Macro.

   Every time when the code is run it will create a new Form with the Labels arranged in a zigzag form. After you create it once, export that Form to your other Projects where you want to install the ZigZag Ticker. Or better you may install the code in a Common Library Database and run it from your new project, after attaching the Library File to your Project.

   Placement of the Ticker Labels.

3. After creating the Labels, click somewhere outside the Labels and drag over them so that all the Labels are selected without disturbing the arrangement of the labels.
4. Select Copy from Edit Menu.
5. Open the Main Switch Board (Control Form) in Design View and Paste them.
6. When all the labels are still in the selected state drag and place the Labels into a position where you want the Ticker to appear on the Form.

   We have two more Sub-Routines which are run from the Form_Load() and Form_Timer() Event Procedures. In the Form_Load() Event Procedure, we can create a Text Message in a String either with a constant value or with Field Values from a Table/Query that provide useful information to display to the User as a reminder. Refer to the earlier example Reminder Ticker Form which uses information from within the Application for Reminder.

   Formatting Ticker Text

   The Form_Timer() Event Procedure will control the Display of Label values shifting one character at a time in succeeding labels giving it a sense of motion.

7. Copy and Paste the following Sub-Routines into the Form Module where you have pasted the above labels.
   

   Option Compare Database
   Option Explicit
   Dim txt As Variant
   
   Private Sub Form_Load()
       txt = Space(42) & UCase("Excellence is not a matter of chance. It is a matter of Change. It is not a thing to be waited for. It is a thing to be achieved.")
       Me.Timerinterval=250
   End Sub
   

8. See that the Dim txt As Variant is placed in the Global Area of the Module, which is referenced from the Form_Load() and Form_Timer() Event Procedures.

   Private Sub Form_Timer()
   Dim x As String, k As String, j As Integer, ctrl As Control
   
     x = Left(txt, 1)
     txt = Right(txt, Len(txt) - 1)
     txt = txt & x
     k = Left(txt, 42)
   For j = 1 To Len(k)
       Set ctrl = Me.Controls("lbl" & J)
       Ctrl.Caption = Mid(k, j, 1)
   Next
   End Sub 
   

   Disable Ticker on inactive Form

9. The following lines of code are useful if you plan to disable the ticker when the Main Form is inactive and run it when the Main Form is active again so that other processes are not interrupted by the Ticker.

Private Sub Form_Deactivate()
   Me.TimerInterval = 0
End Sub

Private Sub Form_Activate()
   Me.TimerInterval = 250
End Sub

Download

Download Demo Database Access2007.zip


Download Demo Database Access2002-03.zip

• Opening dBase Files Directly
• Opening External Data Sources
• PIE Chart Object and VBA
• Column Chart and VBA
• Working with Chart Object in VBA

Read More