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):
-
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_milestones):
- 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)
-
Me.Milestone_Date.SetFocus
-
DoCmd.FindRecord TheDate
-
Me.Milestone_Date.SetFocus
-
-
-
Done:
-
Exit Sub
-
-
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).
Karen