Using If then statment

[Swordfish]

Hello,

I have a tab called Routing with four combo boxes: Production Assigned
(Productionassignedname), Q A Reviewer(QA), Second Tester Assigned(Sectester)
and Claims Monitoring Assigned(Claimmonitor), the controls in parentheses.

In the coding below for Production Assigned and QA, the combo box will
always be required to be enabled. So the email address from these two field
will show in the To: field of the email.

But for Second Tester Assigned and Claims Monitoring Assigned they are
enable=no until another action makes them enabled. I would like to have the
coding recognize if the field is enable then the email address is added to
the To: field of the email.

The coding I have below is not working as you can see. Would someone be
able to assist?

Thank you in advance

Private Sub SendEmail_Click()
Dim SendTo As String, strMsg As String

SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID] =
" & Me.Productionassignedname)
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.Q_A)

'If Not IsNull(SecondTester) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.SecondTester)

'If Not IsNull(SecondTester) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.ClaimMonitor)

'If Not IsNull(cboQAassigned) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.QAassignedname)

'End If

Call ComposeNotesMemo(SendTo, _
"Production Assigned and QA Assigned", strMsg)

strMsg = "Good Day," & vbCrLf & vbCrLf & _
"Please note: you have been assigned " & _
" the following WIT entry" & vbCrLf & vbCrLf & _
"DataTRAK Number - " & Me![Datatrak_Number] & vbCrLf & _
"Effective Date: " & Me![EFFECTIVE_DATE] & vbCrLf & vbCrLf & _
"Production Assigned - " & Me![Production Assigned] & vbCrLf & _
"QA Reviewer assigned - " & Me![QandALead] & vbCrLf & vbCrLf & _
"Second Tester Assigned - " & Me![SecTester] & vbCrLf & _
"Claims Monitoring Assigned - " & Me![ClaimMonitor] & _
vbCrLf & vbCrLf & "Thank You"



End Sub

 

[Steve Sanford]

If I understand right, I think this should work:

'----------------------------------------------------
Private Sub SendEmail_Click()
Dim SendTo As String, strMsg As String

SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID]
=" & Me.Productionassignedname)
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.Q_A)

'check the Enabled property
If Me.SecondTester.Enabled Then
SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.SecondTester)
End If

If Me.ClaimMonitor.Enabled Then
SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.ClaimMonitor)
End If

If Me.QAassignedname.Enabled Then
SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.QAassignedname)
End If

Call ComposeNotesMemo(SendTo, _
"Production Assigned and QA Assigned", strMsg)

strMsg = "Good Day," & vbCrLf & vbCrLf & _
"Please note: you have been assigned " & _
" the following WIT entry" & vbCrLf & vbCrLf & _
"DataTRAK Number - " & Me![Datatrak_Number] & vbCrLf & _
"Effective Date: " & Me![EFFECTIVE_DATE] & vbCrLf & vbCrLf & _
"Production Assigned - " & Me![Production Assigned] & vbCrLf & _
"QA Reviewer assigned - " & Me![QandALead] & vbCrLf & vbCrLf & _
"Second Tester Assigned - " & Me![SecTester] & vbCrLf & _
"Claims Monitoring Assigned - " & Me![ClaimMonitor] & _
vbCrLf & vbCrLf & "Thank You"

End Sub
----------------------------------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hello,

I have a tab called Routing with four combo boxes: Production Assigned
(Productionassignedname), Q A Reviewer(QA), Second Tester Assigned(Sectester)
and Claims Monitoring Assigned(Claimmonitor), the controls in parentheses.

In the coding below for Production Assigned and QA, the combo box will
always be required to be enabled. So the email address from these two field
will show in the To: field of the email.

But for Second Tester Assigned and Claims Monitoring Assigned they are
enable=no until another action makes them enabled. I would like to have the
coding recognize if the field is enable then the email address is added to
the To: field of the email.

The coding I have below is not working as you can see. Would someone be
able to assist?

Thank you in advance

Private Sub SendEmail_Click()
Dim SendTo As String, strMsg As String

SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID] =
" & Me.Productionassignedname)
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.Q_A)

'If Not IsNull(SecondTester) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.SecondTester)

'If Not IsNull(SecondTester) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.ClaimMonitor)

'If Not IsNull(cboQAassigned) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.QAassignedname)

'End If

Call ComposeNotesMemo(SendTo, _
"Production Assigned and QA Assigned", strMsg)

strMsg = "Good Day," & vbCrLf & vbCrLf & _
"Please note: you have been assigned " & _
" the following WIT entry" & vbCrLf & vbCrLf & _
"DataTRAK Number - " & Me![Datatrak_Number] & vbCrLf & _
"Effective Date: " & Me![EFFECTIVE_DATE] & vbCrLf & vbCrLf & _
"Production Assigned - " & Me![Production Assigned] & vbCrLf & _
"QA Reviewer assigned - " & Me![QandALead] & vbCrLf & vbCrLf & _
"Second Tester Assigned - " & Me![SecTester] & vbCrLf & _
"Claims Monitoring Assigned - " & Me![ClaimMonitor] & _
vbCrLf & vbCrLf & "Thank You"



End Sub

 

[Jack Leach]

'If Not IsNull(SecondTester) Then

'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.ClaimMonitor)

'If Not IsNull(cboQAassigned) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.QAassignedname)

SendTo is probably not a great choice for a variable name. I don't know if
its definately reserved in vba/access, but it is on a system level. I would
prefix it with the datatype, personally.

Try checking the combobox's enabled property, and if true, append the
approriate email address:

strSendTo = Iif(Me.cboQAassigned.Enabled, _
strSendTo & DLookup(....), _
strSendTo)

This should take the initial values of string sendto, run dlookup if the
combo is enabled, or just leave strSendTo what it was if the combo isn't
enabled.


hth


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Hello,

I have a tab called Routing with four combo boxes: Production Assigned
(Productionassignedname), Q A Reviewer(QA), Second Tester Assigned(Sectester)
and Claims Monitoring Assigned(Claimmonitor), the controls in parentheses.

In the coding below for Production Assigned and QA, the combo box will
always be required to be enabled. So the email address from these two field
will show in the To: field of the email.

But for Second Tester Assigned and Claims Monitoring Assigned they are
enable=no until another action makes them enabled. I would like to have the
coding recognize if the field is enable then the email address is added to
the To: field of the email.

The coding I have below is not working as you can see. Would someone be
able to assist?

Thank you in advance

Private Sub SendEmail_Click()
Dim SendTo As String, strMsg As String

SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID] =
" & Me.Productionassignedname)
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.Q_A)

'If Not IsNull(SecondTester) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.SecondTester)

'If Not IsNull(SecondTester) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] = " & Me.ClaimMonitor)

'If Not IsNull(cboQAassigned) Then
'SendTo = SendTo & ";" & DLookup("[EmailAddress]",
"tblBenefitsEmployee", "[EmployeeID] = " & Me.QAassignedname)

'End If

Call ComposeNotesMemo(SendTo, _
"Production Assigned and QA Assigned", strMsg)

strMsg = "Good Day," & vbCrLf & vbCrLf & _
"Please note: you have been assigned " & _
" the following WIT entry" & vbCrLf & vbCrLf & _
"DataTRAK Number - " & Me![Datatrak_Number] & vbCrLf & _
"Effective Date: " & Me![EFFECTIVE_DATE] & vbCrLf & vbCrLf & _
"Production Assigned - " & Me![Production Assigned] & vbCrLf & _
"QA Reviewer assigned - " & Me![QandALead] & vbCrLf & vbCrLf & _
"Second Tester Assigned - " & Me![SecTester] & vbCrLf & _
"Claims Monitoring Assigned - " & Me![ClaimMonitor] & _
vbCrLf & vbCrLf & "Thank You"



End Sub