By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 3,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

How to get Access to Display a Message Alert for New Records?

P: 10

I have a project management database that tracks the status of various projects that have been assigned to users. I was wondering if there is a way to get MS Access to beep and display an alert box when a user has a newly assigned project? The database has a main project form (FRM_staff_project) that uses qry_staffprojects as the source of the project data. The main form is a split form.

A newly assigned project would have a value of 7 in the Status_ID field (which translates to "New"). A newly assigned project would also have a value of 0 in the Order field.

Any help would be appreciated! Attached are pictures of the main form and query.


PS The main form includes a subform that runs VBA on load (Thanks to Twinnyfo and NeoPa).

For more details regarding the subform, please see Open Subform to Record Closest to Today

Attached Images
File Type: jpg frm_staff_projects.jpg (67.1 KB, 214 views)
File Type: jpg qry_staffprojects.jpg (82.3 KB, 220 views)
May 6 '18 #1
Share this Question
Share on Google+
5 Replies

Expert 100+
P: 1,430
Hi Karen

How I hate split forms. You will get much the same information using a form & subform where you are in charge of what information is displayed, how it is displayed, rather than Access being in charge.

That aside.

Have you considered adding a Yes/No field called "ProjectSeen" to your project table with a default value of "No". Then on the continuos subform (Split form if you will) have a check box which you can set to ProjectSeen = "Yes"

Rather than using a Message box to indicate that there is a new project, I would use conditional formatting to highlight any project that still has the ProjectSeen flag set to "No"

This has the advantage that if a user has more than 1 new project assigned to them, they are all highlighted.

May 7 '18 #2

Expert Mod 2.5K+
P: 3,158
You could also have the DB generate an e-mail for the specified user. Then, whenever a new project is assigned, they receive an e-mail that informs them. Is is a bit of a greater leap of overhead, but it just depends on what. You want to do and how you want the DB to do it.
May 7 '18 #3

P: 10
Right now, new assignments appear at the the top the list and the status is bolded. Unfortunately, some people are not noticing their new assignments. Ideally, they do want to receive an e-mail listing the newly assigned projects but also want a more visible/audible alert when they open the project form if possible. Karen
May 7 '18 #4

Expert Mod 2.5K+
P: 3,158

This may be a bit more than you are willing/able to digest at this point, but you may want to check out this article on How to Create User Permissions and Customized Menus in Access. You may be able to use some of the same principles found there in notifying users of new assignments with various pop up forms or notifications.

Even so, I think Phil’s idea of having a “ProjectSeen” flag is a good one. It is a simple concept, but implementing it may be tricky. In the corporate world, think about how many times you get pop up warnings from your IT department and you simply ignore what it says or just click OK....

A more bold approach may be to allow each user to see a list of their assignments (a printable report). Then, there is no question that they have accsss to their assignments.

There are many ways to skin this cat, and we can work through options with you.
May 8 '18 #5

P: 10
Thanks Twinnyfo and PhilofWalton,

I might be able to implement those suggestions in the future. However, right now they're pretty set on this approach. The issue is more that they don't recognize that they have new projects even though the new projects are listed at the top the list. I was able to develop coding that appears to work. However, it seems to interfere with the functioning of the vba code in the subform. Specifically, the subform no longer opens to the record closest to today. Here's the vba coding for the main form (this is an event procedure in FRM_staff_project):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. DoCmd.SetOrderBy "[Order], [Due Date]"
  4. On Error GoTo Done
  5.     Dim db          As DAO.Database
  6.     Dim rstb         As DAO.Recordset
  7.     Dim strSQLb      As String
  8.     Dim TheOrd     As Long
  9.     Dim lngStaffb    As Long
  12.     lngStaffb = Me.ID_staff
  14.          strSQLb = "SELECT Min(Order) as Ord " & _
  15.             "FROM qry_staffprojects_test " & _
  16.             "WHERE (ID_staff = " & lngStaffb & ")"
  18.         Set db = CurrentDb
  19.         Set rstb = db.OpenRecordset(strSQLb)
  21.        TheOrd = rstb.Fields(0)
  23. If TheOrd = 0 Then
  24. Beep
  25. MsgBox ("You Have New Projects!  Please update the Status and Start Date.")
  26. End If
  27.         rstb.Close
  28.         db.Close
  29.         Set rstb = Nothing
  30.         Set db = Nothing
  33. Done:
  34.     Exit Sub
  35. End Sub
Here's the vba code from the subform with the milestones (an event procedure in frmvw_milestones):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error GoTo Done
  3.  Dim db As DAO.Database
  4.     Dim rst As DAO.Recordset
  5.     Dim strSQL As String
  6.     Dim TheDate As Date
  7.      Dim lngStaff As Long
  9.     Set db = CurrentDb
  12.     lngStaff = Me.ID_staff
  15.     strSQL = "SELECT max(Milestone_Date) as Dte " & _
  16.         "FROM qryvw_milestones " & _
  17.         "WHERE ((Milestone_Date<=now()) " & _
  18.         "AND (ID_staff = " & lngStaff & "))"
  19.     Set rst = db.OpenRecordset(strSQL)
  20.     TheDate = rst.Fields(0)
  21.     Me.Milestone_Date.SetFocus
  22.     DoCmd.FindRecord TheDate
  23.     Me.Milestone_Date.SetFocus
  26. Done:
  27. Exit Sub
  29. End Sub
Again, the subform works perfectly without the added coding in FRM_staff_project. However, it now seems to be unable to either focus on the milestone date and/or execute the DoCmd.FindRecord method. Everything in the subform above the last three lines appears to be still working fine ("TheDate" returns the correct date when I added a message box to debug).

May 10 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.