Too Many ElseIfs?

[ctpoodle]

Some of my code is working but the bottom several ElseIfs are not. Is there a limit to the number of ElseIfs you can use? Here is the code. Thanks in advance for any advice. I'm not a pro as you can see.

Function mcr_Produce_Issue_Escalation_Template_Templates1()
On Error GoTo mcr_Produce_Issue_Escalation_Template_Templates1_Err
DoCmd.GoToControl "EscalationId"
DoCmd.RunCommand acCmdSaveRecord
Beep
MsgBox "The Issue Escalation Template will be saved to your U: drive and will open automatically.", vbOKOnly, ""
If (Forms![Issue Escalation Screen]!TemplateCode Like "Ops Consultant") Then
DoCmd.OpenReport "Escalation Issue Template - Ops Consultant", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - Ops Consultant", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - Ops Consultant"
Else
If (Forms![Issue Escalation Screen]!TemplateCode Like "SCM supplier*") Then
DoCmd.OpenReport "Escalation Issue Template - SCM Supplier", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - SCM Supplier", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - SCM Supplier"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "AM Director") Then
DoCmd.OpenReport "Escalation Issue Template - AM Director", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - AM Director", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - AM Director"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "AM Manager") Then
DoCmd.OpenReport "Escalation Issue Template - AM Manager", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - AM Manager", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - AM Manager"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "Ops Director") Then
DoCmd.OpenReport "Escalation Issue Template - Ops Director", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - Ops Director", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - Ops Director"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "Ops Manager") Then
DoCmd.OpenReport "Escalation Issue Template - Ops Manager", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - Ops Manager", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - Ops Manager"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "SCM Manager") Then
DoCmd.OpenReport "Escalation Issue Template - SCM Manager", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - SCM Manager", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - SCM Manager"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "SCM Senior") Then
DoCmd.OpenReport "Escalation Issue Template - SCM Senior", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - SCM Senior", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - SCM Senior"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "SCM Director") Then
DoCmd.OpenReport "Escalation Issue Template - SCM Director", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - SCM Director", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - SCM Director"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "Generic Mgr") Then
DoCmd.OpenReport "Escalation Issue Template - Generic Manager", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - Generic Manager", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - Generic Manager"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "Generic") Then
DoCmd.OpenReport "Escalation Issue Template - Generic", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - Generic", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - Generic"
ElseIf (Forms![Issue Escalation Screen]![Template Code] Like "Generic Dir") Then
DoCmd.OpenReport "Escalation Issue Template - Generic Director", acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - Generic Director", "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - Generic Director"
End If
End If
mcr_Produce_Issue_Escalation_Template_Templates1_Exit:
Exit Function
mcr_Produce_Issue_Escalation_Template_Templates1_Err:
MsgBox Error$
Resume mcr_Produce_Issue_Escalation_Template_Templates1_Exit
End Function

 

[NauticalGent]

Word of advice: Code tags. I see this mess and I don’t even want to start. However, I will suggest looking into Select Case statements instead of Else If. Easier to read and maintain.

Again: Code Tags!

 

[ctpoodle]

Sorry! Forgot about the code tags! I haven't used Select Case statements but I'll look into them. Thanks.

 

[plog]

No, there is no limit to the amount of ElseIfs you can use. Now here's the advice--one variable can reduce that function by at least 50 lines of code.

Create a Type variable which holds the input from the form:

TempCode = Forms![Issue Escalation Screen]!TemplateCode

Then take all those blocks of code and have them use that variable instead of repeating each block for each new type:

DoCmd.OpenReport "Escalation Issue Template - " & TempCode, acViewReport, "", "", acNormal
DoCmd.OutputTo acOutputReport, "Escalation Issue Template - " & TempCode, "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
DoCmd.Close acReport, "Escalation Issue Template - " & TempCode"

No more if/elses. That 1 block of code genericized for the TempCode now replaces your 15 or so blocks of code.

 

[JHB]

What is the problem/error?
I just put the function in a module, no problem at all!
Actually you function doesn't return anything, so you could use a Sub instead.

 

[ctpoodle]

Thank you so much plog! Yes, that is so much more efficient!

Interestingly, I'm still having issues with the same ones I was having issues with before but now that tells me the problem lies somewhere other than the code here.

Thank you so much for your help!

 

[MajP]

You have a ton of replication, in that you do the same activities based on whatever choice. I got it down to a few lines of code

Dim TempCode as string
'get rid of nulls and trailing spaces
TempCode = trim(Forms![Issue Escalation Screen]!TemplateCode & "")
Select Case TempCode
  case "Ops Consultant", "SCM Supplier", "AM Director", "AM Manager", "Ops Director", "Ops Manager", "SCM Manager", "SCM Senior", _
       "SCM Director", "Generic Mgr","Generic", "Generic Dir"
       OpenOutPutClose "Escalation Issue Template - " & TempCode
  case "If you had other cases"
end select

If however there are no other choices then the code becomes

Dim TempCode as string
'get rid of nulls and trailing spaces
TempCode = trim(Forms![Issue Escalation Screen]!TemplateCode & "")
If Tempcode <> "" then OpenOutPutClose "Escalation Issue Template - " & TempCode

Helper procedures to make it more readable. Pass in a name and it opens outputs and then closes the report.

Public Sub OpenEsc(ReportName as string)
  DoCmd.OpenReport ReportName, acViewReport, "", "", acNormal
end sub

Public Sub OutPutEsc(ReportName as string)
  DoCmd.OutputTo acOutputReport, ReportName, "RichTextFormat(*.rtf)", "U:\Escalation Issue Template.rtf", True, "", , acExportQualityScreen
end sub

Public Sub OpenOutputClose(reportName)
  OpenEsc ReportName
  OutPutEsc ReportName
  docmd.close acreport, ReportName
end sub