Problems with unfinished job reminder form

[robertmarkdudley95]

I have a piece of code as follows:

Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs


Dim intStore As Integer


'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[JobID]", "[tblJobs]", "[Finish Date] <=Now() AND [Paid?] =0")


'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
            Exit Sub
                Else
                    If MsgBox("There are " & intStore & " incomplete jobs." & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "There are incomplete jobs...") = vbYes Then
                    DoCmd.Minimize
                    DoCmd.OpenForm "frmReminders", acNormal
                Else
            Exit Sub
        End If
    End If


End Sub

However, regardless of how many incomplete jobs there are, it still pops up with both of the jobs in my database, even the one that has been completed, as follows:



I realise that it still says Paid?. I need to sort that out. But how can I resolve this error?

[RuralGuy]

I would start by making the changes in RED and see what you get.

Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
   Dim intStore As Integer

   'Count of uncomplete jobs that are past the Expected Completion Date
   '   intStore = DCount("[JobID]", "[tblJobs]", "[Finish Date] <=Now() AND [Paid?] =0")

   intStore = DCount("[JobID]", "[tblJobs]", "[Paid?] = False")

   'If count of uncomplete jobs is zero display switchboard
   'Else display message box detailing amount of jobs
   'and give the user the option as to whether to view these or not.
   If intStore <> 0 Then
      If MsgBox("There are " & intStore & " incomplete jobs." & _
                vbCrLf & vbCrLf & "Would you like to see these now?", _
                vbYesNo, "There are incomplete jobs...") = vbYes Then
         DoCmd.Minimize
         DoCmd.OpenForm "frmReminders", acNormal
      End If
   End If
End Sub

Notice that I eliminated the Exit Sub's.

[robertmarkdudley95]

I would start by making the changes in RED and see what you get.

Private Sub Form_Load()
'On Load of the switchboard check Jobs table for any uncompleted jobs
   Dim intStore As Integer

   'Count of uncomplete jobs that are past the Expected Completion Date
   '   intStore = DCount("[JobID]", "[tblJobs]", "[Finish Date] <=Now() AND [Paid?] =0")

   intStore = DCount("[JobID]", "[tblJobs]", "[Paid?] = False")

   'If count of uncomplete jobs is zero display switchboard
   'Else display message box detailing amount of jobs
   'and give the user the option as to whether to view these or not.
   If intStore <> 0 Then
      If MsgBox("There are " & intStore & " incomplete jobs." & _
                vbCrLf & vbCrLf & "Would you like to see these now?", _
                vbYesNo, "There are incomplete jobs...") = vbYes Then
         DoCmd.Minimize
         DoCmd.OpenForm "frmReminders", acNormal
      End If
   End If
End Sub

Notice that I eliminated the Exit Sub's.

Thanks for this! I tried it, but it makes no difference. The message box is saying "There are 1 incomplete jobs", but the form is still appearing with both of the jobs in the form. I think it might have something to do with:

DoCmd.OpenForm "frmReminders", acNormal

[robertmarkdudley95]

Oh wait, it doesn't matter! I based the Reminders form on a query. I'm done!!

[RuralGuy]

Great! Glad to hear you got it sorted.