Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Multiple Parameters For Query

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 effortless 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 Design view of the Query.
  2. Creates Text Boxes 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 Text Boxes 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 setting on 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 Text Boxes from a Form too.

    What I prefer to do in these cases, create a small table (let us call it as 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 the User can conveniently key in the date range values into the Table.

    This table will be included in the main Query and the StartDate, 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 for the Report or for other outputs based on this date-range 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 Endate. But there are times that we need data of 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 example image given below:

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 by 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 Sub-Form on a Main Form) is given below:

We need two Queries, one to filter the selected months from the list and second the Main Query in which we will use the Values from the first Query as 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 it:

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, 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 are asking to compare the EmployeeID values with the numbers 1,5,7,8 and select records that match with 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 statement when written in the form of a sub-query it will look like the following:

IN(SELECT MTH FROM MONTH_PARAMQ)

The User don'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.

Share:

Form Menu Bars and Toolbars

In the development process of a Database we spent most of our time on creating Tables, setting Relationships, Designing Forms, Reports, planning process steps to bring the data in Reports so that Users can make 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 on 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, Reports or any other objects and start implementing their own ideas (may be 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 removal of the Default Menu Bars, Tool Bars and replacing them with carefully organized Custom Menus and Tool Bars so that Users can find the necessary work-tools conveniently for their every day tasks.

When you open a Form or Report in Normal View certain settings on the Form/Report's Properties influences 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 other Database) and you can select to insert it here. Like-wise 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 Tool Bar 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:

When you have several Forms and Reports in your Database opening each one of them in Design View and settings these Properties manually takes 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

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

Share:

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:

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

Share:

Wave Shaped Reminder Ticker

We have already seen how to create and install a Reminder Ticker that runs on a straight line on the Main Screen. We could do this with few lines of VBA Code and a Label on the Main Screen. We will try something different this time. This one not runs on a straight line but 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 wave like 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:

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, name them as lbll 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 F5 Key, or running from a Command Button's On Click Event Procedure or from 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 into your other Projects where you want to install the ZigZag Ticker. Or you may install the code in a Common Library Database and run it after attaching the Library File to your Project.

  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 selected state drag and place the Labels into 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. On 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 the earlier example Reminder Ticker Form which uses information from within the Application for Reminder.

    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 the 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 
    
  9. The following lines of code are useful if you plan to disable the ticker when the Main Form is inactive and run it again when the Main Form is active again, so that other processes not interrupted by the Ticker.
Private Sub Form_Deactivate()
   Me.TimerInterval = 0
End Sub

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


Download Demo Database Access2002-03.zip


Download Demo Database Access2007.zip


Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Class Module Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Objects Downloads Menus and Toolbars Property MsaccessLinks Process Controls Art Work Event WithEvents Collection Object Controls msaccess How Tos Graph Charts VBA msaccessQuery Combo Boxes List Boxes Command Buttons Data Emails and Alerts Form Query Report Calculation Command Button Custom Wizards DOS Commands Data Type Dictionary Object RaiseEvent ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database 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 Excel 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 function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents TexBox and CommandButton Control Arrays

Introduction. Hope that you have gone through last three Posts on Report based Event Trapping in Class Module and modifying Report controls ...

Labels

Blog Archive

Recent Posts