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:

Solution:

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

    Alert Param Table

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

      Alert Parameter Updating Form

    • 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.
    SELECT DISTINCTROW Alert_Param.*
    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]
    Next
    rst1.Close
Else
    Exit Function
End If

Set rst1 = cdb.OpenRecordset("Alert_inQ", dbOpenDynaset)
For j = 1 To reccount
    rst1.MoveFirst
    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.Edit
        rst1![Updated] = True
        rst1.Update

    End If
        rst1.MoveNext
    Loop
    '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()
Next

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
   Loop
Next

WKAlert_Exit:
Exit Function

WKAlert_Err:
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