By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,963 Members | 1,231 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,963 IT Pros & Developers. It's quick & easy.

How to shorten codes that using if, else and end if?

100+
P: 180
I want to shorten my codes coz i got a compile error: procedure too large. is there a way that these thing can be done in shortest code?

this are my sample codes: i have a hundred of this codes, i only pasted 12 to show you.

Expand|Select|Wrap|Line Numbers
  1. If Form_List.RecordSource = ("calvelo joseph Query") Then
  2. DoCmd.OpenReport "Clearance", acViewDesign, "calvelo joseph Query"
  3. Reports("Clearance").RecordSource = ("calvelo joseph Query")
  4. DoCmd.OpenReport "Clearance", acViewPreview, "calvelo joseph Query"
  5. Else
  6. If Form_List.RecordSource = ("carpio godofredo Query") Then
  7. DoCmd.OpenReport "Clearance", acViewDesign, "carpio godofredo Query"
  8. Reports("Clearance").RecordSource = ("carpio godofredo Query")
  9. DoCmd.OpenReport "Clearance", acViewPreview, "carpio godofredo Query"
  10. Else
  11. If Form_List.RecordSource = ("caseros roberto Query") Then
  12. DoCmd.OpenReport "Clearance", acViewDesign, "caseros roberto Query"
  13. Reports("Clearance").RecordSource = ("caseros roberto Query")
  14. DoCmd.OpenReport "Clearance", acViewPreview, "caseros roberto Query"
  15. Else
  16. If Form_List.RecordSource = ("celajes artemio Query") Then
  17. DoCmd.OpenReport "Clearance", acViewDesign, "celajes artemio Query"
  18. Reports("Clearance").RecordSource = ("celajes artemio Query")
  19. DoCmd.OpenReport "Clearance", acViewPreview, "celajes artemio Query"
  20. Else
  21. If Form_List.RecordSource = ("coloma lino Query") Then
  22. DoCmd.OpenReport "Clearance", acViewDesign, "coloma lino Query"
  23. Reports("Clearance").RecordSource = ("coloma lino Query")
  24. DoCmd.OpenReport "Clearance", acViewPreview, "coloma lino Query"
  25. Else
  26. If Form_List.RecordSource = ("dayao jose Query") Then
  27. DoCmd.OpenReport "Clearance", acViewDesign, "dayao jose Query"
  28. Reports("Clearance").RecordSource = ("dayao jose Query")
  29. DoCmd.OpenReport "Clearance", acViewPreview, "dayao jose Query"
  30. Else
  31. If Form_List.RecordSource = ("De Guzman josian Query") Then
  32. DoCmd.OpenReport "Clearance", acViewDesign, "De Guzman josian Query"
  33. Reports("Clearance").RecordSource = ("De Guzman josian Query")
  34. DoCmd.OpenReport "Clearance", acViewPreview, "De Guzman josian Query"
  35. Else
  36. If Form_List.RecordSource = ("De Guzman vernadette Query") Then
  37. DoCmd.OpenReport "Clearance", acViewDesign, "De Guzman vernadette Query"
  38. Reports("Clearance").RecordSource = ("De Guzman vernadette Query")
  39. DoCmd.OpenReport "Clearance", acViewPreview, "De Guzman vernadette Query"
  40. Else
  41. If Form_List.RecordSource = ("Dela Cruz helen Query") Then
  42. DoCmd.OpenReport "Clearance", acViewDesign, "Dela Cruz helen Query"
  43. Reports("Clearance").RecordSource = ("Dela Cruz helen Query")
  44. DoCmd.OpenReport "Clearance", acViewPreview, "Dela Cruz helen Query"
  45. Else
  46. If Form_List.RecordSource = ("donsol elvira Query") Then
  47. DoCmd.OpenReport "Clearance", acViewDesign, "donsol elvira Query"
  48. Reports("Clearance").RecordSource = ("donsol elvira Query")
  49. DoCmd.OpenReport "Clearance", acViewPreview, "donsol elvira Query"
  50. Else
  51. If Form_List.RecordSource = ("Dorosan jan christopher Query") Then
  52. DoCmd.OpenReport "Clearance", acViewDesign, "Dorosan jan christopher Query"
  53. Reports("Clearance").RecordSource = ("Dorosan jan christopher Query")
  54. DoCmd.OpenReport "Clearance", acViewPreview, "Dorosan jan christopher Query"
  55. end if
  56. end if
  57. end if
  58. end if
  59. end if
  60. end if
  61. end if
  62. end if
  63. end if
  64. end if
  65. end if
  66. end if
  67. end sub
Dec 21 '11 #1

✓ answered by TheSmileyCoder

Well there is alot to say about the code you have shown.

First of, from what I see, it seems your if/then/else statements in the shown code are superflous, you could simply write it as:

Expand|Select|Wrap|Line Numbers
  1. Dim strSource as String
  2. strSource=Form_List.RecordSource
  3. DoCmd.OpenReport "Clearance", acViewDesign, strSource
  4. Reports("Clearance").RecordSource = strSource
  5. DoCmd.OpenReport "Clearance", acViewPreview, strSource
  6.  
Secondly, if you have a limited number of statements you can use the elseIF instead of nested If's.
Expand|Select|Wrap|Line Numbers
  1. If X=0 then
  2.   Msgbox "Divide by zero"
  3. elseIf X=2 Then
  4.   Msgbox X & " is invalid"
  5. else
  6.   Msgbox X
  7. end if
Now the above is actually something you should have been able to find out yourself, by simply placing the cursor on the if keyword, and pressing F1, and you would get a nice explanation.

The third option, is the SELECT CASE statement, again, look at the help file for info, but here is an example:
Expand|Select|Wrap|Line Numbers
  1. Public Sub DoAction(strAction as string) 
  2.   Select Case strAction
  3.     Case "Open"
  4.       DoCmd.OpenForm "frm_Example"
  5.     Case "Close"
  6.       Docmd.Close acForm, "frm_Example"
  7.     case else
  8.       MsgBox "An invalid argument was passed to the DoAction command"
  9.   End Select
  10. End Sub

Share this Question
Share on Google+
10 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Well there is alot to say about the code you have shown.

First of, from what I see, it seems your if/then/else statements in the shown code are superflous, you could simply write it as:

Expand|Select|Wrap|Line Numbers
  1. Dim strSource as String
  2. strSource=Form_List.RecordSource
  3. DoCmd.OpenReport "Clearance", acViewDesign, strSource
  4. Reports("Clearance").RecordSource = strSource
  5. DoCmd.OpenReport "Clearance", acViewPreview, strSource
  6.  
Secondly, if you have a limited number of statements you can use the elseIF instead of nested If's.
Expand|Select|Wrap|Line Numbers
  1. If X=0 then
  2.   Msgbox "Divide by zero"
  3. elseIf X=2 Then
  4.   Msgbox X & " is invalid"
  5. else
  6.   Msgbox X
  7. end if
Now the above is actually something you should have been able to find out yourself, by simply placing the cursor on the if keyword, and pressing F1, and you would get a nice explanation.

The third option, is the SELECT CASE statement, again, look at the help file for info, but here is an example:
Expand|Select|Wrap|Line Numbers
  1. Public Sub DoAction(strAction as string) 
  2.   Select Case strAction
  3.     Case "Open"
  4.       DoCmd.OpenForm "frm_Example"
  5.     Case "Close"
  6.       Docmd.Close acForm, "frm_Example"
  7.     case else
  8.       MsgBox "An invalid argument was passed to the DoAction command"
  9.   End Select
  10. End Sub
Dec 21 '11 #2

100+
P: 759
Of corse the first piece of code from Smiley can replace all your code.
But may I ask you why you first open your report in design view ?
This line is not necessary in order to view your report:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport "Clearance", acViewDesign, strSource
So you can remove it making the code even shorter:
Expand|Select|Wrap|Line Numbers
  1. Dim strSource as String
  2. strSource=Form_List.RecordSource
  3. Reports("Clearance").RecordSource = strSource
  4. DoCmd.OpenReport "Clearance", acViewPreview, strSource
Dec 22 '11 #3

ADezii
Expert 5K+
P: 8,679
I think that the Main point that we are missing here is that, in order to dynamically change the Record Source of a Report, we should:
  1. Open the Report in Design Mode and Hidden
  2. Modify the Record Source of the Report
  3. Close the Report, Saving the changes
  4. Open the Report with the modified Record Source
    Expand|Select|Wrap|Line Numbers
    1. Dim strReportName As String
    2.  
    3. strReportName = "rptEmployees"
    4.  
    5. With DoCmd
    6.   .OpenReport strReportName, acViewDesign, , , acHidden
    7.    Reports(strReportName).RecordSource = "qryEmployees"
    8.   .Close acReport, strReportName, acSaveYes
    9.   .OpenReport strReportName, acViewPreview, , , acWindowNormal
    10. End With
P.S. - A Report does not become a Member of the Reports Collection unless it is Opened in some manner.
Dec 22 '11 #4

100+
P: 180
Thanks for your reply, The code below is where i used strSource, Is this the right application of your given codes TheSmileCoder? If it is correct, it's the same with my codes. If I'm wrong, how can i use your given codes?

Private Sub TxtClearance_Click()
Dim strSource As String
strSource = Form_List.RecordSource


If strSource = ("juan reynulfo Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "juan reynulfo Query"
Reports("Clearance").RecordSource = ("juan reynulfo Query")
DoCmd.OpenReport "Clearance", acViewPreview, "juan reynulfo Query"
Else
If strSource = ("lacsamana elizabeth Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "lacsamana elizabeth query"
Reports("Clearance").RecordSource = ("lacsamana elizabeth query")
DoCmd.OpenReport "Clearance", acViewPreview, "lacsamana elizabeth query"

End If
End If
End Sub
Dec 26 '11 #5

100+
P: 759
No, eneyardi.
Smiley's code has NOT IFs statements.
You only need to add line 1 and 7 around Smiley's code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtClearance_Click()
  2.     Dim strSource as String
  3.     strSource=Form_List.RecordSource
  4.     DoCmd.OpenReport "Clearance", acViewDesign, strSource
  5.     Reports("Clearance").RecordSource = strSource
  6.     DoCmd.OpenReport "Clearance", acViewPreview, strSource
  7. End Sub
The Smiley's five lines will do ALL your job. This five lines are EQUAL to yours 67 lines from your topic post.

Of course, Smiley assume, as a rule, that ALL your queries has the same name as the attached reports.
Example: Your query is named juan reynulfo Query and the attached report is also named juan reynulfo Query.
As long as you follow this "rule" you can add as many queries/reports as you wish and Smiley's five lines will do the job.
Dec 26 '11 #6

100+
P: 180
How can i apply that five line of smiley without using if, if i want to call other query?
Dec 27 '11 #7

100+
P: 180
This is what i need to do in my access program. I have main form name (home), in that form i have a combolist which row source contains 500 names of employees, i have another form which form name is (list). this form will show up when i click one of the name of employee in combolist and its recordsource is based on the name of employee i clicked. For example when i click juan reynulfo, the form list will show up and its recordsource will be juan reynulfo query. In form list there is a command button (print), when i click that print button, report will show up which name is (clearance) and its recordsource is juan reynulfo query. If i click other employee, how can i apply smiley codes replacing my codes without using if as you said?
Dec 27 '11 #8

100+
P: 180
thanks alot guys, thank you smile, thank you mihail, thank you Adezii. it works!
Dec 27 '11 #9

100+
P: 759
At last ! You understand the Smiley's logic.

More than that, lets see if this presumes are really:
I am almost sure that all your queries are identical. With a slight difference in the criteria: where you have different names.
And you wish to design as many queries as different names you have (500).

If this is the situation, what about if you wish to use your database for the US army's employes or for the China's peoples ?

To do all the job you need only one query. And to pass to the criteria row the correct name before you use the query.
More: If you use only one query is no more need to dynamically change the row source for your form and report. You can set up the row source at design time (to point to that query).

Let us know if this is your situation.

Good luck !
Dec 27 '11 #10

100+
P: 180
yup, that's my situation, i don't know that theres a way to use only one query. I will post my next question later regarding that.
Once again thank you very much!
Dec 27 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.