Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Runtime Error 9 Subscript out of range

Discussion in 'Information Technology' started by James, Nov 5, 2005.

  1. James

    James
    Expand Collapse
    Guest

    I am trying to add code to a report module. This is a new imported report
    without any prior modules in it.

    The following line of code:
    Modules("Report_" & ReportName).AddFromString str
    Fails with Runtime Error 9 Subscript out of range.

    If I open the report, add a dummy sub routine and then run the code it works
    fine.

    Here is the complete function:

    Function AddCode(ReportName As String)

    Dim str As String

    str = "some_code"

    DoCmd.OpenReport ReportName, acViewDesign
    Application.Reports(ReportName).Visible = False
    Modules("Report_" & ReportName).AddFromString str
    Reports(ReportName).OnClose = "[Event Procedure]"
    DoCmd.Close acReport, ReportName, acSaveYes

    End Function
     
  2. Loading...


  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Do you have spaces or special characters in the Report name?

    If you do, you have a naming conflict between Access and VBA ...

    Check Access Help topics:

    * Guidelines for naming fields, controls and objects.
    *Guidelines for naming Visual Basic procedures, variables and constants.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "James" <James@discussions.microsoft.com> wrote in message
    news:3216DE1E-0C9F-461D-ADCD-63FBE9C93597@microsoft.com...
    >I am trying to add code to a report module. This is a new imported report
    > without any prior modules in it.
    >
    > The following line of code:
    > Modules("Report_" & ReportName).AddFromString str
    > Fails with Runtime Error 9 Subscript out of range.
    >
    > If I open the report, add a dummy sub routine and then run the code it
    > works
    > fine.
    >
    > Here is the complete function:
    >
    > Function AddCode(ReportName As String)
    >
    > Dim str As String
    >
    > str = "some_code"
    >
    > DoCmd.OpenReport ReportName, acViewDesign
    > Application.Reports(ReportName).Visible = False
    > Modules("Report_" & ReportName).AddFromString str
    > Reports(ReportName).OnClose = "[Event Procedure]"
    > DoCmd.Close acReport, ReportName, acSaveYes
    >
    > End Function
    >
     
  4. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Just read your question again and noted that there is a possibility that the
    Property "Has Module" of your report might have been set to No / False. Try
    setting this to Yes / True.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "James" <James@discussions.microsoft.com> wrote in message
    news:3216DE1E-0C9F-461D-ADCD-63FBE9C93597@microsoft.com...
    >I am trying to add code to a report module. This is a new imported report
    > without any prior modules in it.
    >
    > The following line of code:
    > Modules("Report_" & ReportName).AddFromString str
    > Fails with Runtime Error 9 Subscript out of range.
    >
    > If I open the report, add a dummy sub routine and then run the code it
    > works
    > fine.
    >
    > Here is the complete function:
    >
    > Function AddCode(ReportName As String)
    >
    > Dim str As String
    >
    > str = "some_code"
    >
    > DoCmd.OpenReport ReportName, acViewDesign
    > Application.Reports(ReportName).Visible = False
    > Modules("Report_" & ReportName).AddFromString str
    > Reports(ReportName).OnClose = "[Event Procedure]"
    > DoCmd.Close acReport, ReportName, acSaveYes
    >
    > End Function
    >
     
  5. James

    James
    Expand Collapse
    Guest

    I do not have any special characters or spaces in my report.
    Also when I set the HasModule property to True and ran the code I got the
    following error: Run Time Error 7961. MS Access can't find the module
    'Report_testreport' referred to in a macro expression or VB code.


    "Van T. Dinh" wrote:

    > Just read your question again and noted that there is a possibility that the
    > Property "Has Module" of your report might have been set to No / False. Try
    > setting this to Yes / True.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "James" <James@discussions.microsoft.com> wrote in message
    > news:3216DE1E-0C9F-461D-ADCD-63FBE9C93597@microsoft.com...
    > >I am trying to add code to a report module. This is a new imported report
    > > without any prior modules in it.
    > >
    > > The following line of code:
    > > Modules("Report_" & ReportName).AddFromString str
    > > Fails with Runtime Error 9 Subscript out of range.
    > >
    > > If I open the report, add a dummy sub routine and then run the code it
    > > works
    > > fine.
    > >
    > > Here is the complete function:
    > >
    > > Function AddCode(ReportName As String)
    > >
    > > Dim str As String
    > >
    > > str = "some_code"
    > >
    > > DoCmd.OpenReport ReportName, acViewDesign
    > > Application.Reports(ReportName).Visible = False
    > > Modules("Report_" & ReportName).AddFromString str
    > > Reports(ReportName).OnClose = "[Event Procedure]"
    > > DoCmd.Close acReport, ReportName, acSaveYes
    > >
    > > End Function
    > >

    >
    >
    >
     
  6. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    It still sounds like VBA is not picking up the Class container behind the
    Report "testreport".

    Try adding some code in the module (e.g. some silly function), copile the
    code, save the Report and close the database. Open the database again and
    see if you pick up the Class module.

    Do you regularly compact & repair the database?

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "James" <James@discussions.microsoft.com> wrote in message
    news:33CA9737-C755-4360-B815-7D5C0C683F31@microsoft.com...
    >I do not have any special characters or spaces in my report.
    > Also when I set the HasModule property to True and ran the code I got the
    > following error: Run Time Error 7961. MS Access can't find the module
    > 'Report_testreport' referred to in a macro expression or VB code.
    >
     

Share This Page