dcsimg
Results 1 to 24 of 24

Thread: [RESOLVED] subscript out of range run time error 9

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Resolved [RESOLVED] subscript out of range run time error 9

    To all experts in VBA,

    Can help to find what wrongs with my code ??? keep giving me run time error 9. What is the solutions to prevent this kind of things to happen again ??

    Thank in advance and appreciated your helps.

    This is my table from database (dummy)


    This the form when i selected sixth index start to show error.


    Here is my code
    Code:
    Private Sub List0_AfterUpdate()
    
    With Me
    .Text2.Value = Split(.Combo14.Column(1), ",")(.List0.ListIndex)
    .Text4.Value = Split(.Combo14.Column(2), ",")(.List0.ListIndex)
    .Text6.Value = Split(.Combo14.Column(3), ",")(.List0.ListIndex)
    .Text8.Value = Split(.Combo14.Column(4), ",")(.List0.ListIndex)
    .Text10.Value = Split(.Combo14.Column(5), ",")(.List0.ListIndex)
    .Text12.Value = Split(.Combo14.Column(6), ",")(.List0.ListIndex)
    End With
    
    End Sub
    Attached Images Attached Images   

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    The indices would be 0 to 5, not 1 to 6.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    My code implies that have a rowsource of 6 columns each column of which is a string which can be split into an array,
    column from 1 to 6 indicated field 6 to field 11. thereafter, the value will populate either 1to6 depending on selected index in listbox.

    however, field 6 to 11 not always contains 6 array.. sometime might have 4 or 5 or even 0 data in the field.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    My code implies that have a rowsource of 6 columns each column of which is a string which can be split into an array,
    column from 1 to 6 indicated field 6 to field 11. thereafter, the value will populate either 1to6 depending on selected index in listbox.

    however, field 6 to 11 not always contains 6 array.. sometime might have 4 or 5 or even 0 data in the field.

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    I misunderstood your issue. Is this line:

    Code:
    .Text12.Value = Split(.Combo14.Column(6), ",")(.List0.ListIndex)
    Giving you the error?

    If so, count the number of items in the list, and only try to populate that number of text boxes.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    is this line giving error..

    Code:
    .Text2.Value = Split(.Combo14.Column(1), ",")(.List0.ListIndex)
    the fields is meant to be have lesser items in the list. because i will like to make it in the way that if the field return less array it will ignore n continue to run the forms

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    When you split, find the count, something like:

    Code:
    Dim splits() As String
        
        splits = Split("a,b,c,d,e", ",")
        
        If UBound(splits) + 1 > 5 Then
            'populate the 6th text box
        End If

  8. #8
    Fanatic Member
    Join Date
    Feb 2013
    Posts
    946

    Re: subscript out of range run time error 9

    as vbfbryce has pointed out, the problem is that your line of code
    Code:
    Split(.Combo14.Column(6), ",")(.List0.ListIndex)
    is trying to get something that doesnt exist
    it looks like the List0 is using the data in field5 and if that is right then your code should be
    Code:
    Split(.Combo14.Column(6), ",")(.List0.ListIndex - 1)
    Yes!!!
    Working from home is so much better than working in an office...
    Nothing can beat the combined stress of getting your work done on time whilst
    1. one toddler keeps pressing your AVR's power button
    2. one baby keeps crying for milk
    3. one child keeps running in and out of the house screaming and shouting
    4. one wife keeps nagging you to stop playing on the pc and do some real work.. house chores
    5. working at 1 O'clock in the morning because nobody is awake at that time
    6. being grossly underpaid for all your hard work


  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    did tried out both Gbeats and vbfyrce codes i still got the same error. when i try to step into debug.. its pointed to this line again.
    Code:
    .text2.value=splits(.combo14.column(1), ",")(.list0.listindex)
    i did count numbers of counts but error still exist
    Last edited by Waka; Feb 10th, 2015 at 10:10 AM.

  10. #10
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    and what is the value of (.list0.listindex) at that time?

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    4 reflect in the textbox.value

  12. #12
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    That's not my question. What is the value of this:

    Code:
     (.list0.listindex)
    in the debug window at the time?

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    oh.. when i select "f" in the listbox (.list0.listindex) value is 0. thus, if i select "y", (.list0listindex) value change to 4.
    however, my textbox .value remain as 4 regards of which item i select in the listbox

  14. #14
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    I'm having trouble following...any way you could zip and attach the sample book with the code?

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    Sure. I have uploaded the file.

    my apology about the mess. BTW the code is under form1 in the zip file.

    and also i have amend the table field for better understand the flow
    Attached Files Attached Files
    Last edited by Waka; Feb 10th, 2015 at 08:24 PM. Reason: reupload a simple version of attachment

  16. #16
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,722

    Re: subscript out of range run time error 9

    the listindex is the same for all textboxes, so surely no error there, else it would error on the first

    are you sure there are enough columns in the combobox?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    yes the listindex is the same for all textboxes. thats why im puzzle where when wrong in the code.

    Yep, the file i attached was given 20 columns to the combobox inwhich should be more than enough
    Last edited by Waka; Feb 11th, 2015 at 04:30 AM.

  18. #18
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,722

    Re: subscript out of range run time error 9

    are you sure that the content of column 6, when split has enough elements?
    what is the content of column 6?

    as i do not use access program i can only look at your code
    someone else may well test you sample later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  19. #19
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    Change your code in post #1 to this:

    Code:
    Private Sub List0_AfterUpdate()
    With Me
        .Text2.Value = Split(.combo14.Column(1), ",")(.List0.ListIndex - 1)
        .Text4.Value = Split(.combo14.Column(2), ",")(.List0.ListIndex - 1)
        .Text6.Value = Split(.combo14.Column(3), ",")(.List0.ListIndex - 1)
        .Text8.Value = Split(.combo14.Column(4), ",")(.List0.ListIndex - 1)
        .Text10.Value = Split(.combo14.Column(5), ",")(.List0.ListIndex - 1)
        .Text12.Value = Split(.combo14.Column(6), ",")(.List0.ListIndex - 1)
    End With
    End Sub
    When you use Split, you get a zero-based array, so it's 0 to 5, not 1 to 6, whereas the list indices are one-based.

  20. #20

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    Quote Originally Posted by westconn1 View Post
    are you sure that the content of column 6, when split has enough elements?
    what is the content of column 6? Yep, content in column 6 consisted of 1,2,3,4,5,6. If you look at my dummy table the column 0 contents f,g,h,t,y,I. hence when following the sequence of columns from column 0 to 6, inwhich is the last column that is the content for column 6.

    as i do not use access program i can only look at your code
    someone else may well test you sample later

    vbfbryce i try out the codes, error still remains..

    To all,
    what about is there a condition which i can validate the number of arrays compare base of the listindex. let's say base on my presumption the code look like.
    Code:
    if condition1 then
    .Text2.Value = Split(.combo14.Column(1), ",")(.List0.ListIndex - 1)
    elseif condition2 then
    .Text4.Value = Split(.combo14.Column(2), ",")(.List0.ListIndex - 1)
    etc.
    etc.
    etc.
    end if
    Last edited by Waka; Feb 11th, 2015 at 08:13 PM.

  21. #21
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,565

    Re: subscript out of range run time error 9

    I changed what you zipped to the code in post #19, and it generated no errors, so I'm back to being thoroughly confused.

  22. #22

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    Ya error still remains, but the error is not occur when select "I" instead now is when i select the "f" the error pops out again. when you test have you try to select from "f" onwards all the way to "I" in the listbox?

  23. #23
    VBA Nutter visualAd's Avatar
    Join Date
    Apr 2002
    Location
    Ickenham, UK
    Posts
    4,907

    Re: subscript out of range run time error 9

    Some of your strings contain more tokens than others. Your logic needs to take this in to account:
    Code:
    Private Sub List0_AfterUpdate()
    
    With Me
    If UBound(Split(.combo14.Column(1), ",")) >= .List0.ListIndex Then .Text2.Value = Split(.combo14.Column(1), ",")(.List0.ListIndex)
    If UBound(Split(.combo14.Column(2), ",")) >= .List0.ListIndex Then .Text4.Value = Split(.combo14.Column(2), ",")(.List0.ListIndex)
    If UBound(Split(.combo14.Column(3), ",")) >= .List0.ListIndex Then .Text6.Value = Split(.combo14.Column(3), ",")(.List0.ListIndex)
    If UBound(Split(.combo14.Column(4), ",")) >= .List0.ListIndex Then .Text8.Value = Split(.combo14.Column(4), ",")(.List0.ListIndex)
    If UBound(Split(.combo14.Column(5), ",")) >= .List0.ListIndex Then .Text10.Value = Split(.combo14.Column(5), ",")(.List0.ListIndex)
    If UBound(Split(.combo14.Column(6), ",")) >= .List0.ListIndex Then .Text12.Value = Split(.combo14.Column(6), ",")(.List0.ListIndex)
    End With
    End Sub
    I would also suggest re-writing to use a loop to make it cleaner and re-name your controls to something a little more sensible
    Code:
    Private Sub List0_AfterUpdate()
        Dim strSplit() As String
        Dim x As Integer
    
        With Me
            For x = 1 To 6
                strSplit = Split(.combo14.Column(x), ",")
                
                If UBound(strSplit) >= .List0.ListIndex Then
                    .Controls("Text" & (x * 2)).Value = strSplit(.List0.ListIndex)
                Else
                    .Controls("Text" & (x * 2)).Value = "" ' clears the text box when no token exists
                End If
            Next x
        End With
        
    End Sub
    PHP || MySql || Apache || Get Firefox || OpenOffice.org || Click || Slap ILMV || 1337 c0d || GotoMyPc For FREE! Part 1, Part 2

    | PHP Session --> Database Handler * Custom Error Handler * Installing PHP * HTML Form Handler * PHP 5 OOP * Using XML * Ajax * Xslt | VB6 Winsock - HTTP POST / GET * Winsock - HTTP File Upload

    Latest quote: crptcblade - VB6 executables can't be decompiled, only disassembled. And the disassembled code is even less useful than I am.

    Random VisualAd: Blog - Latest Post: When the Internet becomes Electricity!!


    Spread happiness and joy. Rate good posts.

  24. #24

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2009
    Location
    Singapore
    Posts
    355

    Re: subscript out of range run time error 9

    Awesome!! THANKS BUDDY!!

    your code works!!! and ya i will change my control source name. what is having now is for testing n dummy purpose..

    thanks to all and really appreciated, buddies.
    Last edited by Waka; Feb 12th, 2015 at 01:59 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
  •  



Featured


Click Here to Expand Forum to Full Width