Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Progress Meter

Introduction

When we process large volume of data in MS-Access for Reports it may take few minutes to several minutes to complete them, depending on the size of the transactions involved. We have to run VB Routines or several Action Queries, which takes data from other Queries or Tables, and sequence the process steps through Macros to complete them. If you have Queries of Select Type or Action Query that pulls data from Crosstab Queries and if large volume of transactions involved, it takes longer than the Normal Queries to prepare the output.

In all these situations it will be difficult to the user to know how long the whole process will take to complete the task. After running the process few times the user may get a rough idea as how long it will take. Normally at the beginning of a lengthy process the Mouse Pointer can be turned on into an Hourglass shape (Docmd.Hourglass True) indicating that the machine is engaged and at end of the program we can turn it off. But, this method will not give an exact indication when the process will be over and every time the user looks at it and if it takes a little more time than usual he/she gets worried. If it takes more than the usual time, depending on other factors, like increase in volume of transactions or due to busy network traffic and so on, it is difficult to determine whether the process is really running or we are facing a machine hang up.

The Quick Solution

When we run several Action Queries in chain from within a Macro, MS-Access displays a Progress Meter for a brief moment for each Query on the Status Bar. If the Status Bar is not visible you can turn it on. Select Options from Tools menu, select View Tab on the displayed Dialog Control. Put check mark on the Status Bar option under Show Option Group. But it will not give an overall time indicator for the full process.

A Better Approach

We will make use of the Progress Meter for our data processing tasks more effectively and will look into more than one method. The users of our Application can relax during the whole process and take little time off to flip through the Weekly Magazine with an occasional glance on the Progress Meter.

  1. Displaying the Progress Meter on the Status Bar
  2. Displaying the Progress Meter on a Form
  3. Usage of a transactions count down method

Usage of Progress Meter on the Status Bar.

We will use the Order Details Table from the Northwind.mdb sample database for our example and write a VB Routine to calculate the Extended Price on each entry in this Table. If you have not already imported this Table for our earlier examples you may do it now. If you don't know the exact location of this File on your machine please visit the page Saving Data on Forms not in Table for references.

  1. Import the Order Details Table from Northwind.mdb sample Database.
  2. Open the Order Details Table in Design View and add a new field with the name ExtendedPrice (Field Type: Number, Field Size: Double) at the end of the existing fields. We will write a program to calculate ExtendedPrice of each record and update this field.
  3. Design a simple Form similar to the one shown below with a Command Button on it. We will modify this Form for our next example also.
  4. Click on the Command Button and display the Property Sheet (View -> Properties).
  5. On the On Click Property type =ProcessOrders() to run the Program, which we are going to write now. Do not forget the equal sign in =ProcessOrders(), otherwise MS-Access will take it as a Macro name.
  6. Close the Form and save it with the name ProgressMeter.

    Usage of SysCmd().

  7. Copy and Paste the following Code into a Global VB Module of your Project and save it.
    Public Function ProcessOrders()
    Dim db As Database, rst As Recordset
    Dim TotalRecords As Long, xtimer As Date
    Dim ExtendedValue As Double, Quantity As Integer
    Dim Discount As Double, x As Variant, UnitRate As Double
    
    On Error GoTo ProcessOrders_Err
    
    DoCmd.Hourglass True
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("Order Details", dbOpenDynaset)
    rst.MoveLast
    TotalRecords = rst.RecordCount
    
    rst.MoveFirst
    Do While Not rst.EOF
      With rst
        Quantity = ![Quantity]
        UnitRate = ![UnitPrice]
        Discount = ![Discount]
        ExtendedValue = Quantity * (UnitRate * (1 - Discount))
    
        .Edit
        ![ExtendedPrice] = ExtendedValue
        .Update
    
        If .AbsolutePosition + 1 = 1 Then
           x = SysCmd(acSysCmdInitMeter, "process:", TotalRecords)
        Else
          'a delay loop to slow down the program       
          'to view the Progress Meter in action.      
          'you may remove it.      
    '=================================================
          xtimer = Timer
          Do While Timer < xtimer + 0.02
            Doevents
          Loop
    '=================================================
    
          x = SysCmd(acSysCmdUpdateMeter, .AbsolutePosition + 1)
        End If
    
       .MoveNext
      End with
    Loop
    rst.Close
    x = SysCmd(acSysCmdRemoveMeter)
    DoCmd.Hourglass False
    
    MsgBox "Process completed.", , "ProcessOrders()"
    
    Set rst = Nothing
    Set db = Nothing
    
    ProcessOrders_Exit:
    Exit Function
    
    ProcessOrders_Err:
    MsgBox Err.Description, , "ProcessOrders()"
    Resume ProcessOrders_Exit
    
    End Function
  8. Open the ProgressMeter Form in normal view and click on the Command Button. You will see the Progress Meter slowly advancing and when it reaches the end of the bar a message will appear announcing that the work is complete.

We have used MS-Access's built-in Function SysCmd() to update the Progress Meter on the Status Bar. When the Function is first called, the Maximum number of Records in the File is passed as third parameter to the function to initialize the Progress Meter. Subsequent calls are made with the current record number to update the Meter with current status. MS-Access calculates a percentage on the current number of records processed based on the Total Records that we have passed to the InitMeter step and updates the Progress Meter. The blue colored indicator on the Progress Meter may advance one step, only after processing several records depending on the total number of records in the file.

A delay loop is built into the Code to slow down the program and view the Progress Meter in action. You may remove these lines when using in your Project.

Need a Better Method

We cannot use this method when we sequence our process steps in Macros involving Queries. Because, when each Action Query is run MS-Access uses the Status Bar to display the progress of each and every query separately overwriting our overall process time meter. We have to device a method of our own to do this.

Next we will see the usage of a Progress Meter on a Form, for the Data Processing steps sequenced through Macro.

Download



Download Access2003 Version


Download Access2007 Version


Share:

Keyboard Shortcuts

Introduction


Keyboard Shortcuts are very useful, if practiced and memorized, for frequently used actions without shifting back and forth between mouse and keyboard. Microsoft Access has an extensive list of Keyboard Shortcuts to make our work much easier. You can enter the search term Keyboard Shortcuts on the Help Menu to get a category-wise list. I have selected some of them and given at the end of this Post for reference.

If you look carefully on the MS-Access Menu Options you can find one letter in each Menu Item is underlined indicating that it is a Keyboard Shortcut Code.

For example: the letter E in Edit menu is underlined indicating that you can open the Edit Menu using ALT+E Keyboard Shortcut. Most of the Options within the Edit Menu also have keyboard shortcut Codes at the right side of each one.


Defining Custom Shortcuts

When we design Forms we can define our own custom Keyboard Shorts (Access Keys) on the Form Controls in a similar way with the help of & symbol. If you insert & symbol at the left side of a character in the Caption of the Command Button (or in the Caption of a Label) you can use ALT+character to make the Command Button act as if it is being clicked and runs the action programmed into that Button.

For example: If you have a Command Button on a Form with the Caption Export you can write the Caption as &Export; so that ALT+E Keyboard Shortcut can be used to run the Export action. When we define keyboard shortcuts this way we should see that it is not ending up with the same built-in Shortcuts. Here, we already have a Built-in Keyboard Short &Edit;, which uses ALT+E. We can insert the & symbol to the left side of any letter within the word Export (like Ex&port for ALT+P) to avoid conflict with the built-in codes.

There are other methods to define Keyboard Shortcuts. You can put all your Custom Keyboard Shortcuts in a Macro for specific actions, like Print Previewing/Printing Report, for opening a Particular Form in Design View or Normal View, Run a Macro to Process Report Data etc. You must name the Macro as Autokeys. An image of the Example Macro is given below.

The first line Action OpenReport runs when the CTRL+O keys are used, CTRL+K runs another Macro.

If you assign an action to a key combination that is already being used by MS-Access (for example, CTRL+C is the key combination for Copy), the action you assign this key combination replaces the Access key assignment.


AutoKeys

A List of Key combination that you can use in your Autokeys Macro is given below.

Key Codes for Autokey Macro
Key Code Key Press
^A or ^4 CTRL+A or CTRL+4
{F1} F1
^{F1} CTRL+F1
+{F1} SHIFT+F1
{INSERT} INS
^{INSERT} CTRL+INS
+{INSERT} SHIFT+INS
{DELETE} or {DEL} DEL
^{DELETE} or ^{DEL} CTRL+DEL
+{DELETE} or +{DEL} SHIFT+DEL

If you want a Macro to run automatically when you open a database, name the Macro as Autoexec.


General Purpose Keyboard Shortcuts

A list of useful general purpose Keyboard Shortcuts are given below for your reference.

Seq Key Strokes Action
SAVING AND PRINTING
1. CTRL+S or SHIFT+F12 or ALT+SHIFT+F2 To save a Database Object
2. F12 or ALT+F2 To open the Save As... dialog box
3. CTRL+P To Print the Current or Selected Object
FINDING REPLACING
4. CTRL+F To open the Find tab in the Find and Replace dialog box (Datasheet view and Form view only
5. CTRL+H To open the Replace tab in the Find and Replace dialog box (Datasheet view and Form view only)
6. SHIFT+F4 To find the next occurrence of the text specified in the Find and Replace dialog box when the dialog box is closed (Datasheet view and Form view only)
WORKING IN DESIGN VIEW
7. F2 To switch between Edit mode (with insertion point displayed) and Navigation mode
8. F4 To switch to the property sheet (Design view in forms and reports in databases and Access projects)
9. F5 To switch to Form view from form Design view
10. F6 To switch between the upper and lower portions of a window (Design view of tables, macros, and queries and the Advanced Filter/Sort window only)
11. SHIFT+F7 To switch from the Visual Basic Editor to form or report Design view
12. ALT+V+P To open property sheet for a selected object
Editing Controls in Form and Report Design View
13. CTRL+C To copy the selected control to the Clipboard
14. CTRL+X To cut the selected control and copy it to the Clipboard
15. CTRL+V To paste the contents of the Clipboard in the upper-left corner of the selected section
16. CTRL+RIGHT ARROW To move the selected control to the right
17. CTRL+LEFT ARROW To move the selected control to the left
18. CTRL+DOWN ARROW To move the selected control down
19. SHIFT+DOWN ARROW To increase the height of the selected control
20. SHIFT+RIGHT ARROW To increase the width of the selected control
21. SHIFT+UP ARROW To reduce the height of the selected control
22. SHIFT+LEFT ARROW To reduce the width of the selected control
WINDOW OPERATIONS
23. F11 To bring the Database window to the front
24. CTRL+F6 To cycle between open windows
25. ENTER To restore the selected minimized window when all windows are minimized
26. CTRL+F8 To turn on Resize mode for the active window when it's not maximized; press the arrow keys to resize the window
27. ALT+SPACEBAR To display the Control menu
28. SHIFT+F10 To display the shortcut menu
29. CTRL+W or CTRL+F4 To close the active window
30. ALT+F11 To switch between the Visual Basic Editor and the previous active window
MISCELLANEOUS
31. F7 To check spelling
32. SHIFT+F2 To open the Zoom box to conveniently enter expressions and other text in small input areas
33. ALT+ENTER To display a property sheet in Design view
34. ALT+F4 To quit Microsoft Access, close a dialog box, or close a property sheet
35. CTRL+F2 To invoke a Builder


Share:

Find or Filter Data on Form

Introduction

When I started learning Microsoft Access in 1996 the first challenge that I have faced was how to find a particular record on the Data Editing Screen or Filter a group of records on some condition.

I was asked to develop a System for the Vehicles Division of our Company, for tracking pending Orders and Receipts of Heavy Equipments and Vehicles. If I am the one who is going to use the System, then some how I could manage to find the information that I want to work with and nobody will know how much time I spent for doing that. But it is going to be used by someone else and it is my job to make it as user friendly as possible. Even though I have good knowledge of BASIC Language at that time, knew nothing about Access Basic (MS-Access Ver.2 language, a primitive form of Visual Basic).

I struggled with the problem for a while and finally decided to have a look in the Northwind.mdb sample database for clues. There it was, on the Customer Phone List Form, the technique that I was struggling for so long. Within an Option Group Control, 27 Buttons with Labels A-Z, All to filter Records of Company Name starting with the respective letter on the Button or to remove the filter using the All labeled button. The Option Group is linked to a Macro. When any of the button on the option group is clicked, the Company Names starting with that letter is filtered from the Customer List.

There were no second thoughts on this and I straight away transplanted this method on my first MS-Access Project. It was developed without touching a single line of Access Basic Code, all automated procedures are run with macros only.

We will look into the Finding/Filtering records using three different methods on the same Form. You can use any one of the three methods or all of them in your Project. We will use the Products Table from the Northwind.mdb sample Database.


Get Sample Table and Design a Form

  1. Import the Products Table from the Northwind.mdb Database. Visit the Page Saving Data on forms not in Table to find the location reference of the Northwind.mdb database, if you are not sure where to find it.
  2. Click on the Table and select Form from Insert menu and select Autoform: Columnar from the displayed list, click OK to create the Form and save it with the suggested name: Products.

  3. Display the Form Header/Footer Sections, if not already visible. Select Form Header/Footer from View Menu.
  4. Create a Label on the Header Section of the Form and type Products List as Caption. Click on the Label and change the Font size to 20 or more, to your liking, and make it Bold using the Format Toolbar above.

    NB: If you would like to create a fancy 3D Style Heading then visit the Page: Shadow 3D Heading Style and follow the procedure explained there.

  5. Create a Command Button at the Footer Section of the Form. Display the Property Sheet of the Button (Alt+Enter or select Properties from View Menu). Change the Property Values as shown:
    • Name = cmdExit
    • Caption = Exit
  6. Design a Text Box and four Command Buttons on the Form, as shown in the shaded area of the Form. Change the property values of the Text Box and Buttons as given below:
  7. Click on the Text Box, Display the Property Sheet and change the property values:
    • Name = xFind
    • Back Color = 0
    • Text Box child Label Caption = Find / Filter Product Name
  8. Click on the first button, Display the Property Sheet and change the property Values.
    • Name = FindPID
    • Caption = << Product ID
    • Fore Color = 128
  9. Click on the second button, Display the Property Sheet and change the property Values.
    • Name = FindFirstLetter
    • Caption = << First Letter
    • Fore Color = 128
  10. Click on the third Button, Display the Property Sheet and change the property Values.
    • Name = PatternMatch
    • Caption = << Pattern Match
    • Fore Color = 128
  11. Click on the fourth Button, Display the Property Sheet and change the property Values.
    • Name = cmdReset
    • Caption = Reset Filter
  12. Display the Visual Basic Module of the form, select Tools . . . > Macros . . . > Visual Basic Editor when the Products Form is still in Design View.
  13. VBA Code of Form Class Module

  14. Copy and paste the following Code into the VB Module of the Form and save the Form:

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub cmdReset_Click()
'Remove Filter effect
'Clear Text Box

Me!xFind = Null
Me.FilterOn = False
End Sub

Private Sub FindPID_Click()
'Find Record matching Product ID
Dim m_find, rst As Recordset

m_find = Me![xFind]
If IsNull(m_find) Then
   Me.FilterOn = False
   Exit Sub
End If

If Val(m_find) = 0 Then
  MsgBox "Give Product ID Number..!"
   Exit Sub
End If

If Val(m_find) > 0 Then
   Set rst = Me.RecordsetClone
   rst.FindFirst "ProductID = " & m_find
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
End If

End Sub

Private Sub FindfirstLetter_Click()
'Filter Names matching First character
Dim xfirstletter

xfirstletter = Me![xFind]
If IsNull(xfirstletter) Then
  Me.FilterOn = False
  Exit Sub
End If
If Val(xfirstletter) > 0 Then
   Exit Sub
End If

xfirstletter = Left(xfirstletter, 1)
Me.FilterOn = False
Me.Filter = "Products.ProductName Like '" & xfirstletter & "*'"
Me.FilterOn = True

End Sub

Private Sub PatternMatch_Click()
'Filter Names matching the group of characters
'anywhere within the Name
Dim xpatternmatch

xpatternmatch = Me![xFind]
If IsNull(xpatternmatch) Then
  Me.FilterOn = False
  Exit Sub
End If

Me.FilterOn = False
Me.Filter = "Products.ProductName Like '*" & xpatternmatch & "*'"
Me.FilterOn = True
End Sub



How it Works

  1. Usage of << Product ID Button.
    • Click the Button with a Number less than or equal to the Product Code range of values in the Text Box.
    • If clicked with Text Value it will ask for Product ID Number.
    • If clicked when the Text Box is empty, it is same as clicking Filter Reset Button.
  2. Usage of << First Letter Button.
    • Click the Button with any Alphabet A to Z or a to z in the Text Box.
    • If more than one character is entered only the first character will be taken.
    • If Numeric Value is entered the filter action will be ignored.
    • If clicked when the Text Box is empty, it is same as clicking the Filter Reset Button.
  3. Usage of << Pattern Match Button.
    • Click the Button with group of characters that to match anywhere within the Product Name.
    • If clicked when the text box is empty, it is same as clicking the Filter Reset Button.
  4. Usage of Filter Reset Button.
    • Resets the earlier applied Filter action.
    • Empties the Text Box Control.


Share:

Who is Online

Introduction

As we already knew we can install Access Database on a Local Area Network (LAN) and several Users can work on it at the same time. But it is difficult to know how many users are actually online with the Application at a given time.

Similarly, when there are several MS-Access Databases on a Network under a centrally controlled Security Workgroup it will be interesting to know which users are currently working on the MS-Access Applications on the Network.

You don't need any complicated programs to find this out. When someone opens a Database, say NorthWind.mdb, MS-Access opens a Lock-File with the same name of the Database with file extension .ldb (Northwind.ldb) in the same folder of the Database. You can open this file with Notepad or any other Text Editor and view the contents.

This file contains the Workstation Ids and MS-Access User IDs of all the active Users of the Database. The lock file will be deleted by MS-Access when all the Users close the database. In other words if a Lock File is not active then nobody is currently using the database.

We can implement MS-Access Security of several Databases with a common Workgroup File in a Network holding Workgroup IDs, User IDs and User Passwords. In such situations there will be a common Workgroup File (a Database with .MDW extension) accessible to all the Users in a Network. When someone Log-in into the Workgroup to open a database, a lock-file with .ldb extension will open up with the same name of the Workgroup file. This file will contain the Workstation IDs and User IDs of all the Users currently active across different databases.

We will design a Form and write Programs to open the Lock-File of a Database or Workgroup File and display the List of Workstation IDs and User IDs currently active. In addition to that we will try to send messages through Network to the selected Users from this list.

Designing a WhoIsOnline Form

  1. Design a Form similar to the one shown below and change the Properties of the Controls as explained. Smaller controls are Text Boxes and the big Control under the label Users Online is a List Box.

  2. Click on the Text Box below the label: Workgroup or Database File Path and display the property sheet. Change the value of the Name Property to FPath.
  3. Display the Property Sheet of the List Box and change the value of the following properties as given below:
    • Name = UsrList
    • Row Source Type = Value List
    • Column Count = 2
    • Column Heads = No
    • Column Width = 1.25";1.25"
    • Bound Column = 1
    • Multi Select = simple
  4. Display the property sheet of the Text Box above the Send Message Button and change the value of the Name Property to msgtxt.
  5. Display the Property Sheet of the Button with the caption < and change the value of the Name Property to cmdUpdate.
  6. Display the Property Sheet of the Button with the Caption Send Message and change the value of the Name property to cmdNS.
  7. Save the Form with the name; whoisonline.

    NB: We are referencing all the above Property Values in Programs and it is important that you change the Values of respective controls as explained above.

    Copy and Paste the following Code into the VBA Module (Select Tools > Macro > Visual Basic Editor to display the VBA Module) of the whoisonline Form, Save and Close the Form.

    Form Class Module VBA Code

    Private Sub cmdNS_Click()
    '-------------------------------------------------------
    'Author: a.p.r. pillai
    'Date  : 01/12/2007
    '-------------------------------------------------------
    Dim ctl As ListBox, msg As String, usrs As String
    Dim i As Integer, pcs() As String, xmsg As String
    Dim ic As Integer, j As Integer, lc As Integer
    
    On Error GoTo cmdNS_Click_Err
    
    xmsg = Nz(Me.msgtxt, "")
    
    If Len(xmsg) = 0 Then
       MsgBox "Message Control is Empty.", , "cmdNS_Click()"
       Exit Sub
    End If
    
    Set ctl = Me.UsrList
    lc = ctl.listcount
    ic = ctl.ItemsSelected.Count
    
    If ic > 0 Then
    ReDim pcs(1 To ic, 1 To 2)
    i = 0
    For j = 0 To lc - 1
    If ctl.Selected(j) Then
        i = i + 1
        pcs(i, 1) = ctl.Column(0, j)
        pcs(i, 2) = ctl.Column(1, j)
    End If
    Next
    
    usrs = ""
    For j = 1 To i
    msg = "NET SEND " & pcs(j, 1) & " User: " & pcs(j, 2) & " " & xmsg
      Call Shell(msg)
      If Len(usrs) = 0 Then
         usrs = j & ". WorkStationID: " & pcs(j, 1) & "  UserID: " & pcs(j, 2)
      Else
         usrs = usrs & vbCr & j & ". WorkStationID: " & pcs(j, 1) & "  UserID: " & pcs(j,2)
      End If
    Next
      MsgBox "Messages Sent to:" & vbCr & vbCr & usrs & vbCr & vbCr & "successfully.", , "cmdNS_Click()"
    Else
      MsgBox "Select one or more Users in list and try again.", , "cmdNS_Click()"
    End If
    
    cmdNS_Click_Exit:
    Exit Sub
    
    cmdNS_Click_Err:
    MsgBox Err.Description, , "cmdNS_Click()"
    Resume cmdNS_Click_Exit
    End Sub
    
    Private Sub cmdUpdate_Click()
        Me.UsrList.SetFocus
        Me.cmdUpdate.Enabled = False
       WhoisOnline Me.FPath
        Me.cmdUpdate.Enabled = True
    End Sub

    The Main Program

  8. Copy and Paste the following VBA Code into a Global Module in your Project and save:

    Public Function WhoisOnline(ByVal strPathName)
    '-------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   : 01/12/2007
    '-------------------------------------------------------
    Dim strldb() As String, j As Integer, strpath As String
    Dim str As String, intlen As Integer, t As Date
    Dim pos As Integer, xsize As Integer, l As Integer
    Dim FRM As Form, ctl As ListBox, qt As String
    Dim x As String
    
    Const strlen As Integer = 62
    
    On Error Resume Next
    
    qt = Chr$(34)
    
    strpath = Trim(Nz(strPathName, ""))
    
    If Len(strpath) = 0 Then
      MsgBox "File Path is Empty.", , "WhoisOnline()"
      Exit Function
    End If
    
    str = Right(strpath, 4)
    l = InStr(1, ".mdb.mdw.ldb", str)
    Select Case l
        Case 0
          strpath = strpath & ".ldb"
        Case 1, 5
          strpath = Left(strpath, Len(strpath) - 4) & ".ldb"
        Case 9       'it is .ldb no action
    End Select
    
    Set FRM = Forms("WhoisOnline")
    Set ctl = FRM.Controls("UsrList")
    
    Open "c:\x.bat" For Output As #1
    Print #1, "@Echo off"
    Print #1, "copy " & strpath & " c:\xx.txt"
    Close #1
    
    Call Shell("c:\x.bat", vbHide)
    
    x = Dir("c:\xx.txt")t = Timer
    Do While Len(x) = 0 And Timer < (t + 2)
       x = Dir("c:\xx.txt")
    Loop
    
    t = Timer
    Do While Timer < (t + 2)   'do nothingLoop
    
    Open "c:\xx.txt" For Input As #1
    If Err > 0 Then
      'Lock file copying was not successfull
      MsgBox "Database is idle.", , "WhoisOnline()"
      Exit Function
    End If
    Input #1, str
    Close #1
    
    intlen = Int(Len(str) / strlen) + 1
    ReDim strldb(1 To intlen, 1 To 2) As String
    xsize = strlen / 2
    
    For j = 1 To intlen
      pos = IIf(j = 1, 1, (j - 1) * strlen + 1)
      strldb(j, 1) = Trim(Mid(str, pos, xsize))
      pos = pos + strlen / 2
      strldb(j, 2) = Trim(Mid(str, pos, xsize))
    Next
    
    str = ""
    For j = 1 To intlen
      If Len(str) = 0 Then
        str = qt & Trim(strldb(j, 1)) & qt & ";" & qt & Trim(strldb(j, 2)) & qt
      Else
        str = str & ";" & qt & Trim(strldb(j, 1)) & qt & ";" & qt & Trim(strldb(j, 2)) & qt
      End If
    Next
    ctl.RowSource = strctl.Requery
    Kill "c:\x.bat"
    Kill "c:\xx.txt"
    
    End Function

    In the above program we are creating a DOS Batch File (c:\x.bat) and running it, to make a copy of the Lock-File c:\xx.txt as a work file.

    The Demo Run

  9. Open the whoisonline Form in Normal View. Type the full path of an active database (Tip: give the Path and Name of the Database you are currently working on, if you are not on a Network). If you are on a Network and have a common MS-Access Workgroup File, which other Users are currently connected then give that File's Path and Name. No need to give File Name extensions .mdb, .mdw or .ldb, but if you wish to include you may do so for clarity.
  10. Click on the Button with < symbol.
  11. Select one or two Workstation IDs in the List Box.
  12. Type a Message in the Text Box Control above the Send Message Button for the selected Users.
  13. Click on the Send Message Button. The Message Text will pop up on the selected Users' Machines.

NB: If you have selected your own machine name then the Send Message action will not work.

Download Demo Database



Download Demo Database


Share:

Automated Email Alerts


Continued from previous Post: Configure Outlook for Lotus Notes.

The next procedure, that we are going to look into, is applicable for both Internet as well as Intranet Emails. We have already configured Microsoft Outlook with Lotus Notes Profile for those who would like to try sending scheduled Emails from MS-Access in their Local Area Network (LAN).

When we invoke the SendObject Action to send Emails with applicable parameters, the Target Addresses will be picked up from the default MS-Outlook Profile (Internet or Lotus Notes) and transmit Emails to destination.

We will send the Mail Alerts on a weekly schedule and little more preparation is required for this event. We need two small tables for the Program.

Address Book Table

  1. A Local Address Book Table to keep the Recipient's Addresses in Access.

    NB: It is possible to attach MS-Outlook Address Book or Lotus Notes Address Book to Microsoft Access Database.

    We may be having Email addresses of many individuals in our Contact List in MS-Outlook or Lotus Notes. But, we are planning to send the automated Mail Alerts regularly only to few people who are involved with the subject matter. We will keep a list of those email addresses picked from the Address Book of the configured profile (please see the fifth image in the earlier Post: Configure Outlook for Lotus Notes) in a local table. Sample image of the local Address Table for Lotus Notes Mail is given below:

    We can read the contents of this Table with Code and create the To. . . & Cc. . . Field values of the Email message.

    Lotus Notes Email Addresses are very lengthy with Location Names, Department Names and other details separated with forward slash, like

    Nancy Davollio/Ohio/hrd/Orgzn01@Orgzn01.

    In our local address table we only need to keep the First-Name, Last-Name part (Nancy Davolio) to use as Mail ID. When the Mail is send through Lotus Notes it will find and pick the full Email Addresses using the First Name, Last Name part from its main Address Book and insert them into the Mail.

    Suppose the contents of the Mail attachment (the Report), should go to only certain recipients in our address book on a particular day and we don't want others to receive the Mail. To implement such type of refinement in the mail sending procedure we must find a way to filter out unwanted Addresses for the current mail. This can be achieved if some kind of personal identification information (like the employee Code), were already recorded in the main table. The employee code also must be included in the Address Book Table in a separate field (See the ECODE field in the above Address Book image). Having this information in both files we can easily link them together and pick addresses that match with the Report Source Data. Or in Program we can search for the employee code in the address table using the Report Source data Value and pick the email addresses. This way we can ensure that the mails with report attachment goes to the correct recipients and prevented others from getting the mail. But, here we will try the simple method.

    Email Scheduling

  2. Second one is a control parameter table for the Email Program, which holds the weekly mail alert schedule information. The Parameter Table image is given below:



Configuring a Machine

Your Application may be shared by several users but it is not necessary to configure all the PCs for sending emails. Even if they all are, we don't want our mails to go from different machines. When the Application is open in any of those machines the program will perform a check to identify the correct machine to send the mail. For this we will keep the Computer Name in a field to cross-check with the active Machine Name.

You can look for your computer's name in one of two methods explained below.

  1. Right-Click the My Computer Desktop Icon and click on the Properties option in the displayed menu. Select the Computer Name Tab. Your Computer's name will appear at the right side of the Full computer name: label.
  2. Click on Start Menu; select Run . . . then type cmd in the Open control and click OK. The DOS command prompt will appear. Type Set and press Enter Key. This action will display a lengthy list of Environment Variable settings. Among them you will find your computer's name like COMPUTERNAME=X where X stands for your Computer's name.

We can check for this value using VBA Environ() Function with the parameter value "COMPUTERNAME".

X = Environ("COMPUTERNAME")

Our Mail is scheduled to send only once in a week on a particular day, let us say every Monday. We assume that the Application will be opened for normal use every day by the User(s) and every time the Program will perform a check for the mail sending schedule. If it is Monday then the mail must go out only once on that day with the Report attachment, even if the Application is open several times on Monday.

It can also happen that the user forgot to open the Application exactly on Monday or it was a holiday and the Mail schedule is by passed. In such cases whenever the user opens the Application next time, any Day, the overdue mail should go and must get prepared itself for the next mail sending schedule on next Monday. For this preparation we will keep a date in the parameter table. Let us start with the sample date Monday, November 19, 2007.


The EMail Sending VBA Code

With the above lines I hope our preparation story is all done and it is time to go for the VBA Code. Copy the Main Program given below and paste it into a Standard Module of your Project and save the Module.

Public Function SendWeeklyMail()
'--------------------------------------------------------
'Author : a.p.r. pillai
'Date   : 01/11/2007
'--------------------------------------------------------
Dim db As Database, rst As Recordset
Dim m_Addto As String, m_Cc As String, j As Integer
Dim msgBodyText As String, m_Subject As String
Dim m_MailDate, m_ComputerName As String, chk_CName As String

On Error GoTo SendWeeklyMail_Err

'Field Name and Table Name are same
m_MailDate = DLookup("MailDate", "MailDate")
m_ComputerName = DLookup("ComputerName", "MailDate")
chk_CName = Environ("COMPUTERNAME")

'identify the Mail Client Computer
If chk_CName  = m_ComputerName Then
   Exit Function
End If

'Verify Mail Schedule
If m_MailDate + 7 > Date Then
    Exit Function ' mail not due
End If

m_Addto = ""
m_Cc = ""
j = 0

'Create Address List
Set db = CurrentDb
Set rst = db.OpenRecordset("Add_Book", dbOpenDynaset)
Do While Not (rst.EOF)
    If rst!TO Then
        If Len(m_Addto) = 0 Then
            m_Addto = rst![MailID]
        Else
            m_Addto = m_Addto & ", " & rst![MailID]
        End If

    ElseIf rst![cc] Then

        If Len(m_Cc) = 0 Then 
           m_Cc = rst![MailID]
        Else
            m_Cc = m_Cc & ", " & rst![MailID]
        End If

    End If
rst.MoveNext
Loop
rst.Close

m_Subject = "BANK GUARANTEE RENEWAL REMINDER"

msgBodyText = "Bank Guarantee Renewals Due weekly Status Report as on " _& Format(Date, "dddd, mmm dd,yyyy") & " which expires within next 15 days Cases is attached for review and " & "necessary action. "
msgBodyText = msgBodyText & vbCr & vbCr & "Machine generated email, " & " please do not send reply to this email." & vbCr

'replace x with your Lotus Notes password retaining the ~ character at end. 
SendKeys "xxxxxxx~", False

DoCmd.SendObject acReport, "BG_Status", "SnapshotFormat(*.snp)", m_Addto, m_Cc, "", m_Subject, msgBodyText, False, ""

'Update MailDate to current date (if Monday) or date of
'previous Monday, if the mail was send after the due date.

Set rst = db.OpenRecordset("MailDate", dbOpenDynaset)
rst.Edit
Do While rst![MailDate] + 7 <= Date
  rst![MailDate] = rst![MailDate] + 7
Loop
rst.Update
rst.Close

SendWeeklyMail_Exit:
Exit Function

SendWeeklyMail_Err:
MsgBox Err.Description, , "SendWeeklyMail()"
Resume SendWeekly
Mail_Exit

End Function

In the above Code you must make a change in the SendKeys "xxxxxxx~", False command parameter given in quotes. You must replace the x characters with your Lotus Notes Password and retain the tilde (~) character, which is equal to the Enter Key stroke.

This will send the Lotus Notes password in advance and expected to meet the Password Prompt of Lotus Notes in time, in that case the password Prompt will not pop up. Since, the mail is routed through different channels this is a blind-folded shot and can miss the target, in that case Lotus Notes will demand for the password and you have no other choice but to key in manually and press Enter.

For Internet Users this can be disabled with a single quote at the left side of the command, if the SMTP in Outlook is already configured with the Email password.

We will put few lines of code on the Control Screen Module to give a few seconds delay immediately after opening the Application to get set for calling the SendWeeklyMail() Program.

' put the next line at the header section of the Module
Dim i as integer 

Private Sub Form_Load()
   i = 0
   Me.TimerInterval = 1000
End Sub

Private Sub Form_Timer()
i=i+1
If i = 16 then ' 15 seconds delay
   Me.Timerinterval = 0
    SendWeeklyMail
End if
End Sub

Copy and paste the above lines in the Control Screen Module save and close the Form. Try out the Automated pre-scheduled EMail Procedure.

Share:

Configure Outlook for Lotus Notes


Continued from earlier Post: MS-Access and Emails

We have seen that sending EMails from MS-Access is easy enough like any other method you are familiar with. With few clicks we can send an email with MS-Access Objects as attachment to the mail. Depending on the object selected for attachment the output format options available may be different. The attachment format for VB Code module is Text format only and will be converted into this format before attaching it to the mail.

As I have pointed out in the earlier post that we can send Mail Alerts automatically on important matters, within the Local Area Network (LAN) through Email Transport Services, like Lotus Notes, from Access Application.

But before we look into the mail sending procedure and Programs, we must configure MS-Outlook on one of the PCs for Lotus Notes and to use its Contact List.

Access Mail Client

Your Application may be shared by several Users on different PCs but the mails should go from only Machine, let us call it Access Mail Client. We need to configure the Access Mail Client Machine for Lotus Notes. Lotus Notes has its own Mail Server and Client installations on each PC. The Access Mail Client machine also must be installed with Lotus Notes Client and you must be using it for sending emails directly from Lotus Notes. You will have a Unique Lotus Notes ID File on your PC besides your own contact Lotus Notes Mail ID.

NB: If you don't want a personal User Id to appear as the Sender of the Mail Alerts from your Access Application then a special Lotus Notes Mail ID must be created and Lotus Notes Client must be installed with this ID on the Access Mail Client machine. You may seek the help of Lotus Notes Administrator for this purpose.

Configuring MS-Outlook for Lotus Notes

We will look into the MS-Outlook configuration steps now with few images and what it does.

  1. Your Lotus Notes ID File reference must be inserted into a control within Lotus Notes itself to be visible to the external Programs like MS-Outlook to use the facilities provided by Lotus Notes. To do this, open Lotus Notes and Log in with your User Id and password. Select Edit Current Location . . . from Mobile group menu of File Menu. The Location: Office (Network) control will be displayed.

  2. Click on the Advanced Tab and select Basics from the displayed 2nd set of Tabs. See Image below.

  3. Click on the Text control to the right of the label User ID to switch to: and click on the Search Light icon underneath the User ID label to browse to the location of your User ID file. The Path will be X:\Lotus\Notes\Data\filename.id, where X stands for the Lotus Notes installed Drive and the file name may have some similarity to your name depending on what name your Administrator has given with the file extension .id. Find this file and insert it into the Text control. Your Lotus Notes email Id, password are stored in this file. MS-Outlook needs this information to access the Contact List as well as to send and download the emails from Lotus Notes.
  4. Close the Control after inserting the Location reference and minimize Lotus Notes.
  5. Open Control Panel from Start Menu - - > Settings . . . and double click on the Mail Icon to display the Control with Services, Delivery & Addressing Tabs. See the image below. If your MS-Outlook installed version is not Corporate or Workgroup then the Options may look different. The images are from MS-Office 2000 installation.

  6. Click on the Services Tab. The Profiles window will be empty if you have not configured for any other services earlier.
  7. Click on the Add. . . Button to open up the Add Services to Profile list with several options. Select Lotus Notes Mail from the list and click to add it to the Profile window.
  8. Click on the Delivery Tab and select Lotus Notes Message Store in the control below. At this point you may be prompted for Lotus Notes password to complete this option. The information Service Lotus Notes Transport will appear in the next window.

  9. Select Addressing Tab on the Control. You may Select the Lotus Notes Main Address Book or your personal Lotus Notes Address Book with selected Addresses from the Main one and insert it in the appropriate controls. See the image below:

  10. Click Apply to update the settings. Click OK to close the window. You can configure the same settings from Options. . . of Tools Menu of MS-Outlook too.
  11. We have to define one more setting as how to store the Contact's Name in the Address Book, which we will do it from Outlook.
  12. Open Microsoft Outlook. Select Preferences from Options. . . of Tools menu.
  13. Click on the Contact Options. . . button.
  14. Select the Options in both controls as shown below and click OK.

    The first control settings says that while sending mails insert the Names in this order but while storing the values in the Address Book use the format in the second control.

    When you look for the Email addresses manually in the Address Book it will be displayed in the second format and you can search for the names by typing the first few characters of the first name in the lookup control.

    But when we attempt to send mails from MS-Access we must present the email ID to Lotus Notes in the first format.

    Image of the Lotus Notes Mail ID look up control is given below for reference. I have edited the image to insert those names more familiar to you. Check the name displayed at the left window and how the name format changed when it is inserted in the To line.

  15. You may now try to send an e-mail through Lotus Notes Mail manually from MS-Access, as we did in earlier session, using some of your friends Lotus Notes ID. During the mail transmitting point Lotus Notes will prompt for password.

Next we will see how to send the Mail Alerts automatically from Microsoft Access through Lotus Notes at scheduled intervals.

Share:

MS-Access and E-Mail


Introduction

It is unlikely that you will use Microsoft Access to send your personal e-mails through Internet, when so many other options are available to you.

But, when you need to share information from your Access Application with remotely located users regularly and you are sending printed Reports through conventional mail services then you can seriously think about the e-mail option.

If you are connected to a Local Area Network (LAN) you can distribute Reports to the recipients through Intranet Mail Service Applications like Lotus Notes.

For both Internet and Intranet mails to work Microsoft Outlook must be configured to act as a communication link between Microsoft Access and the Mail Transport; the SMTP Server of your Internet Service Provider(ISP) or the Intranet Mail Transport, like Lotus Notes. If you are already using Microsoft Outlook for Sending and Receiving e-mails then it is already configured. MS-Outlook will maintain the Contact List from which we can select the prospective mail recipients.

NB: The procedure for configuring Microsoft Outlook is beyond the scope of this Article. You may refer the Help topics of Microsoft Outlook or Help documents provided by your ISP for guidance. But, we will go through the procedure while configuring MS-Outlook for Lotus Notes for Intranet Mails in the forthcoming Posts.


Outlook Versions

There are two versions of Microsoft Outlook installations. The normal one that we see on our PCs, for Internet Mails and the Intranet type for Corporate or Workgroups. You can check your installation version by displaying the About Microsoft Outlook Screen from the Help Menu. If it is a Corporate or Workgroup installation this exact label will appear on the top area of the About Screen.

Let us explore few scenarios that warrants for sending e-mails from Microsoft Access.

Your Company is a Service oriented Organization, like a Bank, and you want to send Birthday Greetings to your Customers automatically or need to inform them the revision of Interest Rates on Fixed Deposits.

OR

Your Company enters into Agency Agreements with other Parties, which is renewable periodically and your Access Application should monitor and send Mail Alerts to the respective Departments well in advance so that they can act in time for renewal of Agency Agreements.

OR

Your company extends Credits to the Customers based on renewable Bank Guarantees and your Access Application should send mail alerts to the parties concerned for taking it up with the Customers well before renewals are due.

In all the above or similar scenarios we can automate the Mail Alert procedure with the help of programs and we will look into a sample procedure for sending e-mails automatically through Lotus Notes, at the end of this series.


Using Lotus Notes for eMail

In the meantime let us look into a simple mail sending example that sends a Report as attachment, in Microsoft Access Snapshot Format, manually.

  1. Open your Database and Click on a Report (avoid the Dynamic Report that we have created in the earlier session).
  2. Point your mouse on the Send To.. option in File Menu and Click on Mail Recipient (as Attachment).
  3. Select Snapshot Format from the displayed file formats menu.
  4. A dialogue control will open up asking to select the Mail Transport Profile already configured in Microsoft Outlook, or you can create a new profile.
  5. Select the default profile that you are using to send your e-mails. If you select the options and set this as the default Profile then this dialog control will not appear next time.
  6. When you click OK the e-mail Editor Screen will appear and you can modify your mail before sending it.

    See the image below.

  7. Click on the To . . . Button to pick e-mail addresses from the Contacts List of Microsoft Outlook. The Cc: Button also works the same way.
  8. You can see the Report that we have selected is already converted into Microsoft Access Snapshot Format and attached to the Mail. Instead, if you prefer to send an external Object like Word or Excel File you can Click on the Attach. . . Button and select the file from your Hard Disk.
  9. The right side blue colored panel will not appear till you click on the Attachment Option . . . Button that you see next to the blue colored panel.

    Two options are provided and the default one is the Regular Attachment option, which sends individual copies of the attachment to each recipient. The second option is for Intranet Mails that will save the attachment in a common location where everybody can share the same file. You can ignore this Option.

  10. You can compose your mail in Plain Text and click the Send Button on the Toolbar above. Microsoft Outlook will take care of the rest.

Sample Mail Image

A sample image of the Sent Items display of Microsoft Outlook 2003 is given below for reference.

If you are sending a regular Report as attachment with fixed wordings of mail body text then you can automate this action with a Macro. See the SendObject Command and its settings in the Macro image below:


The SendObject Command

The SendObject Command is selected in the Action Grid. The E-Mail Field values are filled-in in the Property Sheet of the SendObject command below. If you don't want to send an Access Object as attachment then you can leave the first three property values empty. If you set the Edit Message property to Yes then the E-Mail Client will open for editing as we did earlier, otherwise the mail will be passed on to Microsoft Outlook, without appearing on the screen, for sending through Internet.

You can attach this Macro to a Command Button or run the macro from VB Code using Docmd.Runmacro "MacroName". If you want to control the frequency of the e-mails with the Report attachment then you must set up procedure to cross check the scheduled Date or Day before running the macro. We will look into such a program that sends mail automatically when it hits the weekly schedule, at the end of this series.

Next, we will explore the procedure that involves configuring Lotus Notes to use as Mail Transport to send e-mails within Local Area Network (LAN).

Share:

Dynamic Report

Introduction

Report designing in MS-Access is very easy as we already know. Once we setup a procedure to run the macros/programs to prepare the data for a standard Report, all we need to do is to design the Report and add it to the Report List. Every time the User runs the report it is ready to Preview or Print. The Source data may change, based on the report period setting, but there will not be any change in the structure of the Report or source data or need any change on the Report design itself as all the standard labels, like main heading, Report generated for which period, prepared date, Page Numbers etc. are already taken care of as part of the initial report design task.

We are going to discuss a Report that is not so easy to plan on the above rules or based on a fixed source data structure. The structure of the source data is not predictable or shall we say field names of the source data can change every time we run this Report. If the data structure changes every time then you cannot put the field names permanently on the report design as we normally do. Adding to the difficulty the number of fields for the report also may change. Even more difficult situation is to create Report Summary figures at Group-level and Report Footer level Grand-totals.

When someone asks for something difficult we can easily say it is not possible and the user may swallow it too, provided if he/she doesn't know computer programming at all. In the Software world when someone says no to something, it simply means that he doesn't know how to do it, at least for the time being. I fall in this category too. But, then after saying that it keeps me disturbed and starts asking myself, can I write it off just like that or find a way somehow to do it. It may take a while to have a plan, get things organized, to try something on those line of ideas and finally to come up with something that works. Doesn't matter how many steps you have taken to achieve that. The final result is that matters. You can always go back and review the steps and refine them. But, there are times that we have to stick to the No answer too.

Let us get into the problem solving instead of beating around the bush. I was trying to be little philosophical. It is not that big as you are imagining by now, after listening to my bragging.


Report Preparation

Before getting into the Report preparation part, first I will show you some sample lines of data and the Report Format that we need to deal with. The sample data is taken from two familiar Tables from the Northwind.mdb sample database; Employees and Orders, which we have already used in earlier examples.

In the above table, the Shipped Date range is from July 10, 1996 to May 6, 1998. Our task is to prepare a Report from the following Employee-wise, Year-wise, Month-wise formatted data shown below:

sample data table view

When a sample Report is designed using the above data as source, it will look like the image given below.

The Report contents should be for a period of 12 months and data selection criterion is set for the period Between 199607 (July 1996) and 199706 (June 1997) in the data selection Query of the Report. In the Details Section, the report fields are inserted. Report Footer controls are set with the Summary formula taking month-wise Totals. Field Heading Controls are defined with month-Year labels. So far so good, we can open the report in Preview or Print, no issues.


Report Data Selection Criteria

But, when the data selection criteria changes for a different period the Report will not run. It will show error on the first field that do not match with the fields already placed on the Report Text Boxes and refuses to open to Preview or Print.

An alternative method is to create a Report Table with Field Names like M1, M2 to M12 for holding January to December Data and use an Append Query to add the report data into this table and design Report using this permanent field Names. We must find a way to define the field header labels with some formula taking the values from the report period parameter. The user can create report data for full 12 months or lesser period for a particular year. Then we are putting conditions on the user asking him to select data for a particular year only, not to cross from one year to the other when entering criteria.

If he/she goes for a trial run with cross over period then the data for earlier period comes at the right end of the Report and later period at the beginning. The field headings are another issue to take care. Finally we cannot call it a good report or let us put it this way, is not a user-friendly report.


Sample Data Preparation Queries

We found a remedy for this issue by introducing a small program on the Report VB Module to take care of the final stage designing task based on the source data structure, every time we open it to preview or print.

  1. To get prepared for the Program, Import the Tables Employees and Orders from the Northwind.mdb sample database, if you don't have them. If you don't know the location of the sample database visit the Page Saving Data on Forms not in table for location references.
  2. Copy and paste the following SQL String into new Query's SQL Window and save it with the Names as indicated below:
    Query Name: FreightValueQ0
    
    SELECT [FirstName] & " " & [LastName] AS EmpName,
     Val(Format([ShippedDate],"yyyymm")) AS yyyymm,
     Orders.Freight
    FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
    WHERE (((Val(Format([ShippedDate],"yyyymm"))) Between 199607 And 199706));
    
    Query Name : FreightV_CrossQ
    

    TRANSFORM Sum(FreightValueQ0.Freight) AS SumOfFreight SELECT FreightValueQ0.EmpName FROM FreightValueQ0 WHERE (((FreightValueQ0.yyyymm)<>"")) GROUP BY FreightValueQ0.EmpName PIVOT FreightValueQ0.yyyymm;


    Report Design Task

  3. After creating the above Queries one after the other design a Report as shown below using FreightV_CrossQ Query as Data Source.

  4. In the Detail Section of the Report create 13 Text Box Controls; make the leftmost one slightly bigger for the Employee's Name and others for month-1 to month-12. Change the Name Property of the Text Boxes as M00, M01 to M12 from left to right (write two digit numbers in the Name Property as 01, 02 etc. along with prefix M). Leave the Control Source property empty.
  5. In the Report Footer Section create 13 Text Box controls and Name them from T00, T01 to T12. Leave the Control Source Property empty.
  6. In Page Header Section create 13 Label Controls and name them as L00, L01 to L12 and leave the Caption Property empty.
  7. Create a Label at the Top and set the Caption property value to EMPLOYEE-WISE FREIGHT VALUE LISTING.
  8. While the Report is still in design view select Save from File menu and save the Report with the name FreightVal_Rpt.
  9. Select Code from View menu to display the VB Module of the Report.
  10. Copy and Paste the following Code into the VB Module:


    Automating Report Control's Data Reference Setting

    Private Sub Report_Open(Cancel As Integer)
    Dim db As Database, Qrydef As QueryDef, fldcount As Integer
    Dim rpt As Report, j As Integer, k As Integer
    Dim fldname As String, ctrl As Control, dtsrl As Date
    Dim strlbl As String, fsort() As String
    
    On Error GoTo Report_Open_Err
    
    Set db = CurrentDb
    Set Qrydef = db.QueryDefs("FreightV_CrossQ")
    fldcount = Qrydef.Fields.Count - 1
    
    If fldcount > 12 Then
       MsgBox "Report Period exceeding 12 months will not appear on the Report."
       fldcount = 12
    End If
    
    Set rpt = Me
    
    ReDim fsort(0 To fldcount) As String
    For j = 0 To fldcount
        fldname = Qrydef.Fields(j).Name
        fsort(j) = fldname
    Next
    
    'Sort Field names in Ascending Order
    For j = 1 To fldcount - 1
        For k = j + 1 To fldcount
            If fsort(k) < fsort(j) Then
                fsort(j) = fsort(k)
            End If
        Next
    Next
    
    For j = 0 To fldcount
    'Monthwise Data    
    Set ctrl = rpt.Controls("M" & Format(j, "00"))
        ctrl.ControlSource = fsort(j)
        Set ctrl = rpt.Controls("T" & Format(j, "00"))
        If j = 0 Then
            ctrl.ControlSource = "=" & Chr$(34) & " TOTAL = " & Chr$(34)
        Else
            ctrl.ControlSource = "=SUM([" & fsort(j) & "])"
        End If
    
    'Header labels
    If j = 0 Then
        Me("L" & Format(j, "00")).Caption = "Employee Name"
    Else
        dtsrl = DateSerial(Mid(fsort(j), 1, 4), Right(fsort(j), 2), 1)
        strlbl = Format(dtsrl, "mmm-yy")
        Me("L" & Format(j, "00")).Caption = strlbl
    End If
    Next
    
    Report_Open_Exit:
    Exit Sub
    
    Report_Open_Err:
    MsgBox Err.Description, , "Report_0pen()"
    Resume Report_Open_Exit
    End Sub

  11. After copying the code minimize the VB Window, display the Property Sheet of the Report and check whether the On Open property is marked with the [Event Procedure] Value. If it is not, then the code we have pasted is not correctly loaded into this event. Select [Event Procedure] from the drop down list and open the Code module, cut the Code (except the top and bottom lines that we have pasted from the Web page) and paste the Code within the newly opened Empty Report_Open() . . . End Sub lines, remove the orphaned lines, and save the Report.

    Print Previewing the Report.

  12. Open the Report in Print Preview. Your Report must be loaded with the Values from the Source Query with correct heading Labels and the Report Footer Summary.
  13. Open the First Query in design view and change the Criteria values in different ranges, taking care that the Range of values are not exceeding 12 months (it can be less than 12), and try out the Report.

If the selected period exceeds 12 months the Report will open with the data that fits into the maximum number of fields, after showing a message indicating that the selected period exceeds 12 months.

If the selected period is less than 12 months then the rightmost controls will be empty. In either case the Report will open and we can view the contents.


Download Demo Database



Download Demo Database


Share:

MS-Access and Mail Merge-3

Continuation of MS-Access and Mail Merge-2

Continued from previous Post MS-Access and Mail Merge-2.


MAIL MERGE IN MS-ACCESS

We have already designed and tried the Form Letter preparation procedure through the earlier article. We need all those Objects for the Mail Merge in Access and here we have less work to do. I hope you understood the intricacies of the procedure explained there and how all those objects and methods works together to prepare the Form Letter. Since, we are through with the major designing tasks already; we can concentrate on the implementation of Mail Merge method with some minor changes in two or three places.

I will give few examples below as how to insert Field Values from the Report source Query/Table and how to use Built-in Functions into the letter body text on the editing Form. The user needs only few simple rules to keep in mind to use the Mail Merge method.

I will insert the Main Program Codes at the end of this Article. You may Copy and Paste them in appropriate locations in your Project as suggested there.

Usage of Field Values to merge with the Text:

  • Data Field Names must be enclosed in square brackets [ ] and there should not be any typographical errors in Field Names.
  • Use of at least one space between field names is recommended, if they are placed next to each other, but can be used without space also.
  • Field Names must be picked from the Report Source Data Table/Query only and can be inserted anywhere in the text.
  • Data field values from other Table/Query can be inserted with the help of built-in Functions (like DLOOKUP()), see usage of built-in functions given below.
  • If Field Names are joined with & + ' * / (for character or numerical values) it will not be treated as an expression, instead the symbol will appear between the field values. (See Usage of built-in functions for expressions involving Field Values).

Usage Example -1: A Statement of Year-wise special Sales Incentive, credited to [TitleofCourtesy] [Firstname] [LastName]'s personal A/c., is given below for information. His request for transfer to his home town [City]&[Zip] has been approved.

Output : A Statement of Year-wise special Sales Incentive, credited to Dr. Andrew Fuller's personal A/c., is given below for information. His request for transfer to his home town Tacoma&9801 has been approved.

NB: The inserted text will not appear in bold as shown above, it is used here for highlighting the results only.

Usage of Built-in Functions to Merge the result with the Letter text:

  • Built-in Functions must be enclosed in { } brackets.
  • Nested Functions should have only the outermost pair of { } brackets, like in {Format(Date(),"mmm-yy")}
  • Data Field Names enclosed in [ ] can be inserted as parameter for Built-in Functions, like DLOOKUP().
  • Expressions involving Data Field Names enclosed in [ ] can be joined with + - * / symbols in numerical expressions, & in text data expressions and must be inserted as parameters to the built-in Functions.

Example: To find the 90 days credit period expiry date: {Format([InvoiceDate]+90,"dd/mm/yyyy")}

Usage Example-2: A statement of Year-wise special Sales incentive, of [TitleofCourtesy] [FirstName] [LastName], for a sum of ${DLOOKUP("TotalAmt","Yearwise_Incentive","EmployeeID = " & [EmployeeID])} is credited into his personal A/c. during {Format(DateAdd("m",-1,Date()),"mmm-yyyy")}, is given below for information. His request for transfer to his home town [City], has been approved effective {Date()+15}.

Output: A statement of Year-wise special Sales incentive of Dr. Andrew Fuller, for a sum of $8696.41 is credited into his personal A/c. during Sep-2007, is given below for information. His request for transfer to his home town Tacoma, has been approved effective 30/10/2007.

Don'ts:

[ ] and { } brackets should not be used within the letter body text anywhere other than enclosing Field Names and Built-in Functions respectively.

NB: A simple validation check is performed on the input text for matching pairs of [ ] and { } and if you leave out one closing ] in one Field and one opening [ in a different Field the validation check will not detect it and you will end up with Errors. In that case find out the errors, correct them and re-run.

Always print a trial run page of the letter and check the output thoroughly for accuracy before the final print.

We need to make few changes to the Objects as a final step, the Cross-tab Query, the Letter editing Screen and on the Report Design.

  1. Copy and paste the SQL String given below into the SQL window of Yearwise_FreightQ1 Query that we have created earlier overwriting the old SQL String and save the Query. This change is to create a new column for Total Amount of 1996, 1997 & 1998 figures.

    TRANSFORM Sum(Yearwise_FreightQ0.Sales) AS SumOfSales
    SELECT Yearwise_FreightQ0.EmployeeID,
     Sum(Yearwise_FreightQ0.Sales) AS TotalAmt
    FROM Yearwise_FreightQ0
    GROUP BY Yearwise_FreightQ0.EmployeeID
    PIVOT Yearwise_FreightQ0.Year;
  2. After saving the Yearwise_FreightQ1 run the Make-table Query: Yearwise_IncentiveQ by double-clicking on it to create the output table Yearwise_incentive with the new column TotalAmt, which we have used within the DLOOKUP() Function in the usage Example-2 above.
  3. Open the letter editing Form (Letter) in design view, create a Combo Box to display the Report Source Data Field Names, which the Users can reference and type them correctly into the Body Text. See the image below:



    Turn off the Control Wizards (the Magic Want Symbol) on the Toolbox, if it is on, before clicking on the combo box control on the toolbox. Draw a Combo Box on the Form anywhere convenient on the design surface (I have placed at the left side of the body text area). Display the Property Sheet of the Combo Box and change the following property values:

    • Name = cboFields
    • Row Source Type = Field List
    • Row Source = LetterQ
    • Column Count = 1
    • Column Width = 1"
    • List Rows = 8
    • List Width = 1"
  4. Copy and Paste the following revised Code into the VB Module of the above Form (Letter) for the cmdPreview Button:

    Private Sub cmdPreview_Click()
    On Error Resume Next
    Me.Refresh
    DoCmd.OpenReport "Letter", acViewPreview
    
    If Err > 0 Then
      MsgBox "Errors in Report source Data."
      Err.clear
    End If
    
    End Sub
  5. Save the Form with the above change.
  6. Open the Report named Letter in design view. Click on Para1 text box control and remove the field name Para1 from the Control Source property. Change the Name property to Para1x.
  7. Remove the Field Name Para2 from the second Text Box's Control Source property and change its Name Property to Para2x.

    Both Controls now show as Unbound Text Boxes.

  8. Copy and paste the following VB Code into the VB Module of the above Report:

    Private Sub Report_Open(Cancel As Integer)
    Dim xPara1, xPara2, ErrFlag1 As Boolean
    Dim ErrFlag2 As Boolean, x
    
    On Error Resume Next
    
    xPara1 = DLookup("Para1", "LetterQ")
    xPara2 = DLookup("Para2", "LetterQ")
    
    'submit para1 for parsing
    ErrFlag1 = False
    x = MailMerge(xPara1)
    Me![Para1x].ControlSource = x
    
    If Err > 0 Then
        ErrFlag1 = True
        Err.Clear
    End If
    
    'submit para2 for parsing
    ErrFlag2 = False
    x = MailMerge(xPara2)
    Me![Para2x].ControlSource = x
    
    If Err > 0 Then
        ErrFlag2 = True
        Err.Clear
    End If
    
    If ErrFlag1 Or ErrFlag2 Then
       MsgOK "Errors Found, Correct them and re-try."
    End If
    
    End Sub

  9. Save the Report after the changes.
  10. Open a new Global VB Module in your Project. Copy and paste the following Main Programs and save the Module. The lines of code above the Function MailMerge() are Global Declarations and must appear at the top-most area of the Module.

Type ParaTxt
    text As Variant
    status As Boolean
End Type

Type SpecRec
    LsStart As Integer
    Lsend As Integer
    LfStart As Integer
    Lfend As Integer
    Str As String
    fun As String
End Type

Dim V As ParaTxt, DatF() As SpecRec, DatF2() As SpecRec

Public Function MailMerge(ByVal inpara) As String  
'------------------------------------------------------  
'Author : a.p.r. pillai  
'Date   : 01-10-2007  
'Remarks: Scan and Parse Text  
'------------------------------------------------------
Dim i As Integer, k As Long, L As Long
Dim i2 As Integer, xpara, ypara, yxpara
Dim j As Integer, xchar As String
Dim qot As String, size As Long
Dim curlbon As Boolean

On Error GoTo MailMerge_Err

yxpara = inpara

V.text = inpara
V.status = True

qot = Chr$(34)

strValidate 'run validation check

If V.status Then
  MailMerge = yxpara
  Exit Function
End If

'scan for Merged Fields
'ignore if embedded within built-in Function

xpara = V.text

i = 0
For j = 1 To Len(xpara)
  xchar = Mid(xpara, j, 1)
  If xchar = "{" Then
     curlbon = True
  End If
  If xchar = "[" And curlbon = False Then
     i = i + 1
  ElseIf xchar = "}" And curlbon = True Then
      curlbon = False
  End If
Next

If i > 0 Then
  i = i + 1
  ReDim DatF2(1 To i)
Else
  GoTo chkFunction
End If

'Parse embedded fields
L = 1: curlbon = False
For j = 1 To Len(xpara)
  If j = 1 Then
    DatF2(L).LsStart = 1
  End If

  xchar = Mid(xpara, j, 1)
  If xchar = "{" Then
     curlbon = True
  End If
  If xchar = "[" And curlbon = False Then
    DatF2(L).Lsend = j - 1
       size = DatF2(L).Lsend - DatF2(L).LsStart + 1
       DatF2(L).Str = Mid(xpara, DatF2(L).LsStart, size)
       DatF2(L).LfStart = j
  End If
  If xchar = "]" And curlbon = False Then
       DatF2(L).Lfend = j
       size = DatF2(L).Lfend - DatF2(L).LfStart + 1
       DatF2(L).fun = Mid(xpara, DatF2(L).LfStart, size)
       L = L + 1
       DatF2(L).LsStart = j + 1
  End If
  If xchar = "}" And curlbon = True Then
      curlbon = False
  End If

Next
DatF2(L).Str = Mid(xpara, DatF2(L).LsStart)
DatF2(L).fun = ""

'create output from parsed string
ypara = ""
For j = 1 To L - 1
  If j = 1 Then
    ypara = DatF2(j).Str & qot & " & " & DatF2(j).fun
  Else
    ypara = ypara & " & " & qot & DatF2(j).Str & qot & " & " & DatF2(j).fun
  End If
Next

ypara = ypara & " & " & qot & DatF2(j).Str
If Len(DatF2(j).fun) > 0 Then
   ypara = ypara & qot & " & " & DatF2(j).fun
End If

xpara = ypara

chkFunction:

'scan for embedded built-in functions
i2 = 0
For j = 1 To Len(xpara)
  If Mid(xpara, j, 1) = "{" Then
    i2 = i2 + 1
  End If
Next

If i2 > 0 Then
  i2 = i2 + 1
  ReDim DatF(1 To i2)
Else
  GoTo Finish
End If

'parse built-in functions
L = 1
For j = 1 To Len(xpara)
  If j = 1 Then
    DatF(L).LsStart = 1
  End If
  If Mid(xpara, j, 1) = "{" Then
    DatF(L).Lsend = j - 1
       size = DatF(L).Lsend - DatF(L).LsStart + 1
       DatF(L).Str = Mid(xpara, DatF(L).LsStart, size)
    DatF(L).LfStart = j + 1
  End If
  If Mid(xpara, j, 1) = "}" Then
    DatF(L).Lfend = j - 1
       size = DatF(L).Lfend - DatF(L).LfStart + 1
       DatF(L).fun = Mid(xpara, DatF(L).LfStart, size)
    L = L + 1
    DatF(L).LsStart = j + 1
  End If
Next
DatF(L).Str = Mid(xpara, DatF(L).LsStart)
DatF(L).fun = ""

'format the paragraph
ypara = ""
For j = 1 To L - 1
  If j = 1 Then
    ypara = DatF(j).Str & qot & " & " & DatF(j).fun
  Else
    ypara = ypara & " & " & qot & DatF(j).Str & qot & " & " & DatF(j).fun
  End If
Next

ypara = ypara & " & " & qot & DatF(j).StrIf
 Len(DatF(j).fun) > 0 Then
   ypara = ypara & qot & " & " & DatF(j).fun
End If

Finish:

'if there is no value for merging then
If i2 = 0 And i = 0 Then
  ypara = yxpara
End If

xpara = "=" & qot & ypara & qot

MailMerge = xpara

MailMerge_Exit:
Exit Function

MailMerge_Err:
MsgBox Err.Description, , "MailMerge()"
MailMerge = ""
Resume MailMerge_Exit
End Function

Public Function strValidate()  
'------------------------------------------------------  
'Author : a.p.r. pillai  
'Date   : 01-10-2007  
'Remarks: Pre-parsing validation check  
'Returned Valule = False, if no errors in Expressions  
'------------------------------------------------------   
Dim xpara, j As Long, xchar As String   
Dim msg As String, flag As  Boolean   
Dim SBopen As Integer, SBCIose As Integer   
Dim CBopen As Integer, CBclose As Integer   
Dim str1 As String, str2 As String

   On Error GoTo strValidate_Err

    xpara = V.text
    xpara = Trim(xpara)

    SBopen = 0: SBCIose = 0
    CBopen = 0: CBclose = 0
    str1 = "missing for built-in Function(s)."
    str2 = "missing for Fieldname(s)."

    For j = 1 To Len(xpara)
        xchar = Mid(xpara, j, 1)
       Select Case xchar
            Case "["
                SBopen = SBopen + 1
            Case "]"
               SBCIose = SBCIose + 1
            Case "{"
                CBopen = CBopen + 1
            Case "}"
                CBclose = CBclose + 1
        End Select
    Next
    msg = ""
    If SBopen = SBCIose Then
       GoTo nextstep
   Else
       If SBopen > SBCIose Then
         msg = "1. Closing ] " & str2
         flag = True
       Else
         msg = "1. Opening [ " & str2
         flag = True
      End If
   End If
nextstep:
    If CBopen = CBclose Then
       GoTo FinalStep
    Else
       If CBopen > CBclose Then
         If flag Then
          msg = msg & vbCr & "2. Closing } " & str1
         Else
           msg = "1. Closing } " & str1
         flag = True
         End If
       Else
        If flag Then
            msg = msg & vbCr & "2. Opening { " & str1
        Else
            msg = "1. Opening { " & str1
           flag = True
         End If
       End If
   End If

FinalStep:
   If flag Then
      msg = "Errors found in field/function definitions." & vbCr & vbCr & msg & vbCr & vbCr & "Program Aborted. " & vbCr & "Correct the errors and re-try."
      MsgBox msg
      V.status = True
    Exit Function
   End If

  V.status = False

strValidate_Exit:
Exit Function

strValidate_Err:
MsgBox Err.Description, , "strValidateQ"
strValidate = True
Resume strValidate_Exit
End Function

In Case of Errors

If you end up with errors when you Compile/Run the Programs for the first time try linking the essential Library Files to your Project and try again. Visit the Page Command Button Animation for a list of Library Files and for help on linking those files to your Project.

The Validation Program performs a simple validation check on the input data and gives out warnings if something found not in order.

Open the Text editing screen: Letter and try out examples explained above by inserting Field Names, Built-in Functions and Functions that uses Field Values as parameters and click the Preview Command Button to open the Report with merged values and check whether the output is coming correctly as expected.

Even though the program is very simple in its implementation it is very effective within controlled environment, User friendly and will be a powerful Tool in your Projects.

Any suggestions for improvement of the program are welcome.

Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

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 Class Module 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 Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type 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 and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts