SENDING ALERTS TO WORKSTATIONS
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:
- 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
- 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 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));
- Save the above Query with the name Alert_inQ.
- 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;
- 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.
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.