Access msgbox Yes No to Choose Report - Opening Wrong Report?

Question

I set up the following simple code, to choose if the report should show "excluded" groups or not.

 Private Sub cmdRptWorktypesByGroups_Click()
     Dim rptExclYN As String

     rptExclYN = MsgBox("Would you like this report with excluded groups (Yes)" & vbclrf & " or without excluded practice groups (No)?", vbYesNo, "Choose Report With or Without excluded groups")

     If rptExclYN = Yes Then
         DoCmd.OpenReport "rptWorkTypebyGroups", acViewReport
     Else
         DoCmd.OpenReport "rptWorkTypebyGroupsNoExcl", acViewReport
     End If

 End Sub

That is my code. It should be totally straight forward. Problem is, that when the user clicks Yes, it opens the rptWorkTypebyGroupsNoExcl report. When I click No, it still opens that same report. Can anyone tell what I might be doing wrong?

I originally had the rptWorkTypebyGroups report, with a query record source. I copied that report, renamed the copy as rptWorkTypebyGroupsNoExcl, and saved it's Record Source query as it's own name. I can't tell why the message box isn't opening the right report. Can anyone please help?

Thank!

Answer 1

The MsgBox() function returns a VbMsgBoxResult value (an Integer), not a string.

Since you apparently don't have Option Explicit on, Yes is created as new empty Variant, so your If condition will always be false, no matter what you selected.

Put Option Explicit at the top of each module.
It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time. To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor.

Correct code:

Private Sub cmdRptWorktypesByGroups_Click()

     Dim rptExclYN As VbMsgBoxResult 

     rptExclYN = MsgBox("Would you like this report with excluded groups (Yes)" & vbCrLf & " or without excluded practice groups (No)?", vbYesNo, "Choose Report With or Without excluded groups")

     If rptExclYN = vbYes Then
         DoCmd.OpenReport "rptWorkTypebyGroups", acViewReport
     Else
         DoCmd.OpenReport "rptWorkTypebyGroupsNoExcl", acViewReport
     End If

End Sub

Edit: It's vbCrLf, not vbclrf.
Another one that Option Explicit would have caught.