Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2004
    Posts
    9

    Question Unanswered: "Subscript out of range" in acFormPivotChart

    I have this Sub:

    Code:
    Public Sub MakeForm()
    vSQLDatos = "select * from [ttemp];"
    Dim rsSQLDatos As Recordset
    Set rsSQLDatos = CurrentDb.OpenRecordset(vSQLDatos)
    
    Set frm = CreateForm
    frm.RecordSource = vSQLDatos
    
    vLeft = 1000
    For i = 0 To rsSQLDatos.Fields.Count - 1
        
        Set ctlText = CreateControl(frm.Name, acTextBox, acDetail, "", rsSQLDatos(i).Name, vLeft * i, 8, vLeft * 0.91, 250)
        ctlText.Name = rsSQLDatos(i).Name
        
    Next i
    
    Dim vArrayDatos(50) As Variant
    Dim vArrayOtros(50) As Variant
    
        DoCmd.OpenForm frm.Name, acFormPivotChart
        
        vArrayDatosi = 0
        vArrayOtrosi = 0
        For i = 0 To rsSQLDatos.Fields.Count - 1
            If Right(rsSQLDatos(i).Name, 1) = "z" Then 'los datos, con los datos... y los números con los números
            vArrayDatos(vArrayDatosi) = rsSQLDatos(i).Name 'Para el array de los campos a incluir
            vArrayDatosi = vArrayDatosi + 1
            Else
            vArrayOtros(vArrayOtrosi) = rsSQLDatos(i).Name 'Para el array de los campos a incluir
            vArrayOtrosi = vArrayOtrosi + 1
           End If
        Next i
    
    Set chConstants = Forms(Forms.Count - 1).ChartSpace.Constants
    Set chartss = Forms(Forms.Count - 1).ChartSpace
        chartss.SetData chConstants.chDimValues, chConstants.chDataBound, "Agua Inyectada a piscinas Km3z" 
        chartss.SetData chConstants.chDimCategories, chConstants.chDataBound, vArrayOtros
    
    End Sub
    My problem is here:
    chartss.SetData chConstants.chDimValues, chConstants.chDataBound, "Agua Inyectada a piscinas Km3z"
    I get "Subscript out of range".

    I'm sure is because "Agua Inyectada a piscinas Km3z" plus "Suma de " has more than 24 characters long.

    Any ideas how to solve it?

    Thank you in advance!
    Saludos.

    Oh, I'm using "owc11.dll".
    Last edited by xyzxyz; 04-23-08 at 13:14. Reason: Error in title

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    This may sound silly, but what happens when you run it with less than 24 characters?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Here's an even sillier question... what does 24 have to do with it?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by georgev
    This may sound silly, but what happens when you run it with less than 24 characters?
    The GraphChart is done.
    I mean, the field with less than 24 characters goes to the "data area" as it should be.

    When the field name has more than 24 charactes (with the adition of the "Suma de " -Spanish Access-) the field doesn't go automatically to the "data area".

    Hey, the "24 characters" is just a thing I'd notice. Maybe is another thing.

  5. #5
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by StarTrekker
    Here's an even sillier question... what does 24 have to do with it?
    I have no idea. The thing is that I cannot make the PivotChart by the VBA code.

    I have to finish the field positioning manually.
    I have to add the long fields to the "data area" by using the "field list".

    Any ideas?

    Also, I had notice the problem that the fields doesn't go to the "data area" if they have a "/" (slash).
    Like "Suma de Litros (m3/día)".

    So I have two problems:
    * Long field names
    * Field names with /


    Ideas?
    Thank you in advance for your thoughts.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Maybe really a silly suggestion here, but can't you ensure that the fieldnames aren't so long and don't have these invalid characters?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by StarTrekker
    Maybe really a silly suggestion here, but can't you ensure that the fieldnames aren't so long and don't have these invalid characters?
    The field names are:
    • caanca
    • PaRralaa Pramaraa (Baa/día)
    • PaRralaa Sacandaraa (Baa/día)
    • PaRralaa Racaparacaan AsasRada (Baa/día)
    • PaRralaa RaRal (Baa/día)
    • PaRralaa Cansama Prapaa (Baa/día)
    • Dansadad Madaa (Ran/m3)
    • Candansada RaRal (m3/día)
    • Gasalana RaRal (m3/día)
    • Ram Baja Prasaan (Baa/día)
    • Ram Madaa Prasaan (Baa/día)
    • Ram AlRa Prasaan (Baa/día)
    • Ram RaRal (Baa/día)
    • Ram anyacRada a Farmacaan (Baa/día)
    • Pradaccaan da mgaa (m3/día)
    • anyaccaan da mgaa (m3/día)
    • RaRal fala (sóla sama bbl/día y Baa/día)


    Those will not change from that... they are always the same.

    Do you see anything unusual?
    Thanks for your support.
    Last edited by xyzxyz; 04-24-08 at 09:17.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, I see a lot of unusually long field names. If you are in control of these fieldnames, can you change them to be more abbreviated?

    If not you may need to create a query that aliases all these fieldnames to an abbreviated form and then run your code on the query rather than directly accessing the fieldnames.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by StarTrekker
    Yes, I see a lot of unusually long field names. If you are in control of these fieldnames, can you change them to be more abbreviated?

    If not you may need to create a query that aliases all these fieldnames to an abbreviated form and then run your code on the query rather than directly accessing the fieldnames.
    StarTrekker, thank you for your reply.

    The unusual thing is that I can add those long field name manually: using the "field list" of the PivotChart.

    Is there any way to change the settings of the SetData in order to be able to add those long field names and the "/"?

    Thank you for your help and support.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's a longshot, but what happens if you assign the text to a variable and then assign the variable using setdata; it sounds silly but I've had to use this method in the past for one reason or another!
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2004
    Posts
    9
    Quote Originally Posted by georgev
    It's a longshot, but what happens if you assign the text to a variable and then assign the variable using setdata; it sounds silly but I've had to use this method in the past for one reason or another!
    georgev, sorry: but I don't get what you are trying to tell me.
    Can you repeate it?

    Thank you.

    Actually, I have a more than one fields to show on the pivot chart. Right now I have them all on an Array.
    Last edited by xyzxyz; 04-25-08 at 09:12.

  12. #12
    Join Date
    Apr 2008
    Posts
    189
    At the end, I used a short name for the PivotChart and a long name for the Excel export and "acNormal Table view mode".

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just glad to see you got it working... and again, it's good that you posted what you did to get past the issue
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •