Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.



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

MS-Access Front-End Applications A, B & C are linked to a common Table in a Back-End Database on the Network. Application B & C Users can update the 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 need immediate action from B & C Users.

There must be a way to inform Application A Users to give priority to 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 on their side of the work. It is not necessary to keep B & C Applications open to receive Alert Messages.

Functional Diagram

A Functional Diagram of this arrangement is given below:

The Solution:

  1. Create a new table with the name Alert_Param with similar fields given below in the BE Database:

    • Create Key fields like Supplier Code, Invoice Number, and Invoice Date to link with the Main Table contents. These Fields together must be defined as the Primary Key. 

    • 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 Workstations 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 an 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 the alert was targeted) 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 to 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 the program only.

    • Link the parameter table Alert_Param in BE to all the three Applications (A, B & C).

    • Install the Alert Form in all three Applications.

    • In Application A, open the Alert Form in the 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 match 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

Note: DOS Command NET SEND works under Windows XP only, later Versions of Windows use the MSG Command. Type MSG /? On the Command prompt for help and optional parameter listing.

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 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 belonging to a particular Workstation can be combined into a single message through the above Queries and sent without repeating 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 in 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 belonging to them based on the parameter table.

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


Download AlertDemo.zip


No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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