I'm not real sure what you are trying to do, but you could try this:
Code:
Private Sub Form_Load() 'On Load of the switchboard check Jobs table for any uncompleted jobs
Dim intStore As Integer
Dim intStore1 As Integer
Dim msg As String
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[PeopleID]", "[qryAllDeskVacatesIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND FirstEmailReminder IS NULL")
intStore1 = DCount("[PeopleID]", "[qryAllDeskVacatesSEcondReminderIn14Days]", "[VacateDesk]= True AND EmailAddress IS NOT NULL AND SecondEmailReminder = Date()")
'checks to see if due date is within 14 days
If intStore = 0 Then
MsgBox "No uncomplete jobs that are past the Expected Completion Date", vbOKOnly
Exit Sub
Else
msg = "There are " & intStore & " people " & vbCrLf & vbCrLf & "Would you like to see these now?"
If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmVacateDeskReminder", acNormal
End If
End If
'have a popup two weeks later to send another reminder
If intStore1 = 0 Then
MsgBox "No uncomplete jobs that require 2 week reminder", vbOKOnly
Exit Sub
Else
msg = "There are " & intStore1 & " where " & vbCrLf & vbCrLf & "Would you like to see these now?" '<<-- Not sure about this text. Seems incomplete...
If MsgBox(msg, vbYesNo, "Notify that desk needs vacating...") = vbYes Then
DoCmd.Minimize
DoCmd.OpenForm "frmVacateDeskReminder14", acNormal
End If
End If
End Sub
Note:
Forms "frmVacateDeskReminder" and "frmVacateDeskReminder14" would need to be Pop Up and Modal.