Display Message to All Current Users that a certain table ("form") has been updated

One method is to have your frontends continuously poll a particular table (or file, etc) to detect a change in the table or file, and display a message based on the result. For example, let's say you build a form in your frontend and open (and hide) this form on startup. Set the form's Timer to perhaps 1000 ms (1 second) and set the Timer Event to something like this:

Sub Form_Timer(0
  If Dir("PathToYourFile") <> "" Then
    '/found the file, do something
  End IF
End Sub

To send a message, I'd probably use an .ini file ... you can then read various sections of the ini file (and set/retrieve these sections and values pretty easily), thereby providing a way to distribute messages. For example, consider this .ini file

[Message]
Value="This is a test message"

We'll name this file Message.ini and save it at //Server1/Data/YourDatabase

In your frontend, build a new form named frmTimer. Set the Timer interval = 1000 ms (1 second), and set the On Timer event to this:

Dim strMessage As String

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "Message", "Value")
If strMessage <> "none" Then
  Msgbox strMessage
End If

To set your message, you call WriteINI

WriteINI "//Server1/Data/YourDatabase/Message.ini", "Message", "Value", "Your Message Here"

************************* CODE ***************************************************
Copy/paste the code below into a new, standard module. This allows you to easily get/set values from an INI file:

  Option Compare Database
  Option Explicit

  Declare Function GetPrivateProfileString Lib "kernel32.dll" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
  Declare Function WritePrivateProfileString Lib "kernel32.dll" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpString As String, ByVal lpFileName As String) As Long

Function WriteINI(INIFileName As String, INISection As String, INISetting As String, ValueToWrite As String) As Boolean
'/Purpose:
'/Created: 10/10/2004 07:25 AM
'/Created By: Scott

  Dim retVal      As Long

On Error GoTo Err_WriteINI
  retVal = WritePrivateProfileString(INISection, INISetting, ValueToWrite, INIFileName)


Exit_WriteINI:
  On Error Resume Next
  Exit Function

Err_WriteINI:
  Select Case Err
    'case
    Case Else
     MsgBox Err & ":" & Error$, vbCritical, "basINIRoutines" & ": " & "WriteINI"
  End Select
 
  Resume Exit_WriteINI
End Function

Function GetINI(INIFileName As String, INISection As String, INISetting As String) As String
'/Purpose:
'/Created: 10/10/2004 07:26 AM
'/Created By: Scott

  Dim mvarData       As String  ' receives the value read from the INI file
  Dim lngLength      As Long  ' receives length of the returned string

On Error GoTo Err_GetINI
  mvarData = Space(255)  ' provide enough room for the function to put the value into the buffer
  ' Read from the INI file
  lngLength = GetPrivateProfileString(INISection, INISetting, "none", mvarData, 255, INIFileName)
  mvarData = Left(mvarData, lngLength)  ' extract the returned string from the buffer
  GetINI = mvarData


Exit_GetINI:
  On Error Resume Next
  Exit Function

Err_GetINI:
  Select Case Err
    'case
    Case Else
     MsgBox Err & ":" & Error$, vbCritical, "basINIRoutines" & ": " & "GetINI"
  End Select
 
  Resume Exit_GetINI
End Function

LSM, for my own interest's sake, what is the benefit of using an ini file, instead of a table in the backend to store (and retrieve where necessary) the messages?

Really none, other than users NOT in the database can quickly and easily change the message (like the IT guy who needs to inform all users that they'll need to shut down the server at 2:00 for maintenance). Of course, the IT guy could just as easily change the message in the table or use a system function to do this ...

The other benefit is the ability to tailor messages to different groups, or to send messages to individuals. Add a Section to the INI file like this:

[GroupMessage]
DataEntry = "New tax tables will be installed on Feb. 1. Please complete all pending tax-related items prior to this date."
Telecom = "Telecom meeting this Wednesday in Room 2 at 2:00 p.m. - please review NewTeleComProcedures.doc in the common doc folder prior to meeting."

[UserMessage]
smcdaniel = "Scott, your brand new company-supplied Ferrari is now ready."

You'd of course have to alter the code I supplied to check these different sections:

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "UserMessage", CurrentUser)
strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "GroupMessage", "DataEntry")


Scott (aka LSMConulsting)

Scott, thanks for that, much appreciated.

Just one small error that I picked up in your code.

[GroupMessage]
DataEntry = "New tax tables will be installed on Feb. 1. Please complete all pending tax-related items prior to this date."
Telecom = "Telecom meeting this Wednesday in Room 2 at 2:00 p.m. - please review NewTeleComProcedures.doc in the common doc folder prior to meeting."

[UserMessage]
smcdaniel = "Scott, your brand new company-supplied Ferrari is now ready."

You'd of course have to alter the code I supplied to check these different sections:

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "UserMessage", CurrentUser)
strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "GroupMessage", "DataEntry")


Should read


[GroupMessage]
DataEntry = "New tax tables will be installed on Feb. 1. Please complete all pending tax-related items prior to this date."
Telecom = "Telecom meeting this Wednesday in Room 2 at 2:00 p.m. - please review NewTeleComProcedures.doc in the common doc folder prior to meeting."

[UserMessage]
smcdaniel = "Scott, your brand new company-supplied Ferrari is now ready. They only had bright pink available, hope thats OK"

You'd of course have to alter the code I supplied to check these different sections:

strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "UserMessage", CurrentUser)
strMessage = GetINI("//Server1/Data/YourDatabase/Message.ini", "GroupMessage", "DataEntry")

LOL ... good one!!!

You might want to also compare strMessage to the previously found value of strMessage and only display the message box if the two are different (to stop the message from popping up every second until the INI file's parameter is set to "none".

Will that slow down the server or cause other performance issues? I just thought it would be simpler, but I guess not.
Instead of displaying a message, I think I would prefer to just update the tables so that other users can see the updated
data. Let's say that two users (A and B) have the database open and are looking at different records within the same table (using a "bound" form) (Note: they're using their own front-end mdb's, but the same back-end mdb in the server). User A updates record 1 while User B is viewing some other record. User A saves record 1. Then User B views record 1. How come he sees the old data instead of the new data? Do I have to load every record individually? Does using a bound form loads all the data when the form is opened? What can I do so that the new data will be seen by other users without having to close the form or the mdb?

Thank you so much everybody for your help.

BTW, what's an .ini file?