Procedure too large

[hiwelcome]

Hello,

I'm looking to clean up some of my code, since I got a "Procedure too large" error. Basically I have a form where a user has a number of choices in combo and text boxes and based on those choices, a specific report is opened. When it was smaller, the code seemed to be fine, but as the choices have increased, the code is getting more unwieldy. Here is a small sample of the code. Can I use variables somehow instead of repeating a bunch of code with different options?

ElseIf Me.ComboDiscipline = "Cast" And Me.ComboState2 = "Alabama" Then

If IsNull(Me.TextDate1) = False And IsNull(Me.TextDate2) = False And IsNull(Me.TextMonth1) = True And IsNull(Me.TextMonth2) = True And Me.ComboReportLevel = "State" Then
DoCmd.OpenReport "RptRollupCastAlabamaState", acViewPreview, , , , OpenArgs:="QryRollupCast_Alabama_State"
ElseIf IsNull(Me.TextDate1) = True And IsNull(Me.TextDate2) = True And IsNull(Me.TextMonth1) = False And IsNull(Me.TextMonth2) = False And Me.ComboReportLevel = "State" Then
DoCmd.OpenReport "RptRollupCastAlabamaState", acViewPreview, , , , OpenArgs:="QryRollupCast_Alabama_State_MFR"

The two TextDates and TextMonths allow the user to pull a report by two different time measures. There are ~10 disciplines, ~17 states, and 4-5 report levels. Many different combinations. I assume there's a better way to do this, so any assistance is appreciated.

 

[pbaldy]

I suspect this will have a lot of facets, but let's start with why are there different reports for each state? Unless they're structurally different, normally I'd have one report and use query criteria or a wherecondition to determine which state's data was pulled. You don't have each state in its own table, do you? Normally that would be a mistake.

 

[hiwelcome]

Good question. There are different reports for each state because each state has different regulations (different wording and different numbers/totals). So I guess the answer is they are structurally different?

 

[pbaldy]

A certain amount of that I might handle with functions that return state-specific info, but you know your data better than I. Noting that the discipline and state are included in the report and query names, could some code be eliminated by getting them from the combos instead of If blocks? Like instead of hard coding "RptRollupCastAlabamaState" you could have

strReportName = "RptRollup" & Me.ComboDiscipline & Me.ComboState2 & "State"

 

[hiwelcome]

Thank you, I will play around with this. I can already see one minor issue I'll occasionally encounter - is there a way to account for differences between the combo box selections and the report names, namely spaces? For example, the combo box lists "New Hampshire", but the report name is "RptRollupCastNewHampshireState" (no space).

 

[pbaldy]

One option is the Replace() function:

Replace(Me.ComboState2, " ", "")

 

[hiwelcome]

Was able to get it working. 10 lines of code sure beats 1000. Thanks so much.

 

[pbaldy]

Happy to help!