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

Use Combo box to open reports

P: 12
I am working on a Combo box function that could open a list of reports. The reports' names are saved in a seperate table. The problem I had is that when I choose one of the report from my combo box, nothing pups up... I did a little research on that before and a used a "Case" commend.. could someone help me please

My code is the following:
Expand|Select|Wrap|Line Numbers
  1.  Private Sub cmdOpenReport_Click()
  2.     ' Purpose:  Opens the report selected in the list box.
  3.     On Error GoTo cmdOpenReport_ClickErr
  4.     If Not IsNull(Me.List63) Then
  5.         DoCmd.OpenReport Me.List63, IIf(Me.chkPreview.Value, acViewPreview, acViewNormal)
  6.     End If
  7.     Exit Sub
  8.  
  9. cmdOpenReport_ClickErr:
  10.     Select Case Err.Number
  11.     Case 2501   ' Cancelled by user, or by NoData event.
  12.         MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
  13.     Case Else
  14.         MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
  15.     End Select
  16.     Resume Next
  17. End Sub
  18.  
  19.  
  20. Private Sub List63_AfterUpdate()
  21. Select Case Me![List63]
  22. Case Is = "Modification Monitoring Report"
  23. DoCmd.OpenReport "Modification Monitoring Report", acViewPreview, "", "", acNormal
  24. Case Is = "Special Servicing Loan Performance Report"
  25. DoCmd.OpenReport "Special Servicing Loan Performance Report", acViewPreview, "", "", acNormal
  26. Case Is = "SS Financial Analysis Report"
  27. DoCmd.OpenReport "SS Financial Analysis Report", acViewPreview, "", "", acNormal
  28. End Select
  29. End Sub
Jan 4 '12 #1

✓ answered by Stewart Ross

In your List63_Afterupdate sub your case looks OK, though it has some superfluous elements which I've removed below:

Expand|Select|Wrap|Line Numbers
  1. Case "Modification Monitoring Report"
  2.  DoCmd.OpenReport "Modification Monitoring Report", acViewPreview, , , acNormal
  3.  Case "Special Servicing Loan Performance Report"
  4.  DoCmd.OpenReport "Special Servicing Loan Performance Report", acViewPreview, , , acNormal
  5.  Case "SS Financial Analysis Report"
  6.  DoCmd.OpenReport "SS Financial Analysis Report", acViewPreview, , , acNormal
However, there is no need to use the select case at all. As you had in the other sub, simply use:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport Me.List63, IIf(Me.chkPreview.Value, acViewPreview, acViewNormal)
I suggest that you try compiling your module before running it - syntax errors in one sub in that module will stop the VBA interpreter from compiling and running other subs in that module until the errors have been corrected.

-Stewart

Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
In your List63_Afterupdate sub your case looks OK, though it has some superfluous elements which I've removed below:

Expand|Select|Wrap|Line Numbers
  1. Case "Modification Monitoring Report"
  2.  DoCmd.OpenReport "Modification Monitoring Report", acViewPreview, , , acNormal
  3.  Case "Special Servicing Loan Performance Report"
  4.  DoCmd.OpenReport "Special Servicing Loan Performance Report", acViewPreview, , , acNormal
  5.  Case "SS Financial Analysis Report"
  6.  DoCmd.OpenReport "SS Financial Analysis Report", acViewPreview, , , acNormal
However, there is no need to use the select case at all. As you had in the other sub, simply use:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport Me.List63, IIf(Me.chkPreview.Value, acViewPreview, acViewNormal)
I suggest that you try compiling your module before running it - syntax errors in one sub in that module will stop the VBA interpreter from compiling and running other subs in that module until the errors have been corrected.

-Stewart
Jan 4 '12 #2

P: 12
Thanks Stewart! it works well now. Really appreciate it !!
Jan 9 '12 #3

NeoPa
Expert Mod 15k+
P: 31,314
You may want to select post #2 as Best Answer Annabelle as you seem to have resolved your problem with it.
Jan 10 '12 #4

Post your reply

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