Automated Alert/Message Box

[marvelousme]

I have an access database where tables are stored on SharePoint for multiple users to access simultaneously. I have a table (tblUpdates) where I record messages and updates that they would need to know that might affect how they perform their work. Every time I add a new message or update in this table, I would like a popup to show on their screens alerting them to the fact that there is a new message they need to review in the tblUpdates table. If this is possible, how should I go about doing it? Thanks in advance!!!

 

[pbaldy]

Not hard, but the tricky part is how do you keep track of who has seen the message and who hasn't? It's simple to check the table and pop the message box if there's a record in that table (or a record with a "new" flag or whatever). Do you show it every time to everybody? Or track who's looked at it and not show it to them again?

 

[marvelousme]

Ideally, I'd like to track who's seen the message so that they only get the alert once...but I haven't figured out the best way to do that.

 

[pbaldy]

Best depends on your situation and needs. One way would be a field in a user's table. You'd set it one way when you have a new message, another when they see it.

 

[isladogs]

Agree with pbaldy about the best method of recording which users have seen the message ( or at least have had it pop up on their screen.

What you clearly don't want is a standard message box as users will just click OK without reading it.

Over the years I've tried various things including:
a) creating deliberately hideously coloured forms used to display critical messages that are so 'in your face' they can't be ignored ...
b) messages that flash on/off a few times
c) dimming or completely hiding the background screen so the message stands out
https://www.access-programmers.co.uk/forums/showthread.php?t=293255
d) balloon tooltip messages in the system notifications area
https://www.access-programmers.co.uk/forums/showthread.php?t=295062

Each of these methods can be very useful ...
BUT what I've learned over the years is that many users will treat any such popup message as an irritation and close it without reading it

 

[CJ_London]

one method I've used in the past is a timestamp field in the user table.

Timestamp your messages and your routine would be to display all messages that have a timestamp later than the users timestamp. Once the user has reviewed the messages, a button click updates the user table with the current time - or to be safe, note the time when the user opened the list of messages and update with that time instead - reason being you may have added another message whilst the user is reviewing which won't have been displayed unless the form has been refreshed.

Note this is not suitable if the user needs to be able to select the messages they don't want to see again - as Paul says, depends on your situation and needs.

As regards generating a popup to say there is a new message. I use an appropriate event (such as choosing an option) on the (usually main) navigation form to check if there are any messages later that the user timestamp, and if so display a warning or the messages.

This also has the benefit of being able to provide context sensitive messages - e.g. a message about the customer section of the db is only generated when a user enters or navigates that section - so those users who spend all their time in suppliers will never see the message (note would need a timestamp field in a linked table to the user table, one record for each context)

 

[marvelousme]

Wow!!! My head is spinning from all the fantastic ideas you guys have provided. Thank you so much to all who have responded! It sounds like I need a message that will certainly capture the users attention and ensure that they read it instead of ignoring it or just clicking it away. So now I'm trying to figure out what my backend needs to look like. I currently have just one table that includes the message category, the message comment, and the date that the message was entered. So my first question is:

Do I need to make a second table that will record each user who has acknowledged that they read my message....or do I just add something to my original table?

So the ideas that you've given me are wonderful and I haven't decided on which one to use. But once I do, my second question is:

How can I "push" the message to appear on their system? I don't want them to have to click on anything to see if there is a new message available. I'd like the message to automatically appear whenever a new alert is added to my table.

I was thinking maybe I could add some sort of code that would refresh the database every few minutes?? Does that seem like a possibility?

 

[static]

Create 2 tables

Messages
MsgID = autonumber primary key
Msg = text

ReadMessages
MsgID = long number
User = text

Private Sub Form_Open(Cancel As Integer)
    'start timer when form opens
    'check for new messages every 10 seconds
    TimerInterval = 1000 * 10 '10 secs
End Sub

Private Sub Form_Timer()
    'display new messages in a message box
    'this only works as long as this form is open
    'open it when the application starts and set its visibility to false
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    
    'get current user - Windows logon name in this example
    thisuser = Environ("username")
    
    'get messages that user hasn't seen yet
    s = s & "SELECT m.MsgID, m.Msg                           "
    s = s & "FROM Messages m LEFT JOIN (                     "
    s = s & "  SELECT ReadMessages.msgID, ReadMessages.User  "
    s = s & "     FROM ReadMessages                          "
    s = s & "     WHERE ReadMessages.User='$usr$') rm        "
    s = s & "  ON m.MsgID = rm.msgID                         "
    s = s & "WHERE rm.msgID Is Null  "

    Set rs = db.OpenRecordset(Replace(s, "$usr$", thisuser))
    
    'display messages and log that user has seen them
    Do Until rs.EOF
        MsgBox rs("Msg").Value
        CurrentDb.Execute "insert into ReadMessages (msgID,User) values (" & rs("MsgID").Value & ",'" & thisuser & "')"
        rs.MoveNext
    Loop
    rs.Close
End Sub

 

[CJ_London]

How can I "push" the message to appear on their system? I don't want them to have to click on anything to see if there is a new message available. I'd like the message to automatically appear whenever a new alert is added to my table.

see 4th paragraph of my post.

Instead of a msgbox, have a message textbox on your form which says 'you have messages' or something similar. Instead of having the code open a messagebox, make the textbox visible or alternatively populate it with a bespoke message such as 'you have 10 unread messages'. Then hide or remove the message once the user has viewed them

 

[isladogs]

Hi

This thread has prompted me to create a demo database showing many of the ideas listed by other users and myself.
Some of the features have already been posted in other threads.
Experienced users will be familiar with many of these ideas already.

I have added this to the sample databases area as : An Attention Seeking Database

Features include various ways of creating messages to get users' attention including:
1. Formatted message boxes
2. Customised message boxes with HTML formatting & countdown timer
3. Flashing, scrolling and balloon tooltip text
4. Dim / blur / remove background
5. Adding warning sounds

NOTE: Some items are meant to be less serious than others!

In addition, I have incorporated a simple 'You have 3 new messages' type feature as described by both CJ_London & Static.

 

[ashleedawg]

Re: An Attention Seeking Database

I guess I was warned, right in the thread title...

 

[pbaldy]

FYI, Post 11 moved from the moderated sample db forum.

 

[marvelousme]

Thanks to CJ_London, ridders, and static for your input! You all have given me so many options to choose from!

Static--I started out with your code because it seemed the easiest. I got it to work BUT the message box continues to pop-up every 10 seconds and cycles through all of my messages even though each message has been added to the "MessagesReviewed" table. It's not supposed to do that, right?

Ridders--I took a look at your database (fantastic work, btw) and couldn't find an option that automatically "pushed" a messagebox each time a new memo was added. Do you think I could combine some of Static's code with one of your message box options to accomplish my goal?

CJ_London--My message form is a on the main/landing page of my database so users only see the actual form when they first open the tool. They usually work from a completely different form (opened in a separate tab). Can I still accomplish your suggestion based on this?

 

[CJ_London]

CJ_London--My message form is a on the main/landing page of my database so users only see the actual form when they first open the tool. They usually work from a completely different form (opened in a separate tab). Can I still accomplish your suggestion based on this?

don't see why not. Choose an event in the different form

 

[isladogs]

Ridders--I took a look at your database (fantastic work, btw) and couldn't find an option that automatically "pushed" a messagebox each time a new memo was added. Do you think I could combine some of Static's code with one of your message box options to accomplish my goal?

The message count is automatically updated when you open the form.
I didn't build in a way of automatically updating the message count.

To do so, make the following changes:

1. Form_Load event
Change the last line to check every e.g. 30 seconds (previously set to 0 = disabled). Choose an interval that suits you.

Me.TimerInterval = 30000

2. Form_Timer event
Add the following code in Case Else

Case Else
        Me.lblUnreadMessages.Caption = GetUnreadMesssageCount

3. You might also want to modify the properties of the form frmUserMessages to allow additions

I have uploaded a new version with these changes to the sample databases area
https://www.access-programmers.co.uk/forums/showpost.php?p=1544567&postcount=2

 

[static]

Static--I started out with your code because it seemed the easiest. I got it to work BUT the message box continues to pop-up every 10 seconds and cycles through all of my messages even though each message has been added to the "MessagesReviewed" table. It's not supposed to do that, right?

Nope. Works ok here.
I didn't use a table called "MessagesReviewed"...

 

[marvelousme]

So I'm coming across an error message when copying the code from Ridders sample DB. This section:

    N = Me.ID

    CurrentDb.Execute "INSERT INTO tblUserMessagesRead ( ID, UserName, DateRead )" & _
                " SELECT " & N & " AS ID, GetUserName() AS UserName, Now() AS DateRead;"

It's causing a Run-Time 3061 "Too few parameters. Expected 1." error. I'm not sure how to fix. Any suggestions?

 

[isladogs]

I haven't looked at this for a couple of months.
If you tell me exactly where that code is I'll have a look at it

If you're saying the error is in your db after copying the code, the obvious issue is whether you've copied the GetUserName function

 

[marvelousme]

Thanks! The code is attached to the frmUserMessages "cmdRead" button. The database I'm using has a temporary variable called "CurrentUserName" that I use in place of your "GetUserName" because user have to log into the database with an id and password. But I didn't think that would affect the code any.

I'm also getting the same error message with your code for the cmdAll button. Your code is as follows:

Dim db As DAO.Database
Dim rst As DAO.Recordset, myset As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblUserMessagesRead", dbOpenDynaset, dbSeeChanges)
Set myset = db.OpenRecordset("qryUserMessagesUnread", dbOpenSnapshot)

With myset
.MoveFirst
If .RecordCount > 0 Then
Do Until .EOF
' Debug.Print !ID, GetUserName, Now
' If rst.NoMatch Then
rst.AddNew
rst!ID = !ID
rst!UserName = GetUserName()
rst!DateRead = Now()
rst.Update
' End If
.MoveNext
Loop
.Close
End If
End With

MsgBox "All messages have been marked as read", vbExclamation, "No unread messages"
Me.cmdClose.SetFocus
Me.cmdAll.Enabled = False
Me.cmdRead.Enabled = False

Set rst = Nothing
Set myset = Nothing

End Sub

 

[pbaldy]

Probably pub time in the UK. What line errors? If you open a recordset on a query that has form parameters, you'll get that error. The simplest way around it is to surround the form reference in the Eval() function.