Notify all users of a new record
I have a DB created in Access 2007 that has a user GUI and ADMIN GUI. The administrators can add and change records, the users can only view the records.
What I am looking for here is I have a table called ExMsgs_tbl which stores the information that users need to process requests. The users view the messages via a form (ExMsgsViewer_frm), which is simply a form with a listbox to display the messages as they are created, with some filtering capabilities via comboboxes.
What I am looking for here is when an admin adds a new record to the ExMsgs_tbl I would like all user that have the database open to be notified of the new message. I was attempting to use the on timer event of the MsgsViewer form to check every so often for a new record by doing a listbox record count and comparing it to the number of records in the table. If the numbers differed then a msgbox would display stating that there is a new message.
My problem is even though the event is on the form timer if the user click off of the screen then the form did not refresh and the notification will not show until the user refreshes the form manually.
There is not front/back end single DB accessed by about 8 people simultaneously.
I am capable of putting together a function or procedure if need by, but was not sure of the best way to move forward from here.
Any assistance would be appreciated.
~:TLH:~
What I am looking for here is I have a table called ExMsgs_tbl which stores the information that users need to process requests. The users view the messages via a form (ExMsgsViewer_frm), which is simply a form with a listbox to display the messages as they are created, with some filtering capabilities via comboboxes.
What I am looking for here is when an admin adds a new record to the ExMsgs_tbl I would like all user that have the database open to be notified of the new message. I was attempting to use the on timer event of the MsgsViewer form to check every so often for a new record by doing a listbox record count and comparing it to the number of records in the table. If the numbers differed then a msgbox would display stating that there is a new message.
My problem is even though the event is on the form timer if the user click off of the screen then the form did not refresh and the notification will not show until the user refreshes the form manually.
There is not front/back end single DB accessed by about 8 people simultaneously.
I am capable of putting together a function or procedure if need by, but was not sure of the best way to move forward from here.
Any assistance would be appreciated.
~:TLH:~
Forgot that the method for the user to "ignore" the message is generally something like a command button that says "Remind me in 10 minutes", so the user gets another message 10 minutes later, if they have not subsequently requeried/refreshed the object.
ASKER
I know I have had to do this before but can you provide an example code snippet of how to check to see if a form is open.
Thanks for the quick reply. I will attempt to integrate your soulution this afternoon.
~:TLH:~
Thanks for the quick reply. I will attempt to integrate your soulution this afternoon.
~:TLH:~
currentproject.allforms(fo
will determine whether the form is loaded or not. This will return true even if the form is open in design view, which it hopefully will not be at run-time.
will determine whether the form is loaded or not. This will return true even if the form is open in design view, which it hopefully will not be at run-time.
One way:
Simply, Admin writes to a table, with a field indicating new msg.
The user when moving to a new record, checks that table and message is displayed. Even with a simple messagebox.
The table may include the users to view the message, and option to stop the display of the image.
Simply, Admin writes to a table, with a field indicating new msg.
The user when moving to a new record, checks that table and message is displayed. Even with a simple messagebox.
The table may include the users to view the message, and option to stop the display of the image.
ASKER
Sorry it has taken so long for me to attempt to utilize the suggestions here. I will attempt to integrate them this week and see which will work best for my solution.
Thank you all for your input.
~:TLH:~
Thank you all for your input.
~:TLH:~
ASKER
fyed,
I am attempting to utilize your solution, but I am running into some issues. Here is what I have thus far:
Main form (frmMain) opens (this form provides navigation for the system, and is never closed). On that form I added the time text box (txtTime). In that box I am storing the Now() date and time on form open. Also, on open I am checking to see if the my message viewer form is open using your example of "currentproject.allforms(f
Here is where I am having issues:
"If the form is open, the code in the timer event checks to see whether the table contains any records that have been added since the time that is stored in that textbox. If so, it displays a message which allows the user to either ignore the message or perform a requery on the object that needs to be requeried (in your case, the list)."
The timmer event that is referenced is that on the main form or the viewer form, and do my records need to have a time stamp to determine if a new record has been aded since the last check? I currently have a record count for the listbox in a text box to allow the user to see how many records there are total. Can I somehow use that count to check for new records.
This is how I was attempting the notification prior to your suggestion. This is on the msg viewer form.
If you could provide a more step by step, that would be great. This project is requireing a lot more time than it should, and I have mulitple projects in the works at the moment.
Thanks again.
~:TLH:~
I am attempting to utilize your solution, but I am running into some issues. Here is what I have thus far:
Main form (frmMain) opens (this form provides navigation for the system, and is never closed). On that form I added the time text box (txtTime). In that box I am storing the Now() date and time on form open. Also, on open I am checking to see if the my message viewer form is open using your example of "currentproject.allforms(f
Here is where I am having issues:
"If the form is open, the code in the timer event checks to see whether the table contains any records that have been added since the time that is stored in that textbox. If so, it displays a message which allows the user to either ignore the message or perform a requery on the object that needs to be requeried (in your case, the list)."
The timmer event that is referenced is that on the main form or the viewer form, and do my records need to have a time stamp to determine if a new record has been aded since the last check? I currently have a record count for the listbox in a text box to allow the user to see how many records there are total. Can I somehow use that count to check for new records.
This is how I was attempting the notification prior to your suggestion. This is on the msg viewer form.
Private Sub Form_Timer()
Dim MsgCount as Long
'Check for number of records loaded agains listbox
MsgCount = Me.lstbxMessages.ListCount - 1
'If the count is off, there is a new message. Display alert and requery the listbox
If MsgCount> Me.txtCount Then
MsgBox "New Message!!!", vbInformation + vbOKOnly, "New Message Alert"
Esle
Exit Sub
End If
'Update the record count text box for the next verificaion and requery list box
Me.txtCount = Me.lstbxMessages.ListCount -1
Me.lstbxMessages.RequeryIf you could provide a more step by step, that would be great. This project is requireing a lot more time than it should, and I have mulitple projects in the works at the moment.
Thanks again.
~:TLH:~
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Project completed. No new solutions provided.
If the form is open, the code in the timer event checks to see whether the table contains any records that have been added since the time that is stored in that textbox). If so, it displays a message which allows the user to either ignore the message or perform a requery on the object that needs to be requeried (in your case, the list). If they choose to requery the object, I update the textbox on the hidden form with the current date/time using the Now() function.