Results 1 to 12 of 12
  1. #1
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108

    Open report dialog button error management

    Hello,



    I have a sub with the following code that opens a report based on the report name entered by the user, it is activated by a Call command on a button

    Code:
    Private Sub ReportName()'Set variable report name, box prompt to open report by name manually
    
    
    Dim Message, Title, Default, ReportName
    Message = "Enter the Report Name from the list"    ' Set prompt.
    Title = "Report Name?"    ' Set title.
    Default = "rpt"    ' Set default.
    ' Display message, title, ,default value and box position x,y.
    ReportName = InputBox(Message, Title, Default, 11500, 3000)
    
    
    DoCmd.OpenReport ReportName, acViewReport
    
    
    End Sub
    It works fine but I would like to refine a couple points
    #1 if the user enters a report name that does not exist it brakes and opens a debug window, instead I would like it to open an error message such "the requested report does not exist", close

    #2 all my reports are like "rptREPORT", that's why de default value is "rpt" but I would like the used to be able to enter "report" instead of "rptreport" or better something like "rep*" so if the report name is for example "rptReportforwhateversales" I'd like the user to enter "sales" which would open rptReportforwhateversales, assuming no other report has the same sales string in it, if it does prompt something like "multiple possibilities, please refine".

    thank you

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    #1 and #2 conflict. If the report name does not exist, and #1 handles that, then #2 is never given a chance.
    This will take care of #1.
    Code:
    Public Sub ReportName() 'Set variable report name, box prompt to open report by name manually
        On Error GoTo ReportName_Error
        Dim Message, Title, Default, ReportName
        Message = "Enter the Report Name from the list"    ' Set prompt.
        Title = "Report Name?"    ' Set title.
        Default = "rpt"    ' Set default.
        ' Display message, title, ,default value and box position x,y.
        ReportName = InputBox(Message, Title, Default, 11500, 3000)
        DoCmd.OpenReport ReportName, acViewReport
    ReportName_EXIT:
        Exit Sub
    ReportName_Error:
        Select Case Err
            Case 2103
                 MsgBox "No such report exists"
           Case Else
                MsgBox Err.Number & ", " & Err.Description & " in Procedure ReportName"
        End Select
        Resume ReportName_EXIT
    End Sub

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    what you ought to be using is a combo displaying a list of table records with report name (or alias and report name) and let user choose. Less typing, no spelling issues, no checking for wrong report name.
    Last edited by Micron; 05-31-2019 at 04:19 PM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Absolutely agree with Micron...it's better, when possible, to prevent errors rather than having to check for errors and warn the users, when they're found!

    To load the Combobox with all Reports in the db, you can use this as the RowSource:

    Code:
    SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") AND (MSysObjects.Type)= -32764 ORDER BY MSysObjects.Name;

    To simply print a Report from the Combobox selection you can use code like this:

    Code:
    Private Sub cboReportToPrint_AfterUpdate()
        
        If Nz(Me.cboReportToPrint, "") <> "" Then
         DoCmd.OpenReport cboReportToPrint, acViewPreview
        Else
         MsgBox ("You Must First Select a Report To Print!")
         Me.cboReportToPrint.SetFocus
       End If
       
       Me.cboReportToPrint = Null
    
    End Sub

    You can, of course, replace acViewPreview with whatever view you want.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As neat as that is for iterating over the reports, I think "rptMthIncByRegion" is perhaps not as intuitive to users as would be
    SELECT rptName, rptAlias FROM tblReports

    because rptAlias would show in combo as Monthly Income By Region. After all, we all use Camel Case, no spaces and short as possible object names, right?
    I might even include a sort order field to group and order reports.

  6. #6
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by Micron View Post
    what you ought to be using is a combo displaying a list of table records with report name (or alias and report name) and let user choose. Less typing, no spelling issues, no checking for wrong report name.
    I have that already, I'm building both options.

  7. #7
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Thank you, this handles the error indeed.

  8. #8
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    Quote Originally Posted by davegri View Post
    #1 and #2 conflict. If the report name does not exist, and #1 handles that, then #2 is never given a chance.
    This will take care of #1.
    Code:
    Public Sub ReportName() 'Set variable report name, box prompt to open report by name manually
        On Error GoTo ReportName_Error
        Dim Message, Title, Default, ReportName
        Message = "Enter the Report Name from the list"    ' Set prompt.
        Title = "Report Name?"    ' Set title.
        Default = "rpt"    ' Set default.
        ' Display message, title, ,default value and box position x,y.
        ReportName = InputBox(Message, Title, Default, 11500, 3000)
        DoCmd.OpenReport ReportName, acViewReport
    ReportName_EXIT:
        Exit Sub
    ReportName_Error:
        Select Case Err
            Case 2103
                 MsgBox "No such report exists"
           Case Else
                MsgBox Err.Number & ", " & Err.Description & " in Procedure ReportName"
        End Select
        Resume ReportName_EXIT
    End Sub
    Thank you, this handles the error indeed.

    However, if I hit the Cancel button instead of typing a report name then OK, I get the following error message instead of just closing "2497, The action or method requires a Report Name argument. in Procedure ReportName" which corresponds to the "Case Else" message.

  9. #9
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I fixed the cancellation error

    instead of

    Code:
    Case Else            MsgBox Err.Number & ", " & Err.Description & " in Procedure ReportName"
        End Select     Resume ReportName_EXIT
    I changed to
    Code:
    Case Else            GoTo Canceled
        End Select
        Resume ReportName_EXIT
        
    Canceled:
    End Sub

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, I'm confused. Why do you need a sub if you have a combo of reports? Using combo AfterUpdate event,
    DoCmd.OpenReport Me.cmbReports, acViewPreview - that could be all.
    But you're using an input box which requires a typed in name, which can easily be a typo.

    Code example if you had an "open report" button and a combo
    Code:
    Private Sub cmdReports_Click()
    
    If IsNull(Me.cmbReports) Then 
     msgbox "Please select report from list"
     Exit Sub
    End If
    
    DoCmd.OpenReport ReportName, acViewReport
    
    End Sub
    Last edited by Micron; 06-03-2019 at 09:01 AM. Reason: added code

  11. #11
    geotrouvetout67 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    108
    I don't really need it but wanted to give options because when you know what you are looking for it's faster to type it than finding it in a somewhat long list. If you know SAP, same thing, it's faster to type "MD04", to open the report than finding it in bookmarks.

    I have it working now. It does not take an expression such as Like "name*", only the exact name but that's good enough and if the name is incorrect it pops an error message and closes. Thank you.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you're welcome.
    FYI - you can use FAYT (find as you type) feature on text and combo boxes in case you're interested for future. List is filtered to exact or partial matches. Combo needs proper settings.
    Here's one of a gazillion sources for it
    https://www.microsoft.com/en-us/micr...h-as-you-type/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 10-24-2016, 01:55 AM
  2. Button to open form sudden error
    By tgunarto in forum Macros
    Replies: 2
    Last Post: 11-21-2014, 01:04 AM
  3. Replies: 3
    Last Post: 10-20-2014, 03:25 PM
  4. Open Print Dialog when Report is opened
    By tylerg11 in forum Reports
    Replies: 1
    Last Post: 01-09-2014, 10:44 AM
  5. Button to open a Report
    By 95DSM in forum Reports
    Replies: 1
    Last Post: 07-09-2010, 11:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums