If then and Case Select

S

Swordfish

Good Day,

I now have dilemma, which perhaps someone may be able to assist me with.
*** I am having a problem with what appears to be my If then statement. Here
is the situation: I have a field called WorkCategory which is coded with
Select Case (noted below)
Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(1)
Case "RATES/DMR", "COPAY/DAW", "ACCUMULATIONS", "NEW PLAN -
COPIED/TEMPLATE", "NEW PLAN"
Me.ROUTING.Controls("ClaimMonitor").Enabled = True

Case Else
Me.ROUTING.Controls("ClaimMonitor").Enabled = False

End Select

Select Case Me.WorkCategory.Column(1)
Case "RATES/DMR", "COPAY/DAW", "ACCUMULATIONS"
Me.ROUTING.Controls("SecondTester").Enabled = True

Case Else
Me.ROUTING.Controls("SecondTester").Enabled = False

End Select
End Sub
***I have four combo boxes Production Assigned (Productionassignedname), QA
Reviewer Assigned (Q&A), Second Tester Assigned (SecondTester) and Claims
Monitoring Assigned (Claimmonitor). The coding below composes a message
through lotus notes with the names in the combo boxes to different areas of
the email - Email Addresses, the Effective Dates and Datatrak Number, etc it
looks like the following in the body of the email.

Good Day,

Please note: You have been assigned the following WIT entry:
DataTRAK Number - 181818

Effective Date - 6/23/2009 8:00:00 AM

Production Assigned - MICHAEL, MICHAEL
QA Reviewer Assigned - MICHAEL, MICHAEL
Second Tester Assigned - MICHAEL, MICHAEL
Claims Monitoring Assigned - MICHAEL, MICHAEL

Thank You

*** Second Tester and Claims Monitoring are disabled in the data field
properties of the combo boxes, until invoked by the Case Select. With the ?If
Then? I was trying to get the same results as the Production Assigned and QA
Reviewer Assigned with respect to the information in the body of the email
but only if the Second Tester or Claims Monitoring fields are enabled by the
Case Select coding.

But what is happening is: When I select an option from Work Category that
does not enable Second Testing and the Claims Monitoring, the send email will
not execute. If I select the right options to enable the two fields and then
select the information in the drop down (names) then send email will execute
with information similar to the example above. My guess is the way I have
coded the If Then.

Would you have any idea what my solution might be?

Thank you for any assistance.
**************************
Private Sub SendEmail_Click()
Dim SendTo 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
********************************
Call ComposeNotesMemo(SendTo, "Production Assigned and QA Assigned", "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 - " & DLookup("[LastName]", "tblBenefitsEmployee",
"[EmployeeID]= " & Me.Productionassignedname) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.Productionassignedname) & vbCrLf & "QA Reviewer Assigned - " &
DLookup("[LastName]", "tblBenefitsEmployee", "[EmployeeID]= " & Me.Q_A) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.Q_A) & vbCrLf & vbCrLf & _
"Second Tester Assigned - " & DLookup("[LastName]", "tblBenefitsEmployee",
"[EmployeeID]= " & Me.SecondTester) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.SecondTester) & vbCrLf & "Claims Monitoring Assigned - " &
DLookup("[LastName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.ClaimMonitor) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.ClaimMonitor) & _
vbCrLf & vbCrLf & "Thank You")
End Sub ...
 
S

Stewart Berman

Did you step through the code to see where it fails? What type of error is thrown?

BTW, the part of your message text process:
"Second Tester Assigned - " & DLookup("[LastName]", "tblBenefitsEmployee",
"[EmployeeID]= " & Me.SecondTester) _
&
References a disabled control when the Case Else is executed.

Swordfish said:
Good Day,

I now have dilemma, which perhaps someone may be able to assist me with.
*** I am having a problem with what appears to be my If then statement. Here
is the situation: I have a field called WorkCategory which is coded with
Select Case (noted below)
Private Sub WorkCategory_AfterUpdate()
Select Case Me.WorkCategory.Column(1)
Case "RATES/DMR", "COPAY/DAW", "ACCUMULATIONS", "NEW PLAN -
COPIED/TEMPLATE", "NEW PLAN"
Me.ROUTING.Controls("ClaimMonitor").Enabled = True

Case Else
Me.ROUTING.Controls("ClaimMonitor").Enabled = False

End Select

Select Case Me.WorkCategory.Column(1)
Case "RATES/DMR", "COPAY/DAW", "ACCUMULATIONS"
Me.ROUTING.Controls("SecondTester").Enabled = True

Case Else
Me.ROUTING.Controls("SecondTester").Enabled = False

End Select
End Sub
***I have four combo boxes Production Assigned (Productionassignedname), QA
Reviewer Assigned (Q&A), Second Tester Assigned (SecondTester) and Claims
Monitoring Assigned (Claimmonitor). The coding below composes a message
through lotus notes with the names in the combo boxes to different areas of
the email - Email Addresses, the Effective Dates and Datatrak Number, etc it
looks like the following in the body of the email.

Good Day,

Please note: You have been assigned the following WIT entry:
DataTRAK Number - 181818

Effective Date - 6/23/2009 8:00:00 AM

Production Assigned - MICHAEL, MICHAEL
QA Reviewer Assigned - MICHAEL, MICHAEL
Second Tester Assigned - MICHAEL, MICHAEL
Claims Monitoring Assigned - MICHAEL, MICHAEL

Thank You

*** Second Tester and Claims Monitoring are disabled in the data field
properties of the combo boxes, until invoked by the Case Select. With the ?If
Then? I was trying to get the same results as the Production Assigned and QA
Reviewer Assigned with respect to the information in the body of the email
but only if the Second Tester or Claims Monitoring fields are enabled by the
Case Select coding.

But what is happening is: When I select an option from Work Category that
does not enable Second Testing and the Claims Monitoring, the send email will
not execute. If I select the right options to enable the two fields and then
select the information in the drop down (names) then send email will execute
with information similar to the example above. My guess is the way I have
coded the If Then.

Would you have any idea what my solution might be?

Thank you for any assistance.
**************************
Private Sub SendEmail_Click()
Dim SendTo 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
********************************
Call ComposeNotesMemo(SendTo, "Production Assigned and QA Assigned", "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 - " & DLookup("[LastName]", "tblBenefitsEmployee",
"[EmployeeID]= " & Me.Productionassignedname) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.Productionassignedname) & vbCrLf & "QA Reviewer Assigned - " &
DLookup("[LastName]", "tblBenefitsEmployee", "[EmployeeID]= " & Me.Q_A) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.Q_A) & vbCrLf & vbCrLf & _
"Second Tester Assigned - " & DLookup("[LastName]", "tblBenefitsEmployee",
"[EmployeeID]= " & Me.SecondTester) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.SecondTester) & vbCrLf & "Claims Monitoring Assigned - " &
DLookup("[LastName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.ClaimMonitor) _
& ", " & DLookup("[FirstName]", "tblBenefitsEmployee", "[EmployeeID]= " &
Me.ClaimMonitor) & _
vbCrLf & vbCrLf & "Thank You")
End Sub ...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using If then statment 2
Coding message in email 1
Dlookup - To access and send multiple email addresses 3
SendObject Email 2
Select Case and If...Then Statements in UserForm not Calculating 6
Select Case or If Then? 10

Top