Running two Select Statements in One Event (1 Viewer)

[irunergoiam]

I’m trying to run two Select statements from one event (a button on a form without fields) where the “WHERE” criteria are different.

If I was doing this from a form where I could use the value of a field to do an "ELSE…Then…End If" statement, it would be no problem, but basically I’m trying to have this loop through the first Select statement where certain criteria are true and then move to the next Select statement where other criteria are true (the values not true in the first Select statement are true in the second Select statement). I need it to run this way as the verbiage in the e-mails is different based on the Select statement criteria.

I highlighted in red (about half way down) where I think something should go, but I can't for the life of me figure out what it is.

Here’s me raising the white flag to access and humbly asking for some gentle guidance. Otherwise, the rest of the code is good and the two Select statements work when I run them from their own button. I could have more than one button, but what’s the point of doing that if I can combine the two select statements into one event.

Just need a little nudge on this one…




Private Sub Send2DaySTARTNotifications_Click()

Dim response As Integer
response = MsgBox(" Are you sure you want to send e-mails for incomplete START requests?", vbYesNo, "2-day START Request Follow Up")
If response = vbNo Then
MsgBox ("Email Notifications Not Sent.")

Else

DoCmd.SetWarnings False

Dim rs As DAO.Recordset
Dim sBody As String
Dim sSubject As String
Dim sTo As String
Dim sCC As String

Dim sSQL As String
Me.Refresh

'Set rs = CurrentDb.OpenRecordset("select * from 1STARTRequestLog", dbOpenDynaset, dbSQLPassThrough)
sSQL = " SELECT [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS [FullName], [1STARTRequestLog].[LastName] & ', ' & [1STARTRequestLog].[FirstName] & ' - ' & [1STARTRequestLog].System & ' - ' & Date() AS [START Subject], [1STARTRequestLog].HireType, [1STARTRequestLog].Ministry, [1STARTRequestLog].DeptName, [1STARTRequestLog].Manager, [1STARTRequestLog].[Cost Center], [PL] & ' - ' & [Dept] AS [PL-Dept], [1STARTRequestLog].JobTitle, [1STARTRequestLog].PositionCode, [1STARTRequestLog].EEID, [1STARTRequestLog].ApplicantID, [1STARTRequestLog].Req, [1STARTRequestLog].System, [1STARTRequestLog].PermissionLevel, [1STARTRequestLog].DateRequestCompleted, [1STARTRequestLog].DateTimeEmail2, [1STARTRequestLog].ToSysAdminEmail, [1STARTRequestLog].CCSysAdminEmail1, [1STARTRequestLog].TASORepEmail, [1STARTRequestLog].DateEmail1, [1STARTRequestLog].ProposedStartDate, [1STARTRequestLog].ToSysAdminName" & _
" From 1STARTRequestLog" & _
" WHERE ((([1STARTRequestLog].FirstName)= 'Test') AND (([1STARTRequestLog].HireType) = 'Current PH&S CA Region Employee' Or ([1STARTRequestLog].HireType) = 'PH&S Employee Inter-region Transfer' Or ([1STARTRequestLog].HireType) = 'Traveler Converting to PH&S CA Region Employee') AND (([1STARTRequestLog].DateRequestCompleted) Is Null) AND (([1STARTRequestLog].DateTimeEmail2) Is Null));" _

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)


If rs.RecordCount > 0 Then
Do While Not rs.EOF

sSubject = "TEST (Effective Date) Follow Up START Notification: " & rs("[START Subject]")
sBody = "Dear " & rs("[ToSysAdminName]") & "," & vbNewLine & vbNewLine
sBody = sBody & "The START request sent " & rs("[DateEmail1]") & " has not yet been completed. This is a follow up notification, reminding you to complete the START request as determined by the department manager. Once you have completed this request, please conserve the details of this e-mail thread by FORWARDING this mail to " & rs("[TASORepEmail]") & " with any applicable username and password logon information for HR to close this START request." & vbNewLine & vbNewLine
sBody = sBody & "Name: " & rs("[FullName]") & vbNewLine
sBody = sBody & "Hire Type: " & rs("[HireType]") & vbNewLine
sBody = sBody & "Ministry: " & rs("[Ministry]") & vbNewLine
sBody = sBody & "Department Name: " & rs("[DeptName]") & vbNewLine
sBody = sBody & "Manager: " & rs("[Manager]") & vbNewLine
sBody = sBody & "Cost Center: " & rs("[Cost Center]") & vbNewLine
sBody = sBody & "PL - Dept: " & rs("[PL-Dept]") & vbNewLine
sBody = sBody & "Job Title: " & rs("[JobTitle]") & vbNewLine
sBody = sBody & "Position Code: " & rs("[PositionCode]") & vbNewLine
sBody = sBody & "Employee ID: " & rs("[EEID]") & vbNewLine
sBody = sBody & "Applicant ID: " & rs("[ApplicantID]") & vbNewLine
sBody = sBody & "Req: " & rs("[Req]") & vbNewLine
sBody = sBody & "System: " & rs("[System]") & vbNewLine
sBody = sBody & "Permission Level: " & rs("[PermissionLevel]") & vbNewLine
sBody = sBody & "Effective Date of Transfer: " & rs("[ProposedStartDate]") & vbNewLine & vbNewLine

sBody = sBody & "NOTE: Please conserve the details of this e-mail thread by FORWARDING this e-mail to " & rs("[TASORepEmail]") & " once this request is complete with any applicable password and username information. Human Resources provides this information to the transferring employee prior to the transfer effective date of " & rs("[ProposedStartDate]") & "." & vbNewLine & vbNewLine
sBody = sBody & "If you are not the administrator for this system, please forward this request to the appropriate system administrator or contact the START system administrator at start@providence.org. A follow up e-mail will be sent in 2 days to escalate this request if system access is incomplete." & vbNewLine & vbNewLine
sBody = sBody & "Thank you," & vbNewLine & "START Administrator"
sTo = rs("[ToSysAdminEmail]")
If rs("[CCSysAdminEmail1]") = Null Or Len(rs("[CCSysAdminEmail1]")) > 0 Then
sCC = rs("[CCSysAdminEmail1]")
End If

If sTo <> "" Then
SendEmail sTo, sCC, "", sSubject, sBody
End If
rs.MoveNext
Loop

Else
'msgbox "No records found."
End If '' Recordcount > 0

'SEEMS LIKE I NEED SOMETHING HERE TO LOOK AT THE WHERE STATEMENT BELOW IF THE SELECT WHERE STATEMENT ABOVE IS NOT TRUE

Dim rs1 As DAO.Recordset
Dim sBody1 As String
Dim sSubject1 As String
Dim sTo1 As String
Dim sCC1 As String

Dim sSQL1 As String
Me.Refresh

'Set rs = CurrentDb.OpenRecordset("select * from 1STARTRequestLog", dbOpenDynaset, dbSQLPassThrough)
sSQL1 = " SELECT [LastName] & ', ' & [FirstName] & ' ' & [MiddleName] AS [FullName], [1STARTRequestLog].[LastName] & ', ' & [1STARTRequestLog].[FirstName] & ' - ' & [1STARTRequestLog].System & ' - ' & Date() AS [START Subject], [1STARTRequestLog].HireType, [1STARTRequestLog].Ministry, [1STARTRequestLog].DeptName, [1STARTRequestLog].Manager, [1STARTRequestLog].[Cost Center], [PL] & ' - ' & [Dept] AS [PL-Dept], [1STARTRequestLog].JobTitle, [1STARTRequestLog].PositionCode, [1STARTRequestLog].EEID, [1STARTRequestLog].ApplicantID, [1STARTRequestLog].Req, [1STARTRequestLog].System, [1STARTRequestLog].PermissionLevel, [1STARTRequestLog].DateRequestCompleted, [1STARTRequestLog].DateTimeEmail2, [1STARTRequestLog].ToSysAdminEmail, [1STARTRequestLog].CCSysAdminEmail1, [1STARTRequestLog].TASORepEmail, [1STARTRequestLog].DateEmail1, [1STARTRequestLog].ProposedStartDate, [1STARTRequestLog].ToSysAdminName" & _
" From 1STARTRequestLog" & _
" WHERE ((([1STARTRequestLog].FirstName)= 'Test') AND (([1STARTRequestLog].HireType) = 'Standard New Hire' Or ([1STARTRequestLog].HireType) = 'Reinstated, Former Providence Employee' Or ([1STARTRequestLog].HireType) = 'Rehired, Former Providence Employee') AND (([1STARTRequestLog].DateRequestCompleted) Is Null) AND (([1STARTRequestLog].DateTimeEmail2) Is Null));" _

Set rs1 = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)


If rs1.RecordCount > 0 Then
Do While Not rs.EOF

sSubject1 = "TEST (No Effective Date) Follow Up START Notification: " & rs1("[START Subject]")
sBody1 = "Dear " & rs1("[ToSysAdminName]") & "," & vbNewLine & vbNewLine
sBody1 = sBody1 & "The START request sent " & rs1("[DateEmail1]") & " has not yet been completed, this is a follow up notification, reminding you to complete the following START request as determined by the department manager. If you have completed this request, please FORWARD this mail to " & rs1("[TASORepEmail]") & " with any applicable username and password logon information for HR to close this START request." & vbNewLine & vbNewLine
sBody1 = sBody1 & "Name: " & rs1("[FullName]") & vbNewLine
sBody1 = sBody1 & "Hire Type: " & rs1("[HireType]") & vbNewLine
sBody1 = sBody1 & "Ministry: " & rs1("[Ministry]") & vbNewLine
sBody1 = sBody1 & "Department Name: " & rs1("[DeptName]") & vbNewLine
sBody1 = sBody1 & "Manager: " & rs1("[Manager]") & vbNewLine
sBody1 = sBody1 & "Cost Center: " & rs1("[Cost Center]") & vbNewLine
sBody1 = sBody1 & "PL - Dept: " & rs1("[PL-Dept]") & vbNewLine
sBody1 = sBody1 & "Job Title: " & rs1("[JobTitle]") & vbNewLine
sBody1 = sBody1 & "Position Code: " & rs1("[PositionCode]") & vbNewLine
sBody1 = sBody1 & "Employee ID: " & rs1("[EEID]") & vbNewLine
sBody1 = sBody1 & "Applicant ID: " & rs1("[ApplicantID]") & vbNewLine
sBody1 = sBody1 & "Req: " & rs1("[Req]") & vbNewLine
sBody1 = sBody1 & "System: " & rs1("[System]") & vbNewLine
sBody1 = sBody1 & "Permission Level: " & rs1("[PermissionLevel]") & vbNewLine & vbNewLine

sBody1 = sBody1 & "NOTE: Please conserve the details of this e-mail thread by FORWARDING this e-mail to " & rs1("[TASORepEmail]") & " once this request is complete with any applicable password and username information. Human Resources provides this information to the new employee prior to his/her hire date." & vbNewLine & vbNewLine
sBody1 = sBody1 & "If you are not the administrator for this system, please forward this request to the appropriate system administrator or contact the START system administrator at start@providence.org. A follow up e-mail will be sent in 24 hours to further escalate this request if system access is incomplete." & vbNewLine & vbNewLine
sBody1 = sBody1 & "Thank you," & vbNewLine & "START Administrator"
sTo1 = rs1("[ToSysAdminEmail]")
If rs1("[CCSysAdminEmail1]") = Null Or Len(rs1("[CCSysAdminEmail1]")) > 0 Then
sCC1 = rs1("[CCSysAdminEmail1]")
End If

If sTo1 <> "" Then
SendEmail sTo1, sCC1, "", sSubject1, sBody1
End If
rs1.MoveNext
Loop

Else
'msgbox "No records found."
End If '' Recordcount > 0


MsgBox (" Follow Up START Notifications Successfully Sent for Net Systems")

strSQL = "UPDATE 1STARTRequestLog SET 1STARTRequestLog.DateTimeEmail2 = Date()"
strSQL = strSQL & "WHERE ((([1STARTRequestLog].DateRequestCompleted) Is Null) AND (([1STARTRequestLog].DateTimeEmail2) Is Null) AND (([1STARTRequestLog].DateTimeEmail3) Is Null));"
DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If

End Sub

 

[pbaldy]

Generally speaking, I don't think you need any logic there (unless I'm misunderstanding). Just run one after the other:

Dim statements

set SQL
Open recordset
do while not rs.eof
...
loop

set second SQL
Open recordset
do while not rs.eof
...
loop

A few points. This line:

If rs("[CCSysAdminEmail1]") = Null Or Len(rs("[CCSysAdminEmail1]")) > 0 Then

makes no sense. It basically says "If the field is null or if it's not". You get away with it because of the second part, because the first would fail anyway; nothing is ever equal to Null. You'd want to use the IsNull() function. A better test for what you're doing:

If Len(rs("[CCSysAdminEmail1]") & vbNullString) > 0 Then

which will test for both Null and a zero length string.

Given that there's nothing dynamic about your SQL, I would likely use saved queries instead. They'll be more efficient, since they'll be compiled, and it would cut down on the clutter in the procedure.

 

[irunergoiam]

Thanks for your "few points" recommendations - especially regarding the use of Null.

I'm a bit stumped as I would think it would execute them both. All I'm getting to work are e-mails for the first Select statement, nothing for the second Select statement, but all of the records update the DateTimeEmail2 field in the table. So, it's skipping over the second e-mail.

 

[pbaldy]

At a quick glance, this line:

Set rs1 = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbSQLPassThrough)

is using the original SQL string, not the second one.

 

[irunergoiam]

Hmmmm... I made the change, but it still only sends out e-mails where the criteria of the first Select statement are true.

 

[irunergoiam]

Got it!!! Like the saying goes: "The devil's in the details". PBaldy, your attention to detail in drawing my attention to the sSQL that was missing the trailing "1" prompted me to take a closer look at the others. I found it... There was an rs not far frmo the sSQL you pointed out that was missing its "1"

Thanks for your attention to the details!

 

[pbaldy]

Happy to help!