Using IF/ELSEIF in VBA

[MP220591]

Hello!

Hopefully this should be a very easy one to solve. I'm trying to do an If code to open a form or report, depending what option has been selected in a combo box on a modal dialogue box. I am putting the VBA on a button that say's go, so they select their report, hit Go, and I want it to then open up the report for them. However I'm getting an error that says 'Compile Error - Else without If', I'm not sure what is wrong with my If Statement (I've used them successfully elsewhere to display checklists for example depending on what is selected in a combo box).

Private Sub Command1_Click()
If Me.ReportCombo = "Team Daily Task List" Then DoCmd.OpenReport "Daily To Do List"
ElseIf Me.ReportCombo = "Team Current Active Cases" Then DoCmd.OpenReport "Current Active Cases Team"
ElseIf Me.ReportCombo = "HD Active Cases" Then DoCmd.OpenReport "HD Active Cases"
ElseIf Me.ReportCombo = "Monthly Broker Case Allocation" Then DoCmd.OpenReport "Monthly Broker Case Allocation"
ElseIf Me.ReportCombo = "Monthly Team EOI Referrals" Then DoCmd.OpenReport "Monthly EOI Referrals"
ElseIf Me.ReportCombo = "Monthly Team Full Tender Referrals" Then DoCmd.OpenReport "Monthly Full Tender Referrals"
ElseIf Me.ReportCombo = "Monthly All Tendered Case Referrals" Then DoCmd.OpenReport "Monthly Tendered Case Referrals"
ElseIf Me.ReportCombo = "Test of Change" Then DoCmd.OpenReport "Test of Change Data"
ElseIf Me.ReportCombo = "Team Weekly Task Last" Then DoCmd.OpenReport "Weekly To Do List"
ElseIf Me.ReportCombo = "Broker Case Close Details" Then DoCmd.OpenForm "Broker Case Closure Details"
ElseIf Me.ReportCombo = "CCG EOI Completed Cases" Then DoCmd.OpenReport "Broker CCG EOI Completed Cases"
ElseIf Me.ReportCombo = "CCG Completed Full Tenders" Then DoCmd.OpenReport "Broker CCG Full tender Completed Cases"
ElseIf Me.ReportCombo = "DCC EOI Completed Cases" Then DoCmd.OpenReport "Broker DCC EOI Tender Completed Cases"
ElseIf Me.ReportCombo = "DCC Completed Full Tenders" Then DoCmd.OpenReport "Broker DCC Full Tender Completed Cases"
End If
End Sub

Hopefully it's something super simple that I've missed and I'll be able to know for next time.

Thank you!

 

[Gasman]

I think you would be better off, having another field with the report name in it, then you can simplify your code to

DoCmd.Openreport Me.ReportCombo.Column(x) where is the relevant column?
or something along those lines?

What happens when a new report is created?, you'd have to change the code each time?

 

[The_Doc_Man]

There are a gazillion ways to skin this cat but the easiest (conceptually) might be:

SELECT CASE Me.ReportCombo
    CASE "Team Daily Task List"
        DoCmd.OpenReport "Daily To Do List"
    CASE "Team Current Active Cases"
        DoCmd.OpenReport "Current Active Cases Team"
    CASE "HD Active Case" .

{etc. etc.}
    CASE ELSE
      MsgBox "No Report Selected"
END SELECT

Just as a simple suggestion and not a criticism, you should avoid formal object names (in this case, reports) with spaces in the name, since you will forever have to refer to them in quoted or bracketed strings. What you have WILL work, but you could shorten those names to something more abbreviated and do less typing in the future. The name of the formal object has nothing to do with the report title that gets displayed when you open it, so your users would see nothing special.

 

[Frothingslosh]

Private Sub Command1_Click()
If Me.ReportCombo = "Team Daily Task List" Then DoCmd.OpenReport "Daily To Do List"
ElseIf Me.ReportCombo = "Team Current Active Cases" Then DoCmd.OpenReport "Current Active Cases Team"
ElseIf Me.ReportCombo = "HD Active Cases" Then DoCmd.OpenReport "HD Active Cases"
ElseIf Me.ReportCombo = "Monthly Broker Case Allocation" Then DoCmd.OpenReport "Monthly Broker Case Allocation"
ElseIf Me.ReportCombo = "Monthly Team EOI Referrals" Then DoCmd.OpenReport "Monthly EOI Referrals"
ElseIf Me.ReportCombo = "Monthly Team Full Tender Referrals" Then DoCmd.OpenReport "Monthly Full Tender Referrals"
ElseIf Me.ReportCombo = "Monthly All Tendered Case Referrals" Then DoCmd.OpenReport "Monthly Tendered Case Referrals"
ElseIf Me.ReportCombo = "Test of Change" Then DoCmd.OpenReport "Test of Change Data"
ElseIf Me.ReportCombo = "Team Weekly Task Last" Then DoCmd.OpenReport "Weekly To Do List"
ElseIf Me.ReportCombo = "Broker Case Close Details" Then DoCmd.OpenForm "Broker Case Closure Details"
ElseIf Me.ReportCombo = "CCG EOI Completed Cases" Then DoCmd.OpenReport "Broker CCG EOI Completed Cases"
ElseIf Me.ReportCombo = "CCG Completed Full Tenders" Then DoCmd.OpenReport "Broker CCG Full tender Completed Cases"
ElseIf Me.ReportCombo = "DCC EOI Completed Cases" Then DoCmd.OpenReport "Broker DCC EOI Tender Completed Cases"
ElseIf Me.ReportCombo = "DCC Completed Full Tenders" Then DoCmd.OpenReport "Broker DCC Full Tender Completed Cases"
End If
End Sub

First, please use code tags when posting code - it preserves your formatting and makes things MUCH easier to read.

Second, I've highlighted the error in red. Basically, by putting the first THEN condition on the same line as If...Then, you're telling the compiler that it's a single-line IF statement. So then it moves to the next line and freaks out, because it can't find a matching IF.

In fact, every single line is going to generate that error.

To fix it, just place 'DoCmd.OpenReport "Daily To Do List"' on its own line between the IF line and the first ElseIf line. Do the same for each other result.

A personal suggestion would be to replace this whole thing with SELECT..CASE, since you're simply one item for one of any number of meanings. I've always found the Select/Case format a bit easier to read and follow than a sequence of If/Thens, although you DO have to use the latter when you're doing a series of DIFFERENT comparisons.

 

[Minty]

+ Vote for Gasman's suggestion. Add the report name to the combo from a table or query and simply open the hidden column. No need to recode after adding more reports.

 

[MP220591]

Brilliant thank you for your advice everyone, will get onto it... Knew it would be something straightforward! I'm self taught so learning as I go