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

[Karen20785]

Hello,

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_proje ct) that uses qry_staffprojec ts 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.


Thanks,
Karen

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
[IMGNOTHUMB]https://bytes.com/attachments/attachment/9436d1525620637/frm_staff_proje cts.jpg[/IMGNOTHUMB]
[IMGNOTHUMB]https://bytes.com/attachments/attachment/9437d1525620637/qry_staffprojec ts.jpg[/IMGNOTHUMB]

[PhilOfWalton]

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 "ProjectSee n" 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.

Phil

[twinnyfo]

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.

[Karen20785]

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

[twinnyfo]

Karen,

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 “ProjectSe en” 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.

[Karen20785]

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_proje ct):

Private Sub Form_Load()
DoCmd.SetOrderBy "[Order], [Due Date]"

On Error GoTo Done
    Dim db          As DAO.Database
    Dim rstb         As DAO.Recordset
    Dim strSQLb      As String
    Dim TheOrd     As Long
    Dim lngStaffb    As Long
   
    
    lngStaffb = Me.ID_staff
    
         strSQLb = "SELECT Min(Order) as Ord " & _
            "FROM qry_staffprojects_test " & _
            "WHERE (ID_staff = " & lngStaffb & ")"
    
        Set db = CurrentDb
        Set rstb = db.OpenRecordset(strSQLb)

       TheOrd = rstb.Fields(0)

If TheOrd = 0 Then
Beep
MsgBox ("You Have New Projects!  Please update the Status and Start Date.")
End If
        rstb.Close
        db.Close
        Set rstb = Nothing
        Set db = Nothing
    
 
Done:
    Exit Sub
End Sub

Here's the vba code from the subform with the milestones (an event procedure in frmvw_milestone s):

Private Sub Form_Load()
On Error GoTo Done
 Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim TheDate As Date
     Dim lngStaff As Long
 
    Set db = CurrentDb
    
    
    lngStaff = Me.ID_staff
    
    
    strSQL = "SELECT max(Milestone_Date) as Dte " & _
        "FROM qryvw_milestones " & _
        "WHERE ((Milestone_Date<=now()) " & _
        "AND (ID_staff = " & lngStaff & "))"
    Set rst = db.OpenRecordset(strSQL)
    TheDate = rst.Fields(0)
    [B]Me.Milestone_Date.SetFocus
    DoCmd.FindRecord TheDate
    Me.Milestone_Date.SetFocus[/B]
      
      
Done:
Exit Sub
  
End Sub

Again, the subform works perfectly without the added coding in FRM_staff_proje ct. However, it now seems to be unable to either focus on the milestone date and/or execute the DoCmd.FindRecor d 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).

Karen