Syntax help - cleaning up my code

[lordhaworth]

I currently have some VBA code which consists of a series of IF statements that receive the choice from an input box and use this choice to filter a report so that only the appropriate report entries are displayed.
What I am looking to do now is just make my code more manageable for future users, for at the moment there are quite a lot of these IF statements across various forms.

Basically what I want to do is go from this:

IF DropBoxChoice = "Choice" THEN
DoCmd.OpenReport ReportDocName, acPreview,, Fieldname Like '*Choice*'" (plus a few other filters)
END IF

Many many times...

To something more like this (obviously this doesnt work)

DoCmd.OpenReport ReportDocName, acPreview, Fieldname Like DropBoxChoice

i.e. cutting out loads of IF statements and making the coding practice much cleaner!

Obviously this is not a vital change, the system runs fine. It will just be useful to future users to not have to cope with a gzillion lines of code.

Thanks very much for any suggestions on how to I can go about tidying up this code

Cheers

[si_the_geek]

That should be possible, but which method(s) would be best depends on the kind of things you are doing.

Could you show us a larger section of code?

[lordhaworth]

Yep sure this is what I have at the minute

Private Sub DropDownList1_AfterUpdate()
Dim stDocName As String
Dim stQuDocName As String

If DropDownList1 = "Commercial - London" Then
stDocName = "rpt_By_Business_Area"
DoCmd.OpenReport stDocName, acPreview, , "[Commercial - London] = " & True & " AND Team LIKE '*Infrastructure Support*' And BusinessName LIKE '*Commercial - London*'"

If DropDownList1 = "Commercial - North" Then
stDocName = "rpt_By_Business_Area"
DoCmd.OpenReport stDocName, acPreview, , "[Commercial - North]= " & True & " AND Team LIKE '*Infrastructure Support*' And BusinessName LIKE '*Commercial - North*'"
End If


Only on a bit of a bigger scale

[si_the_geek]

If stDocName is always the same, and it is always the same structure for the last parameter, you can do it with simple string building like this:

Private Sub DropDownList1_AfterUpdate()
Dim stDocName As String
Dim stQuDocName As String

   stDocName = "rpt_By_Business_Area"
   DoCmd.OpenReport stDocName, acPreview, , "[" & DropDownList1 & "] = " & True & " AND Team LIKE '*Infrastructure Support*' And BusinessName LIKE '*" & DropDownList1 & "*'"

If however you have a group of items that work one way, but another group (or multiple) that work another way, you can add a Select Case (which is basically multiple If statements that check the same value), eg:

Private Sub DropDownList1_AfterUpdate()
Dim stDocName As String
 Dim stQuDocName As String

  Select Case DropDownList1 
  Case "Commercial - London", "Commercial - North"
     stDocName = "rpt_By_Business_Area"
     DoCmd.OpenReport stDocName, acPreview, , "[" & DropDownList1 & "] = " & True & " AND Team LIKE '*Infrastructure Support*' And BusinessName LIKE '*" & DropDownList1 & "*'"

  Case "Another option"
     stDocName = "rpt_Fake"
     DoCmd.OpenReport stDocName, acPreview, , "Team LIKE '*Infrastructure Support*'"

  Case Else
     stDocName = "rpt_Anything"
     DoCmd.OpenReport stDocName, acPreview, , "Team LIKE '*Infrastructure Support*'"

   End Select

There are probably several other ways that things could be shortened - if you want to see how, show us the code you end up with after implementing this.

[lordhaworth]

I've just quickly checked, and i think it will work
Thanks this will drastically change the long term appearance of the database code