Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.


Let us look into a scenario to understand the importance of this method.

MS-Access Applications A, B & C are linked to a common Table in a Back-End Database on the Network. Application B & C Users can update information in this Table and complete their side of the work only after the main records are introduced by Application A Users. All records are not very urgent in nature but some of them are and needs immediate action from B & C Users.

There must be a way to inform Application A Users to give priority for certain Data for updating in the System. When they do, B & C Users must get Alerts on the updated status of those records, so that they can open their Applications and act upon their side of the work. It is not necessary to keep B & C Applications open to receive Alert Messages.

A Functional Diagram of this method is given below:


  1. Create a new table with the name Alert_Param with similar fileds given below in the BE Database:
    • Create Key fields like Supplier Code, Invoice Number, Invoice Date to link with the Main Table contents. These Fields together must be defined as Primary Key to prevent duplicate values.
    • One Text Field with the name WrkStation to record the Workstation name on the Windows Network. This will be recorded automatically when B & C Users enter the Key Field Values from their respective Workstation and will be used by the Machine connected to Application A to send Alerts to others.
    • One Logical Field (Yes/No) with the name Updated to mark as alert sent to Workstations and to avoid repeating the Alerts for the same item. If Application B & C Users missed the live alerts (PC was not On when alert was targetted) this flag can be viewed as an indication that the relevant records are updated in the main table.

      Note: Managing the parameter Table contents (adding, editing, deleting records) will be done by B & C Users only. Application A Users will only refer this list and give priority to these cases to clear from their side.

    • Design a Form with the name Alert for the parameter table.

    • Display the Property Sheet of Wrkstation Field and change the Enabled Property to No.
    • Repeat this for the Logical Field with the name Updated. Both these fields are managed and used by program only.
    • Link the parameter table Alert_Param in BE to all the three Applications (A, B & C).
    • Install the Alert Form in all the three Applications.
    • In Application A, open the Alert Form in design view. Display the Form's Property Sheet and change the following Properties to prevent modifying the parameter data manually:
    • Allow Edits = No
    • Allow Deletions = No
    • Allow Additions = No
    • Allow Design Changes = Design View Only
  2. Display the Code Module of the Form (View - - >Code), copy and paste the following Code in the Module and save the Form:
    Private Sub Form_BeforeUpdate(Cancel as Integer)
       Me![WrkStation] = Environ("COMPUTERNAME")
    End Sub
  3. Create a Query to JOIN Alert_Param Table with the Main_Table on the Key Fields (Supplier Code, Invoice Number & Invoice Date) in BE Database to pick the records that matches for sending Alerts.
    FROM Main_Table 
    INNER JOIN Alert_Param ON (Main_Table.Supl_Code = Alert_Param.SuplCode) AND (Main_Table.INVOICE = Alert_Param.INV_NO) AND (Main_Table.INVOICE_DATE = Alert_Param.INV_DATE) 
    WHERE (((Alert_param.Updated)=False));
  4. Save the above Query with the name Alert_inQ.
  5. Create a second Query as shown below using Alert_inQ as Source and save it with the name Alert_in_ParamQ.
    SELECT Alert_inQ.WrkStation
    FROM Alert_inQ
    GROUP BY Alert_inQ.WrkStation;
  6. Copy and paste the Code below into a Global Module of your Project and save it.
Public Function WKAlert()
'Author : a.p.r. pillai
'Date : 01/03/2008
'All Rights Reservered by msaccesstips.com
Dim wrkStn() As String, xlnvoice As String, msg() As String
Dim cdb As Database, rst1 As Recordset, rst2 As Recordset
Dim reccount, j As Integer, T As Double, flag As Boolean

On Error GoTo WKAlert_Err

reccount = DCount("* ", "Alert_in_ParamQ")

If reccount > 0 Then
 'check the number of workstations involved
     ReDim wrkStn(l To reccount) As String, msg(l To reccount) As String
    Set cdb = CurrentDb
    Set rst1 = cdb.OpenRecordset("Alert_in_paramQ", dbOpenDynaset)
    For j = 1 To reccount
        wrkStn(j) = rst1![WrkStation]
    Exit Function
End If

Set rst1 = cdb.OpenRecordset("Alert_inQ", dbOpenDynaset)
For j = 1 To reccount
    flag = False
    Do While Not rst1.EOF
    If flag = False Then
        msg(j) = " UPDATED "
        flag = True
    End If
     'add the Supplier Invoice details.
    If rst1![WrkStation] = wrkStn(j) Then
        msg(j) = msg(j) & "Supl.Code: " & rst1![SuplCode] & ", " & "Invoice: " & rst1![Inv_No] & ", " & "Inv.Date: " & rst1![Inv_Date] & ", : "
        rst1![Updated] = True

    End If
    'Use the NET SEND command and format the message
     msg(j) = Left(msg(j), Len(msg(j)) - 2)
    msg(j) = "NET SEND " & wrkStn(j) & msg(j)
    msg(j) = msg(j) & " on " & Now()

For j = 1 To reccount
    Call Shell(msg(j))
' send message through Network
     T = Timer
    Do While Timer < T + 0.5
       DoEvents 'Delay Loop for the next message

Exit Function

MsgBox Err.Description, , "WKAlert"
Resume WKAlert_Exit
End Function

Consider Options for Sending Alerts to Workstations.

Automated Alert.

a. This is the recommended method when several records are added to the Main Table in batch mode (i.e. at the end of some intermediate processing within the Application A) pertains to the parameters created by B & C Users. Call the Function WkAlert() at the end of the Process Steps.

In such situations all updated record references belongs to a particular Workstation can be combined into a single message through the above Queries and sent without repeating for each record. This is the most efficient way, which will send the Alerts automatically and will reduce the number of messages . The VBA Code presented here uses this method and runs after adding the records to the Main Table.

b. Run the Program from the After Update Event of a particular record updated in Application A which will reference the parameter table created by B & C Application Users and if it matches with the record updated into the Main Table then sends an Alert to the Workstation for that record. Sets the Updated Flag on the Parameter Table for that record.

Sending Alerts Manually

If the above methods are not convenient then Alerts can be sent manually to the respective Workstations by clicking a Command Button after updating all or some of the records that have been requested through the parameter table.

The Button Click can invoke the Program and send a single message to each Workstation for a group of records belongs to them based on the parameter table.

This method depends on the manual intervention from Users and it cannot be classified as an efficient one. But, this will be better than sending Alerts at record level, explained under item b. above.

Download Demo Database


No comments:

Post a Comment


Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Combo Boxes Custom Wizards DOS Commands Data Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation Class Module Data Type msaccessprocess security advanced Access Security Custom Functions Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Field Type Fields Form Form Instances Join Methods Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls 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 Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Base Class and Derived Object Variants

Last week we have tried an example as how to pass a Base Class Object, through the Set Property Procedure,  to become part of the Object in...


Blog Archive

Recent Posts