Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Progress Meter

Introduction.

When we process a large volume of data for MS-Access Reports it may take a few minutes to several minutes to complete, depending on the transaction volume involved. The transactions are organized and processed through Select Queries, and Action Queries, sequencing the process through Macros and VBA routines to arrive at the final Report Data.  If you have Action Queries that pull data from Select, Crosstab Queries, and if a large volume of transactions is involved, it may take longer than the Normal Queries to filter for output.

In all these situations it will be difficult for the user to know how long the whole process will take to complete the task. After running the process a few times the user may get a rough idea as to how long it will take to finish. 

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 can be turned it off at the end of the process. But, this method will not give an exact indication as to when the process will be over.  

If it takes more than the usual running time, depending on other factors, like an increase in the volume of transactions or due to busy network traffic and so on, it is difficult to determine whether the process is really running or it is a machine hang up issue.

The Quick Solution

When we run several Action Queries in a 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 the Tools menu, and select View Tab on the displayed Dialog Control. Put a check-mark in the Status Bar option, under Show Options 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 at the Progress Meter.

  1. Displaying the Progress Meter on the Status Bar
  2. Displaying the Progress Meter on a Form
  3. Usage of a transaction countdown 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 the extended price 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 the third parameter to the function to initialize the Progress Meter. Subsequent calls are made with the current record number to update the Meter with the current status. MS-Access calculates a percentage of 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 them 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 devise 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 Access2007 Version



Download Access2003 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 them at the end of this post for reference.

If you look carefully at 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 the 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 on the right side of each one. These are all predefined keyboard shortcuts in the system.

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 a & symbol. If you insert a & symbol on 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 does not end up with the same built-in Shortcuts.

 Here, we already have a Built-in Keyboard Short &Edit, which uses the ALT+E combination. We can insert the & symbol on 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 combinations 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

A macro with the name Autoexec runs automatically when you open a database.

General Purpose Keyboard Shortcuts

A list of useful general-purpose Keyboard Shortcuts is 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 the 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 the 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 to spell
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 the 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 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 Equipment and Vehicles. If I am the one who is going to use the System, then somehow I could manage to find the information that I want to work with and nobody will know how much time I spent 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 struggled with 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 Options Group is linked to a Macro. When any of the buttons on the option group is clicked, the Company Names starting with that letter are filtered from the Customer List.

There were no second thoughts on this and I straight away transplanted this method to my first MS-Access Project. It was developed without touching a single line of Access Basic Code, all automated procedures are by 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 the 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 Lists 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 TextBox is empty, it is the 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 a Numeric Value is entered the filter action will be ignored.

    • If clicked when the TextBox is empty, it is the same as clicking the Filter Reset Button.

  3. Usage of << Pattern Match Button.

    • Click the Button with a group of characters that match anywhere within the Product Name.

    • If clicked when the text box is empty, it is the 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.

We already know that 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 to the Workgroup to open a database, a lock file with the ldb extension will open up with the same name as 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 the 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 TextBoxes 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 symbol < 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; whois online.

    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 whois online 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

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

NOTE: The above VBA Code was written for Windows XP and the NET SEND message Command is used for sending messages to a Workstation. Windows 10/08/7 uses the MSG Command. Type C:\MSG /? In the Command Prompt for full Parameter List and Options.

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

  1. Open the Whois online 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, on which other users are currently connected, then give that File's PathName. No need to give File Name extensions mdb, mdw, or ldb, but if you wish to include you may do so for clarity.

  2. Click on the Button with < symbol.

  3. Select one or two Workstation IDs in the List Box.

  4. Type a message in the Text Box Control above the Send Message Button for the selected Users.

  5. 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 Who-is-online.zip


Share:

Automated Email Alerts

Continued from the previous post: Configure Outlook for Lotus Notes.

The next procedure, that we are going to look into, is applicable for both internets 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 the 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 a 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. A 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 by a 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 sent 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 a 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 identifying 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 emails with report attachments go to the correct recipients and prevented others from getting the mail. But, here we will try the simple method.

    Email Scheduling

  2. The 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 emails 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 to. 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 the 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 on 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 long 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 bypassed. 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 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 to 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 Keystroke.

This will send the Lotus Notes password in advance and is 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 shot and can miss the target, in that case, Lotus Notes will demand the password and you have no other choice, but to key in and press Enter.

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

We will put a few lines of code in 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 a few clicks, we can send an email with MS-Access Objects as an attachment to the mail. Depending on the object selected for attachment the output format options available may be different. The attachment format for the 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 the 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 use its Contact List.

Access Mail Client

Your Application may be shared by several users on different PCs, but the mails should go from the 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 the Lotus Notes Administrator for this purpose.

Configuring MS Outlook with Lotus Notes.

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

  1. Your Lotus Notes ID File reference must be inserted into the 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 the 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 the 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 and the 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 the Lotus Notes password to complete this option. The information Service Lotus Notes Transport will appear in the next window.

  9. Select the 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 the Options of Tools Menu of MS-Outlook too.

  11. We have to define one more setting as to how to store the Contact's Name in the Address Book, which we will do from Outlook.

  12. Open Microsoft Outlook. Select Preferences from the 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 say 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 mail from MS-Access we must present the email ID to Lotus Notes in the first format.

    The Image of the Lotus Notes Mail ID lookup control is given below for reference. I have edited the image to insert those names more familiar to you. Check the name displayed in 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 an earlier session, using some of your friends Lotus Notes IDs. During the mail transmitting point, Lotus Notes will prompt for the 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 emails via the Internet,  while 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 to 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 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, is for Internet e-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 in the top area of the About Screen.

Let us explore a few scenarios that warrant 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 of 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 an attachment, in Microsoft Access Snapshot Format, manually.

  1. Open your Database and Click on a Report (avoid the Dynamic Report that we created in the earlier session).

  2. Point your mouse to the Send To option in File Menu and Click on Mail Recipient (as Attachment).

  3. Select Snapshot Format from the displayed options of the file-formats menu.

  4. A dialogue control will open up asking you 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 which 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 Item display of Microsoft Outlook 2003 is given below for reference.

If you are sending a regular Report as an 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 an 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 the 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 a 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 a Mail Transport to send emails within the Local Area Network (LAN).

Share:

Dynamic Report

Introduction

Report designing in MS-Access is very easy as we already know. Once we set up 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 in the Report design itself as all the standard labels, like the 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 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 into this category too. But, then after saying that it keeps me disturbed, and start 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, try something along those lines of ideas, and finally 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 problem-solving instead of beating around the bush. I was trying to be a little philosophical. It is not as big as you are imagining 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, and Month-wise formatted data shown below:

sample data table view

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

The Report contents should be for a period of 12 months and the 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, with no issues.

Report Data Selection Criteria

But, when the data selection criteria change for a different period the Report will not run. It will show an error on the first field that does not match with the fields already placed on the Report TextBoxes 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 a Report using these permanent field Names. We must find a way to define the field header labels with some formula taking the values of the report period parameter. The user can create report data for the full 12 months or a 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 cross-over a period, then the data for the earlier period comes at the right end of the Report and the later period in the beginning. The field headings are another issue to take care of.  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 VBA 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 the 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 is shown below using FreightV_CrossQ Query Data Source.

  4. In the Detail Section of the Report create 13 Text Box Controls; make the leftmost one wider for the Employee's Name, and others month-1 to 12. Change the Name Property of the TextBoxes as M00, M01 to M12 from left to right (write two-digit numbers in the Name Property as 01, 02, etc., along with the prefix M). Leave the Control Source property empty.

  5. In the Report Footer Section, create 13 TextBox controls and Name them from T00, T01 to T12. Leave the Control Source Property empty.

  6. In Header Section creates 13 Label Controls, name them 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 the Save option from the File menu and save the Report with the name FreightVal_Rpt.

  9. Select Code from the View menu to display the VBA Module of the Report.

  10. Copy and Paste the following Code into the VBA 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 VBA 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 the 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 the 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 work together to prepare the Form Letter. Since we are through with the major designing tasks already; we can concentrate on the implementation of the Mail Merge method with some minor changes in two or three places.

I will give a few examples below as how to insert Field Values from the Report source Query/Table and how to use Built-in Functions in the letter body text on the editing Form. The user needs only a 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 into the 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.

  • If field names are placed next to each other, use at least one space between them, 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 another Table/Query can be inserted with the help of built-in Functions (like DLOOKUP()), see the 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 Account, is given below for information. His request for transfer to his hometown [City]&[Zip] has been approved.

Output: A Statement of Year-wise special Sales Incentive, credited to Dr. Andrew Fuller's personal Account is given below for information. His request for transfer, to his hometown, Tacoma9801 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 parameters 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 Account 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 Yearwise special Sales incentive of Dr. Andrew Fuller, for a sum of $8696.41 credits into his personal Account 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 a few changes to the Objects as a final step, the Cross-tab Query, the Letter editing Screen, and the Report Design.

  1. Copy and paste the SQL String given below into the SQL window of the Yearwise_FreightQ1 Query that we created earlier overwriting the old SQL String and saving the Query. This change is to create a new column for the 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;
  • 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.

  • Open the letter editing Form (Letter) in the design view, and create a Combo Box to display the Report Source Data Field Names, which the Users can reference and type correctly in 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 it on 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"

  • 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
  • Save the Form with the above change.

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

  • Remove the Field Name Para2 of the second Text Box's Control Source property and change its Name Property to Para2x.

    Both Controls now show as Unbound Text Boxes.

  • 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
  • Save the Report after the changes.

  • 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 topmost 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 linking those files to your Project.

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

    Open the Text editing screen: Letter and try out the examples explained above by inserting Field Names, Built-in Functions, and Functions that use 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 the controlled environment, User friendly, and will be a Powerful Tool in your Projects.

    Any suggestions for improvement of the program are welcome.

    Downloads



    Share:

    PRESENTATION: ACCESS USER GROUPS (EUROPE)

    Translate

    PageRank

    Post Feed


    Search

    Popular Posts

    Blog Archive

    Powered by Blogger.

    Labels

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