SENDING ALERTS TO WORKSTATIONS ~ LEARN MS-ACCESS TIPS AND TRICKS

NET SEND <message> Windows Command.

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

Assume that we have three MS Access front-end applications—A, B, and C. The main Application A has a back-end Master Table that is shared by the B and C front-end databases on their respective PCs. Users of Applications B and C can update information in this table and complete their tasks and Report generation only after the main records have been created by users of Application A. Not all records are time-sensitive, but some require urgent attention from users of B and C. There is a common parameter table visible to A, B, & C where B & C Users can update the common parameter table to inform A users to prioritize certain Invoices.

Once these records are updated by User A, the System sets a Flag in the parameter table as the work from the A side is completed. In addition, users of Applications B and C will receive desktop alerts indicating the change in status, prompting them to open their applications and act on their respective tasks. Importantly, Applications B and C do not need to remain open continuously to receive these alert messages.

Functional Diagram.

A Functional Diagram of this arrangement is given below:

Right-click to open the Large Image in a New Window.

The Solution:

Create a new table named Alert_Param with similar fields given below in the BE Database:
Right-click to open the Large Image in a New Window.
- Create key fields—such as Supplier Code, Invoice Number, and Invoice Date—to establish a link with the main table’s records. These fields, when combined, should be defined as the Primary Key.
- Add a Text field named WrkStation to store the workstation name on the Windows network. This value will be recorded automatically when users of Applications B and C enter the key field values from their respective workstations. It will be used by the system running Application A to send alerts to the appropriate recipients.
- Include a Logical (Yes/No) field named Updated to indicate that an alert has been sent to the designated workstations, thereby preventing duplicate alerts for the same item. If users of Applications B and C miss a live alert (for example, if their machine was turned off at the time), this flag can serve as a visual cue that the relevant records have already been updated in the main table.
  Note: Management of the parameter table’s contents (adding, editing, and deleting records) will be performed exclusively by users of Applications B and C. Users of Application A will only reference this list to prioritize these cases and complete their part of the process.
- Finally, design a form named Alert to serve as the interface for working with the parameter table.
- Right-click to open the Large Image in a New Window.
- Display the Property Sheet of the 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 VBA.
- Link the parameter table Alert_Param in BE to all 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
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
```

Create a Query to JOIN the Alert_Param Table with the Main_Table on the Key Fields (Supplier Code, Invoice Number & Invoice Date) in the BE Database to pick the records that match 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));

Save the above Query named Alert_inQ.
Create a second Query as shown below using Alert_inQ as the Source and save as Alert_in_ParamQ.
```
SELECT Alert_inQ.WrkStation
FROM Alert_inQ
GROUP BY Alert_inQ.WrkStation;
```
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

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 an optional parameter listing.

Consider Options for Sending Alerts to Workstations.

Automated Alert.

a) This method is recommended when multiple records are added to the main table in batch mode—for example, at the end of intermediate processing within Application A—based on parameters created by users of Applications B and C. In such cases, call the function `WkAlert()` at the end of the processing steps.

When using this approach, all updated record references for a particular workstation can be consolidated into a single message (via the relevant queries) and sent together, avoiding repetition for each individual record. This is the most efficient method, as it sends alerts automatically while reducing the total number of messages. The VBA code provided here demonstrates this approach and executes after the records are added to the main table.

b) Alternatively, run the program from the After Update event of an individual record updated in Application A. In this case, the program will reference the parameter table created by Applications B and C. If the updated record in the main table matches an entry in the parameter table, then an alert is sent to the corresponding workstation for that record. The updated flag in the parameter table is set accordingly.

Sending Alerts Manually

If the above automated methods are not practical in certain situations, alerts can be sent manually to the respective workstations by clicking a command button after updating all or a selected subset of the records requested through the parameter table.

Clicking the button will run the program and send one consolidated message to each workstation for the group of records belonging to them, based on the parameter table.

While this approach relies on manual user intervention and is therefore less efficient than automation, it is still preferable to sending alerts at the individual record level, as described in item b above.