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

[eneyardi]

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.

If Form_List.RecordSource = ("calvelo joseph Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "calvelo joseph Query"
Reports("Clearance").RecordSource = ("calvelo joseph Query")
DoCmd.OpenReport "Clearance", acViewPreview, "calvelo joseph Query"
Else
If Form_List.RecordSource = ("carpio godofredo Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "carpio godofredo Query"
Reports("Clearance").RecordSource = ("carpio godofredo Query")
DoCmd.OpenReport "Clearance", acViewPreview, "carpio godofredo Query"
Else
If Form_List.RecordSource = ("caseros roberto Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "caseros roberto Query"
Reports("Clearance").RecordSource = ("caseros roberto Query")
DoCmd.OpenReport "Clearance", acViewPreview, "caseros roberto Query"
Else
If Form_List.RecordSource = ("celajes artemio Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "celajes artemio Query"
Reports("Clearance").RecordSource = ("celajes artemio Query")
DoCmd.OpenReport "Clearance", acViewPreview, "celajes artemio Query"
Else
If Form_List.RecordSource = ("coloma lino Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "coloma lino Query"
Reports("Clearance").RecordSource = ("coloma lino Query")
DoCmd.OpenReport "Clearance", acViewPreview, "coloma lino Query"
Else
If Form_List.RecordSource = ("dayao jose Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "dayao jose Query"
Reports("Clearance").RecordSource = ("dayao jose Query")
DoCmd.OpenReport "Clearance", acViewPreview, "dayao jose Query"
Else
If Form_List.RecordSource = ("De Guzman josian Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "De Guzman josian Query"
Reports("Clearance").RecordSource = ("De Guzman josian Query")
DoCmd.OpenReport "Clearance", acViewPreview, "De Guzman josian Query"
Else
If Form_List.RecordSource = ("De Guzman vernadette Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "De Guzman vernadette Query"
Reports("Clearance").RecordSource = ("De Guzman vernadette Query")
DoCmd.OpenReport "Clearance", acViewPreview, "De Guzman vernadette Query"
Else
If Form_List.RecordSource = ("Dela Cruz helen Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "Dela Cruz helen Query"
Reports("Clearance").RecordSource = ("Dela Cruz helen Query")
DoCmd.OpenReport "Clearance", acViewPreview, "Dela Cruz helen Query"
Else
If Form_List.RecordSource = ("donsol elvira Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "donsol elvira Query"
Reports("Clearance").RecordSource = ("donsol elvira Query")
DoCmd.OpenReport "Clearance", acViewPreview, "donsol elvira Query"
Else
If Form_List.RecordSource = ("Dorosan jan christopher Query") Then
DoCmd.OpenReport "Clearance", acViewDesign, "Dorosan jan christopher Query"
Reports("Clearance").RecordSource = ("Dorosan jan christopher Query")
DoCmd.OpenReport "Clearance", acViewPreview, "Dorosan jan christopher Query"
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end if
end sub

[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:

Dim strSource as String
strSource=Form_List.RecordSource
DoCmd.OpenReport "Clearance", acViewDesign, strSource
Reports("Clearance").RecordSource = strSource
DoCmd.OpenReport "Clearance", acViewPreview, strSource
 

Secondly, if you have a limited number of statements you can use the elseIF instead of nested If's.

If X=0 then
  Msgbox "Divide by zero"
elseIf X=2 Then
  Msgbox X & " is invalid"
else
  Msgbox X
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:

Public Sub DoAction(strAction as string) 
  Select Case strAction
    Case "Open"
      DoCmd.OpenForm "frm_Example"
    Case "Close"
      Docmd.Close acForm, "frm_Example"
    case else
      MsgBox "An invalid argument was passed to the DoAction command"
  End Select
End Sub

[Mihail]

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:

DoCmd.OpenReport "Clearance", acViewDesign, strSource

So you can remove it making the code even shorter:

Dim strSource as String
strSource=Form_List.RecordSource
Reports("Clearance").RecordSource = strSource
DoCmd.OpenReport "Clearance", acViewPreview, strSource

[ADezii]

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
   

   Dim strReportName As String
    
   strReportName = "rptEmployees"
    
   With DoCmd
     .OpenReport strReportName, acViewDesign, , , acHidden
      Reports(strReportName).RecordSource = "qryEmployees"
     .Close acReport, strReportName, acSaveYes
     .OpenReport strReportName, acViewPreview, , , acWindowNormal
   End With

P.S. - A Report does not become a Member of the Reports Collection unless it is Opened in some manner.

[eneyardi]

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

[Mihail]

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

Private Sub TxtClearance_Click()
    Dim strSource as String
    strSource=Form_List.RecordSource
    DoCmd.OpenReport "Clearance", acViewDesign, strSource
    Reports("Clearance").RecordSource = strSource
    DoCmd.OpenReport "Clearance", acViewPreview, strSource
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.

[eneyardi]

How can i apply that five line of smiley without using if, if i want to call other query?

[eneyardi]

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?

[eneyardi]

thanks alot guys, thank you smile, thank you mihail, thank you Adezii. it works!

[Mihail]

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 !

[eneyardi]

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!