Code worked, but would like advice for improvement

[DavidDrotts]

Hello,

I have built a database, Weekly Activity Report (WAR), that keeps track of the workflow in the following five work centers: DPC, DPE, DPF, DPM, and MOF. Updates to the progress of the tasks assigned to each work center must be updated weekly. In order to keep track of which work center flight chief (FC) has updated their tasks for the given week, I have created an Admin form that shows if each of the FCs have submitted their data for the given week. On this form there is a reminder button which, once clicked, will email the FCs that has tasks in the system that have not been updated. I have created the following unbound/bound controls to ensure that only a FC that has a task and has not updated for the week receives an email.

1. I use a textbox, one for each work center, with a Dcount in the control source, named DPCcount etc.. ), once a reminder button has been clicked, will email each FC to let them know that they have not updated their tasks.
2. I use a yes/no checkbox, for each FC, to keep track if the weekly update was complete.
Also, I need to ensure that there is an email address correctly inputted into the WAR so that a email reminder can be sent.

Long story short…I need to determine three conditions. 1 Have you updated for the week? 2. Do you have a task? 3. Is their an email address available to send the reminder to? If all three are true, then let the emails fly!

I struggled (I am very inexperienced) with the right code to use to make this happen and came up with the following mess, which works fine. In the interest of becoming better at this, would someone offer their two cents on how I could improve on this? Or is there a different way altogether that you can recommend for my future use. I played with nested If then statements and couldn’t get it to work.

Thank you all very much for your time, I sincerely appreciate it!

Cheers!
David

Private Sub Reminder_Click()
On Error GoTo Err_Reminder_Click
Dim rst As DAO.Recordset
Dim DPCNumber As Integer
Dim DPENumber As Integer
Dim DPFNumber As Integer
Dim DPMNumber As Integer
Dim MOFNumber As Integer

Set rst = CurrentDb().OpenRecordset("SELECT AdminLink.Link FROM AdminLink;")

If Me.DPCCount > 0 And Me.DPC = True Then
DPCNumber = 1
End If
If Me.DPECount > 0 And Me.DPE = True Then
DPENumber = 1
End If
If Me.DPFCount > 0 And Me.DPF = True Then
DPFNumber = 1
End If
If Me.DPMcount > 0 And Me.DPM = True Then
DPMNumber = 1
End If
If Me.MOFCount > 0 And Me.MOF = True Then
MOFNumber = 1
End If
'DPC Section
Select Case DPCNumber
Case 1
Debug.Print "selected 1"
If IsNull(Me.DPCFCAddress.Column(0, 0)) Then
MsgBox "There is no email address. Ensure that the Flight Chief's e-mail address has been correctly inputed.", vbOKOnly + vbInformation, _
"Weekly Activity Report"
Else
DoCmd.SendObject , "", "", Me.DPCFCAddress.Column(0, 0), Me.DPCFCAddress.Column(0, 1), "", "Please update the WAR", "An update is still required for the week of " & Me.FirstDaY & " thru " & Me.LastDay & "." & " The WAR program can be accessed by clicking on the following link (If prompted, select the 'OPEN IT' option)... " & "" & rst!link, True, ""
End If
End Select

'DPE Section
Select Case DPENumber
Case 1
Debug.Print "selected 1"
If IsNull(Me.DPEFCAddress.Column(0, 0)) Then
MsgBox "There is no email address. Ensure that the Flight Chief's e-mail address has been correctly inputed.", vbOKOnly + vbInformation, _
"Weekly Activity Report"
Else
DoCmd.SendObject , "", "", Me.DPEFCAddress.Column(0, 0), Me.DPEFCAddress.Column(0, 1), "", "Please update the WAR", "An update is still required for the week of " & Me.FirstDaY & " thru " & Me.LastDay & "." & " The WAR program can be accessed by clicking on the following link (If prompted, select the 'OPEN IT' option)... " & "" & rst!link, True, ""
End If
End Select

'DPF Section
Select Case DPFNumber
Case 1
Debug.Print "selected 1"
If IsNull(Me.DPFFCAddress.Column(0, 0)) Then
MsgBox "There is no email address. Ensure that the Flight Chief's e-mail address has been correctly inputed.", vbOKOnly + vbInformation, _
"Weekly Activity Report"
Else
DoCmd.SendObject , "", "", Me.DPFFCAddress.Column(0, 0), Me.DPFFCAddress.Column(0, 1), "", "Please update the WAR", "An update is still required for the week of " & Me.FirstDaY & " thru " & Me.LastDay & "." & " The WAR program can be accessed by clicking on the following link (If prompted, select the 'OPEN IT' option)... " & "" & rst!link, True, ""

End If
End Select

'DPM Section
Select Case DPMNumber
Case 1
Debug.Print "selected 1"
If IsNull(Me.DPMFCAddress.Column(0, 0)) Then
MsgBox "There is no email address. Ensure that the Flight Chief's e-mail address has been correctly inputed.", vbOKOnly + vbInformation, _
"Weekly Activity Report"
Else
DoCmd.SendObject , "", "", Me.DPMFCAddress.Column(0, 0), Me.DPMFCAddress.Column(0, 1), "", "Please update the WAR", "An update is still required for the week of " & Me.FirstDaY & " thru " & Me.LastDay & "." & " The WAR program can be accessed by clicking on the following link (If prompted, select the 'OPEN IT' option)... " & "" & rst!link, True, ""
End If
End Select

'MOF Section
Select Case MOFNumber
Case 1
Debug.Print "selected 1"
If IsNull(Me.MOFFCAddress.Column(0, 0)) Then
MsgBox "There is no email address. Ensure that the Flight Chief's e-mail address has been correctly inputed.", vbOKOnly + vbInformation, _
"Weekly Activity Report"
Else
DoCmd.SendObject , "", "", Me.MOFFCAddress.Column(0, 0), Me.MOFFCAddress.Column(0, 1), "", "Please update the WAR", "An update is still required for the week of " & Me.FirstDaY & " thru " & Me.LastDay & "." & " The WAR program can be accessed by clicking on the following link (If prompted, select the 'OPEN IT' option)... " & "" & rst!link, True, ""
End If
End Select





Exit_Reminder_Click:
Exit Sub

Err_Reminder_Click:
MsgBox "Your reminder has not been sent.", vbOKOnly + vbCritical, _
"Weekly Activity Report"
Resume Next
End Sub