Introduction.
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:
- 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
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 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 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
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.
Download AlertDemo.zip
No comments:
Post a Comment
Comments subject to moderation before publishing.