UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Messaging System    
Messaging System

Image:NotifInfo.gif This page has been marked as requiring review

Links to other pages that appear as dead links may have pages with a slightly different spelling - I haven't searched the index yet to make sure the dead links in this article are actually dead links

If this page has been reviewed please remove the {{REVIEW}} template from the page's source markup.

Article Information
Accompanying Wiki Content

Class: Message Engine

This is not a messaging system in the sense of users communicating with each other. See the AlmostIM Code Archive entry for such an example.

This article is intended to provide a concept only and not a cut and paste working example. Any accompanying code provided or details given may need modification to work in a given scenario. Expect that some table and object names may be interchanged slightly between code and text.

See the Accompanying Wiki Content for the Class Module that goes along with this article.



A messaging system can be used as an integral part of a database application to alert users and the application of certain events that occur. Examples of user-related messaging may include alerting when a new order is entered, when an invoice goes past due, when an employee anniversary or review is due, or when a minimum quantity flag is thrown. System messages can be used to notify the system of elapsed time since last maintenance procedures were run, or when a database grows beyond a given size. This article will focus on the User related aspect of a messaging system. The system related messages can be incorporated into the same structure with little extra effort.

There are three essential parts to a messaging system.

  1. The Message Bank
  2. Subscriptions
  3. The UI Display

The Message Bank

The Message Bank is a table where every message and its core details will be entered. Information such as the message’s unique ID, it’s subject, the actual message, and the date it was created will be stored here. Also the Type of message (see below) and any reference ID that the message should be associated with.


The Subscriptions are the interaction layer between the Message Bank and the User. This will define what users see what messages, what the user-specific status of the message is (read, unread, priority, etc). There are two tables required for the Subscriptions:

  1. The User’s Subscription List
  2. The junction between Users and Messages

(The User’s Subscription List is implemented as a child table of the Users table, and in this article is based on a custom built User rather than a User in the Microsoft Workgroup Security sense).

The Subscription List is a list of Message Types that the user will subscribe to. For instance, a user might want to know when a new order is added, and thus will subscribe to the Orders, New message type. Any messages that are added to the Message Bank and have a Orders, New message Type will then be displayed to this user.

The junction between the Users and the Messages is where all user-specifc data concerning the message will be held. This table will include the UserID, the MessageID, the Priority and the Status. This table is queried to provide the user with a list of up to date Messages.

The UI Display

The User Interface must then account for the message system and provide some means of letting the user know about messages as well as interact with them. This should generally be easily visible and readily available to the user - If they have to go out of their way to find them the messaging system won’t be nearly as effective as an alerting tool. For the purposes of this article, we’ll assume the message interface is embedded into the user’s home page via subform. We’ll call this the Message Panel.

Finding New Messages

Because the Message Bank and Subscription sections do not interact at all with the Message Panel, it will be up to the Panel to periodically check for new messages in the Subscription Junction for the given user. This can be accomplished by requerying the form’s data on a timer – say every 5 or 10 minutes.

Interacting with Messages

Users will want a way to interact with messages: a method to say yes, I’ve read this, remove it from my list or possibly to archive it for later use or to flag it as a high priority item. Another form of interaction is giving the user a reference ID and allowing them to easily find more information regarding the subject. For example, if a user sees they have a message for an overdue invoice, which was created in the Message Bank with a Type of Invoice, Overdue and a Reference of 123456 (the invoice number), this information can be passed along to the user who can then click a button to open the appropriate Invoices form at Invoice 123456.

Table Structures

A conceptual overview of the required tables:

tblMessagesThe Message Bank
tblUserSubscriptionsChild table of tblUsers
jncUserMessagesJunction table between Users and Messages

The following relationship window picture shows tables and fields slightly different than what is otherwise referred to in this article. The table and fields shown in this relationship view are exactly what is used in the Message Engine class. The class works off a query of the message table, which is split on a one to one relationship to hold the memo message field separately (not shown)

The Working Class

Having a brief description of the tables required to make this work, let’s now take a look at how the events that create messages actually get that information where it needs to go. The clsMsgEngine companion class module(s) handle much of this for us. For now, we’ll concentrate only on event driven messages rather than system related messages.

Event driven messages are relatively easy to capture and distribute as each one is tied to some particular event that you should have easy access to from within your application’s code. For instance, to send a message when a new order is created, you can use the Before or After Update event in the Orders form to create and distribute a new message. Below is a brief example of how to use the clsMsgEngine class to perform this the task of Creating and Distributing the message (note that the Creation and Distribution of the Message are handled separately to give a finer level of control so we can handle miscellaneous cases that may arise in the future).

Private Sub psMsgDist(bNew As Boolean)

 Dim msg As New clsMsgEngine
 msg.RefType = IIf(bNew, msgrefCustomerOrder, msgrefOrderChanged)
 msg.RefID = Me.ctlOrder
 msg.Subject = _
     IIf(bNew, "New ", "") & "Order #" & Me.ctlOrder & " for " & Me.ctlCustomer & _
     IIf(bNew, "", " updated")
 If bNew Then
   msg.Message = _
       "Order #" & Me.ctlOrder & " for " & Me.ctlCustomer & " has been created by " & _
       User & " at " & Now()
   msg.Message = _
       "Order #" & Me.ctlOrder & " for " & Me.ctlCustomer & " has been updated by " & _
       User & " at " & Now()
 End If

 Set msg = Nothing
End Sub

Aside from the obvious setting of the properties such as the Subject and Message, here’s what the class does for us in a nutshell (for complete documentation, see the inline notes in the class code).

The class generates a new message in the tblMessages table, including relevant information such as the Type, Reference, the Date, etc. Importantly, the Creation of the Message returns to us the MessageID, which will be later referenced for the distribution. Remember – the Users won’t directly see the Message Bank: we will need to “Distribute” the message to the junction table for the appropriate users before they become visible. This is the next step.

In the same instance of the class, you call the Distribute method. This method takes the previously generated message, finds all users who are subscribed to the message’s Type, and adds a record into the junction table with a default status (unread) and priority (based on user settings) for each applicable user.

The UI (in a little more detail)

The User Interface can mainly consist of a datasheet list to display the relevant messages, along with a few buttons – consider one to remove the message (mark it as read), and another one to navigate to the ReferenceID provided by the message. Something like this:


The driver behind this setup will be the datasheet subform. This will be bound to the junction table and filtered based on the User and Message Status. If you like you can include conditional formatting to bold unread messages, color the text red for urgent messages and so on and so forth. The parent form and it’s controls are unbound and their content is driven by the selected subform record.

Most importantly, the subform must either have a Dynaset type recordsource or include a timed requery of itself to capture changes. A thorough consideration of concurrency and locking with the use of a Dynaset recordset type should be taken into account before deciding on which method to use.

Changing Status

The change of message status (the Remove From List button) is easily accomplished by running an update query on the junction table which sets a desired status and requeries the list.

Viewing a Referenced Entity

Each message must have a Type associated with it, and thus we have a means to determine what table or entity that the message will relate to. This, in concordance with a supplied ReferenceID makes it possible for us to provide the user with a method to navigate to that entity with the click of a button. (see the OpenFormEx Code Archive entry – this module was designed explicitly for performing this task due to the unknown state of the environment at the time a user might want to view this entity).

For instance, when a Message is created that has a type of Order – New, and we can supply a ReferenceID with that message. Let’s say that order number 12345 was entered, and we supply this information to the message as a reference. Now, on the other side, the user can see that we have an Order –New type Message, with a reference of 12345. In a little bit of code behind the form, we link the Order – New type with our orders form, and our ReferenceID with the order form’s key field. At this point it is as simple as using OpenFormEx to open/manipulate the desired form and navigate to the record that was referenced.

 Select Case pRefType
   Case msgrefCompanyEdit, msgrefNewCompany
     sForm = "frmCompanies"
     KeyField = "fldCode"
   Case msgrefCustomerOrder
     sForm = "frmOrders"
     KeyField = "fldOrderID"
   Case msgrefEmployeeUpdated, msgrefVacationTransaction
     sForm = "frmEmployees"
     KeyField = "fldID"
   Case msgrefQuoteEntered
     sForm = "frmQuotes"
     KeyField = "fldID"
   Case Else
     GoTo Exit_Proc
 End Select

 OpenFormEx FormName:=sForm, _
            SaveRecord:=dsSaveRecordPrompt, _
            NavRecordID:=pRefID, _

Running a Service

The majority of this article dealt with the User side (Event Driven Messages), so we'll touch briefly on messages that are not directly driven by events. These messages have to be queried for on a fairly regular basis, examples including employee anniversaries or reviews pending and time-based data flags such as overdue invoices.

Running such queries from a standard user's front end is not necessary, and will free up system time on the local client. Create a small utility database that can reside on the server and run at a scheduled time and have it query for these types of events. This service application can then create and distribute messages based on it's findings.


Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 9,285 times.  This page was last modified 03:29, 11 February 2012 by Jack Leach. Contributions by Ace  Disclaimers