Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

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:~
0
t_hungate
Asked:
t_hungate
  • 5
  • 3
1 Solution
 
Dale FyeCommented:
Generally, when I want this type of functionality, I will create a form that opens (hidden) when the application opens.  It has a timer event which checks to see whether a particular form is open (I generally populate a textbox on this form with the time the form was opened or the last time the form was requeried).

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.

0
 
Dale FyeCommented:
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.
0
 
t_hungateAuthor Commented:
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:~
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Dale FyeCommented:
currentproject.allforms(formname).isloaded

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.
0
 
hnasrCommented:
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.
0
 
t_hungateAuthor Commented:
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:~
0
 
t_hungateAuthor Commented:
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(frmExMsgViewer).isloaded".  

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.Requery

Open in new window


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:~
0
 
t_hungateAuthor Commented:
This project has been completed, thanks to all that assisted with this question.

~:TLH:~
0
 
t_hungateAuthor Commented:
Project completed. No new solutions provided.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now