Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > access > answers

Send message to logged on user

Newbie
  
Join Date: Sep 2009
Posts: 8
#1: Sep 28 '09
Dear all,

This is my first post in BYTES.
I am new to Access VBA. I am currently doing a database which serve as a central warehouse for 2 properties. The database is put on the company network so that users from different properties can access in the same time.
I have already having a code for checking who is logged on the database using ADODB, which works fine.

Here come to the problem, how can I send individual message to the logged on users? Something like an instant message between 2 users. I tried to look in the forum and I did found a post which posted in 2006 and have a suggestion. But now the link in that post was damaged and could not open any more.

Much appriciated if anyone could give me some hints, thanks for your kindness in advance.

Angus L.
best answer - posted by ADezii
I've reduced the code to its simplest state for ease of use, but left the original code in tact for your reference. The Attachment contains 2 Databases (Front and Back End) which you will need.
  1. Copy both Databases to the Root Directory of Drive C: (C:\) for an initial Demo since the Path to the Back End has already been set.
  2. Open the Front End Database (EMail_FE.mdb). The AutoExec Macro contained within will open frmReceiveMail and position it in the Upper Left Corner of the Screen. It also will open frmSendMail and Center it on Screen.
  3. Send yourself an E-Mail by utilizing the To Drop Down Combo and a short message.
  4. In 10 seconds, the E-Mail to yourself will be shown in frmReceiveMail.
  5. Click the Mark as Read Command Button to acknowledge, and clear frmReceiveMail. I think you'll get the idea from this point on.
  6. ALL Front End Databases (EMail_FE.mdb) must be Linked to tblMessage in the Back End Database (EMail_BE.mdb) and have Full Permissions on this Table. This Table contains all E-Mail related information.
  7. You can modify the time that it takes to receive New Mail by changing the TimerInterval Property on frmReceiveMail (currently set to 10000 or 10 seconds).
  8. Have fun, and if you have any questions, feel free to ask.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,672
#2: Sep 28 '09

re: Send message to logged on user

If you post the link to the old thread you found I'll see if I can fix the link. Otherwise, it's very hard to work from a thread we cannot see.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,672
#3: Sep 28 '09

re: Send message to logged on user

Until then, I would say that Access is not well suited to such an IM system. The best I can think of would be to have a table which held such messages. You would need to implement an interface - using a form - that allowed you to post such a message to a user. You would also need a system whereby a logged on user could have the message show whenever one appeared. For this I would consider a polling mechanism, driven by the timer, which periodically checked the contents of the table. When one was found relevant to the currently logged on user it would be displayed in a form.

Welcome to Bytes!
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,213
#4: Sep 28 '09

re: Send message to logged on user

Quote:

Originally Posted by angusfreefa View Post

Dear all,

This is my first post in BYTES.
I am new to Access VBA. I am currently doing a database which serve as a central warehouse for 2 properties. The database is put on the company network so that users from different properties can access in the same time.
I have already having a code for checking who is logged on the database using ADODB, which works fine.

Here come to the problem, how can I send individual message to the logged on users? Something like an instant message between 2 users. I tried to look in the forum and I did found a post which posted in 2006 and have a suggestion. But now the link in that post was damaged and could not open any more.

Much appriciated if anyone could give me some hints, thanks for your kindness in advance.

Angus L.

NeoPa pretty much hit the nail on the head with this one, I'll just try to assist.

There is a mechanism by which you can Send 'Pseudo' Instant Messages to other Users in a Multi-User environment without using E-Mail. This system involves a Table, Form, Timer() Event, and the use of a Table in a Shared Database to which all Users have access. This Table facilitates and contains data relating to the transference of Messages. When I get a chance, I'll simply the system and send it to you as an Attachment with instructions.
Newbie
  
Join Date: Sep 2009
Posts: 8
#5: Sep 28 '09

re: Send message to logged on user

Quote:

Originally Posted by NeoPa View Post

If you post the link to the old thread you found I'll see if I can fix the link. Otherwise, it's very hard to work from a thread we cannot see.

Thanks NeoPa,
The thread is as below:

Send message to logged-on user

I saw you at the post also, haha

Cheers
Newbie
  
Join Date: Sep 2009
Posts: 8
#6: Sep 28 '09

re: Send message to logged on user

Quote:

Originally Posted by ADezii View Post

NeoPa pretty much hit the nail on the head with this one, I'll just try to assist.

There is a mechanism by which you can Send 'Pseudo' Instant Messages to other Users in a Multi-User environment without using E-Mail. This system involves a Table, Form, Timer() Event, and the use of a Table in a Shared Database to which all Users have access. This Table facilitates and contains data relating to the transference of Messages. When I get a chance, I'll simply the system and send it to you as an Attachment with instructions.

Thanks ADezii,

I got a question, how can the system identify who would be the receiver of the message? I am not expecting when i post a message in a Form, with a timer, Everone logged on would see the mesasage.

If you have have anything which likely to have similar function, You are welcome to send to my E-Mail: ** removed as per site rules **.

Thanks.
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,213
#7: Sep 28 '09

re: Send message to logged on user

Quote:

Originally Posted by angusfreefa View Post

Thanks ADezii,

I got a question, how can the system identify who would be the receiver of the message? I am not expecting when i post a message in a Form, with a timer, Everone logged on would see the mesasage.

If you have have anything which likely to have similar function, You are welcome to send to my E-Mail: ** removed as per site rules **.

Thanks.

Quote:
I got a question, how can the system identify who would be the receiver of the message?
  1. A To Combo Box on a Send E-Mail Form is populated with a list of all currently Logged-On Users using a List Filling Callback Function. The Sender is automatically assigned as CurrentUser(), select a Recipient from the To Combo Box, then just enter the Message Text. A Shared Table on the Back End is populated with the following information:
    • MessageID
    • From
    • To
    • Date Sent
    • Date Received
    • Message
  2. I'm sure that the code could be modified to return only the latest Message designated for a specific User, namely the CurrentUser() on each FE.
  3. If you are sincerely interested, I'll try to modify the code to make it work under your circumstances, but if not, please do not let me spent time on this Project. Let me know either way.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,672
#8: Sep 28 '09

re: Send message to logged on user

Quote:

Originally Posted by angusfreefa View Post

If you have have anything which likely to have similar function, You are welcome to send to my E-Mail: ** removed as per site rules **.

Angus,

Feel free to swap contact details, but using the Private Message system for this rather than the public forum. This particular rule is mainly for your own protection.

Administrator.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,672
#9: Sep 28 '09

re: Send message to logged on user

Quote:

Originally Posted by angusfreefa View Post

Thanks NeoPa,
The thread is as below:

Send message to logged-on user

You could use the LDB file to return the PC names of all logged-on users. Messages (using NET SEND) could be sent to the PC name.

Bear in mind this message would depend on the Messaging Service running on the recipient PC. It would not be received within the Access application (It would pop-up on the screen).
Newbie
  
Join Date: Sep 2009
Posts: 8
#10: Sep 29 '09

re: Send message to logged on user

Quote:
If you are sincerely interested, I'll try to modify the code to make it work under your circumstances, but if not, please do not let me spent time on this Project. Let me know either way.
Hi ADezii,
I am sincerely interested. I tried to make a Form for sending message, still I am not sure how to send to particular receiver. So now the code would show a pop-up form which all logged-on users can see the message.

Below is how I set up the Message sending Form.
A Form with a listbox, a textbox, a timer and a commandd button. User type message in the textbox, then the message saved in a table. the content in the table will be load to the listbox. Timer is set to refresh the list box every 5 seconds. This works, but there is no privacy as everyone can see the message.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3.     Me.txtuserbase = strUserbase  ' shows where is the working property
  4.     Me.txtCurrentUser = strCurrentUserName 'shows the user name
  5.  
  6. End Sub
  7.  
  8. Private Sub Send_Click()
  9. If Me.message = Null Then
  10.  
  11. MsgBox " Can't send blank message"
  12.  
  13. Else
  14. ' to save the message to a table called message_history
  15. Set rs = New ADODB.Recordset
  16.     StrTemp = "Select * From message_history" 
  17.     rs.Open StrTemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  18.  
  19.                 rs.AddNew
  20.                 rs("sender") = Me![txtCurrentUser]
  21.                 rs("message") = Me![message]
  22.                 rs("time") = Now
  23.                 rs("receiver") = ""
  24.                 rs.Update
  25.                 rs.close
  26.     Set rs = Nothing
  27.  
  28.     Me.message = Null
  29.     Me.message.SetFocus
  30.     history.Requery
  31. End If
  32. End Sub
  33.  
  34. 'timer to load and refresh data in the listbox
  35. Private Sub Form_Timer()
  36. history.Requery
  37. Dim sList As String
  38. Set myrs = New ADODB.Recordset
  39.     StrTemp = "Select * From message_history"
  40.     myrs.Open StrTemp, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  41.  
  42. While Not myrs.EOF
  43. sList = sList & myrs(0) & ";" & myrs(1) & ";"
  44. myrs.MoveNext
  45. Wend
  46. history.RowSource = sList
  47. myrs.close
  48. Set myrs = Nothing
  49. End Sub
Newbie
  
Join Date: Sep 2009
Posts: 8
#11: Sep 29 '09

re: Send message to logged on user

Also, the below code is from somewhere in Internet which i used for tracking who is logging-on the database and it work well.
Expand|Select|Wrap|Line Numbers
  1. Function ShowUserRosterMultipleUsersLocal()
  2.  
  3.     Dim cn As New ADODB.Connection
  4.     Dim rs As New ADODB.Recordset
  5.     Dim Rst As DAO.Recordset
  6.  
  7.     Dim i, j As Long
  8.     Dim StrComp As String
  9.     Dim StrUser As String
  10.     StrWhereAmI = CurrentProject.Path
  11.  
  12.  
  13.         Set cn = CurrentProject.Connection
  14.     Set Rst = CurrentDb.OpenRecordset("TblSession")
  15.     ' The user roster is exposed as a provider-specific schema rowset
  16.     ' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
  17.     ' reference the schema, as provider-specific schemas are not
  18.     ' listed in ADO's type library for schema rowsets
  19.  
  20.     Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
  21.     , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
  22. DoCmd.SetWarnings False
  23. DoCmd.RunSQL "Delete * From TblSession"
  24.  
  25.     While Not rs.EOF
  26.        Rst.AddNew
  27.        Rst.Fields(0) = rs.Fields(0)
  28.        Rst.Fields(1) = rs.Fields(1)
  29.        Rst.Fields(2) = rs.Fields(2)
  30.        Rst.Update
  31.  
  32.  
  33.  
  34.         rs.MoveNext
  35.     Wend
  36. DoCmd.SetWarnings True
  37. Set Rst = Nothing
  38. DoEvents
  39. Me.List0.RowSource = "QryCurrentUsers"
  40. End Function
  41. Function ShowUserRosterMultipleUsersRemote()
  42.  
  43.     Dim cn As New ADODB.Connection
  44.     Dim rs As New ADODB.Recordset
  45.     Dim Rst As DAO.Recordset
  46.  
  47.     Dim i, j As Long
  48.     Dim StrComp As String
  49.     Dim StrUser As String
  50.     StrWhereAmI = Me.TxtPath
  51.  
  52.     Set Rst = CurrentDb.OpenRecordset("TblSession")
  53.     With cn
  54.       .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  55.          "Data Source=" & Me.TxtPath & "\" & Me.TxtFile
  56.  
  57.     ' The user roster is exposed as a provider-specific schema rowset
  58.     ' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
  59.     ' reference the schema, as provider-specific schemas are not
  60.     ' listed in ADO's type library for schema rowsets
  61.  
  62.     Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
  63.     , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
  64.     DoCmd.SetWarnings False
  65.     DoCmd.RunSQL "Delete * From TblSession"
  66.  
  67.         While Not rs.EOF
  68.            Rst.AddNew
  69.            Rst.Fields(0) = rs.Fields(0)
  70.            Rst.Fields(1) = rs.Fields(1)
  71.            Rst.Fields(2) = rs.Fields(2)
  72.            Rst.Update
  73.  
  74.  
  75.  
  76.             rs.MoveNext
  77.         Wend
  78.     DoCmd.SetWarnings True
  79.     Set Rst = Nothing
  80.     End With
  81. DoEvents
  82. Me.List0.RowSource = "QryCurrentUsers"
  83. End Function
  84.  
  85.  
  86. Private Sub CmdBrowse_Click()
  87. Dim LastSlash As Integer
  88.  
  89.     Me.CmDlg.InitDir = CurrentProject.Path
  90.     Me.CmDlg.Filter = "Microsoft Access Database (*.mdb)|*.mdb"
  91.  
  92.     Me.CmDlg.ShowOpen
  93.     Me.TxtPath = Me.CmDlg.FileName
  94.     LastSlash = InStrRev(Me.CmDlg.FileName, "\")
  95.  
  96.     Me.TxtPath = Left(Me.CmDlg.FileName, LastSlash - 1)
  97.  
  98.     Me.TxtFile = Mid(Me.CmDlg.FileName, LastSlash + 1)
  99.  
  100. End Sub
  101.  
  102. Private Sub CmdLock_Click()
  103. If Me.CmdLock.Caption = "Lock Database" Then
  104.     Dim strMsg As String
  105.     If Nz(Me.TxtFile, "") = "" Then
  106.         StrWhereAmI = CurrentProject.Path
  107.     Else
  108.         StrWhereAmI = Me.TxtPath
  109.     End If
  110.  
  111.     strMsg = InputBox("What message do you want to show the user?", "System Down Message")
  112.     If strMsg = "" Then
  113.         Exit Sub
  114.     End If
  115.     Open StrWhereAmI & "\Locked.Txt" For Output As #1
  116.     Print #1, strMsg
  117.     Close #1
  118.  
  119.     Me.CmdLock.Caption = "Unlock Database"
  120. Else
  121.     If Dir(StrWhereAmI & "\Locked.Txt") <> "" Then
  122.         Kill StrWhereAmI & "\Locked.Txt"
  123.     End If
  124.     Me.CmdLock.Caption = "Lock Database"
  125. End If
  126.  
  127.  
  128.  
  129. End Sub
  130.  
  131. Private Sub CmdReset_Click()
  132.     Me.TxtPath = ""
  133.     Me.TxtFile = ""
  134.     Me.List0.RowSource = ""
  135.     Me.List0.Requery
  136.  
  137. End Sub
  138.  
  139. Private Sub CmdRetry_Click()
  140. Me.List0.RowSource = ""
  141. Me.List0.Requery
  142.  
  143.     If Nz(Me.TxtFile, "") = "" Then
  144.         Call ShowUserRosterMultipleUsersLocal
  145.     Else
  146.         Call ShowUserRosterMultipleUsersRemote
  147.     End If
  148. End Sub
  149.  
  150. Private Sub CmdClose_Click()
  151. On Error GoTo Err_CmdClose_Click
  152.  
  153.  
  154.     DoCmd.Close
  155.  
  156. Exit_CmdClose_Click:
  157.     Exit Sub
  158.  
  159. Err_CmdClose_Click:
  160.     MsgBox Err.Description
  161.     Resume Exit_CmdClose_Click
  162.  
  163. End Sub
  164.  
  165. Public Function FindComputerName()
  166.     Dim strBuffer As String
  167.     Dim lngSize As Long
  168.  
  169.     strBuffer = String(100, " ")
  170.     lngSize = Len(strBuffer)
  171.  
  172.     If GetComputerName(strBuffer, lngSize) = 1 Then
  173.         FindComputerName = Left(strBuffer, lngSize)
  174.     Else
  175.         FindComputerName = "Computer Name not available"
  176.     End If
  177.  
  178. End Function
  179.  
  180. Private Sub Form_Load()
  181. 'Call LockedOut
  182. End Sub
  183.  
  184. Private Sub List0_Click()
  185.     Me.CmdSend.Enabled = True
  186.     Me.CmdDisconnect.Enabled = True
  187. End Sub
Newbie
  
Join Date: Sep 2009
Posts: 8
#12: Sep 29 '09

re: Send message to logged on user

Quote:

Originally Posted by NeoPa View Post

Angus,

Feel free to swap contact details, but using the Private Message system for this rather than the public forum. This particular rule is mainly for your own protection.

Administrator.

I will be aware of this next time. Thanks NeoPa.
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,672
#13: Sep 29 '09

re: Send message to logged on user

That looks like some interesting code Angus. I think I'll make a note of the link for re-use later. If you know the original source I'd be interested to see that too.

PS. Don't forget the tags for code. Tags are done as matching pairs where the opening one is surrounded by [...] and the closing one by [/...]. A set of buttons is available for ease of use in the Standard Editor (Not the Basic Editor). The one for the [ CODE ] tags has a hash (#) on it. You can choose which editor to use in your Profile Options (Look near the bottom of the page).
Newbie
  
Join Date: Sep 2009
Posts: 8
#14: Oct 8 '09

re: Send message to logged on user

Any one got any suggestion? thanks
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,213
#15: Oct 8 '09

re: Send message to logged on user

Quote:

Originally Posted by angusfreefa View Post

Any one got any suggestion? thanks

I can E-Mail you the entire System, along with Instructions, straight from the Access Cookbook. Because of Attachment restrictions, the Files will be too large to Attach to this Forum. If you are interested, give me your E-Mail Address in a Private Message and I'll send it to you as soon as I get a chance.
ADezii's Avatar
Expert
  
Join Date: Apr 2006
Location: Philadelphia
Posts: 5,213
#16: 4 Weeks Ago

re: Send message to logged on user

I've reduced the code to its simplest state for ease of use, but left the original code in tact for your reference. The Attachment contains 2 Databases (Front and Back End) which you will need.
  1. Copy both Databases to the Root Directory of Drive C: (C:\) for an initial Demo since the Path to the Back End has already been set.
  2. Open the Front End Database (EMail_FE.mdb). The AutoExec Macro contained within will open frmReceiveMail and position it in the Upper Left Corner of the Screen. It also will open frmSendMail and Center it on Screen.
  3. Send yourself an E-Mail by utilizing the To Drop Down Combo and a short message.
  4. In 10 seconds, the E-Mail to yourself will be shown in frmReceiveMail.
  5. Click the Mark as Read Command Button to acknowledge, and clear frmReceiveMail. I think you'll get the idea from this point on.
  6. ALL Front End Databases (EMail_FE.mdb) must be Linked to tblMessage in the Back End Database (EMail_BE.mdb) and have Full Permissions on this Table. This Table contains all E-Mail related information.
  7. You can modify the time that it takes to receive New Mail by changing the TimerInterval Property on frmReceiveMail (currently set to 10000 or 10 seconds).
  8. Have fun, and if you have any questions, feel free to ask.
Attached Files
File Type: zip EMail.zip (39.0 KB, 9 views)
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,272 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved