Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

Array subscript out of range in vba

I had a piece of code from ID: 40357636, which has started failing.

The code rotates around a recordset and stores the words it finds in an instr to an array.

I modded to a multidimensional array.

The data is small so pasted here

if it finds "[Business" (example) it stores both [Business and "[Bus" to the array elements.
thats all it needs do. it does the first element 0,0 then it halts after that.

I cannot see why its failing as the indexing looks simple

0,0
0,1
1,0
1,1 etc


    With RstAlias

        If RstAlias.BOF And RstAlias.EOF Then
        Else
        .MoveLast
        y = .RecordCount
        .MoveFirst
        'cycle through the recordset
        Do Until RstAlias.EOF
            
            If InStr(Me.[DecodeResult].Value, .Fields("sClientDescGroup").Value) <> 0 Then
            
                ReDim Preserve AliasArray(index+1, 1) As String
              
                If .Fields("sDesc").Value = " " Or .Fields("sDesc").Value = "" Then
                
                Else
                    AliasArray(index, 0) = .Fields("sDesc").Value
                    AliasArray(index, 1) = .Fields("sClientDescGroup").Value
                    index = index + 1
                End If
            Else
            
            
            End If
        

        
        
        .MoveNext
        Loop
        
     End If
    End With

Open in new window



sDesc            sClientDescGroup
[Bus         [Business
[Prof      [Professional
Med]      Media]
Lux             Luxury
Med            Media]
Med]      Media]
Auto[Pro            [Professional
Auto[Bus      [Business
Auto[Prof      [Professional
Bus                 Business
Busines         Business
Prof               [Professional
Pro              [Professional
Professional      [Professional
ASKER CERTIFIED SOLUTION
Avatar of Darrell Porter
Darrell Porter
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

ok i will delete the bit i have and create a recordset and look at getrows method
I am not seeing how to use getrows. I have a recordset that produces the 14 rows you see in the post and i have a string and i need to see if field sclientdescgroup.value is in the string and only store those words.

so if stringin  has "[Professional" in then the following gets put into an array.
element 1     element 2
Auto[Prof      [Professional
string in might be ="black shadow [professional blue"
I am on a doubleclick event of a subform that passes the value of the row
ok i think ive got it in sql, its abstract but does return the records:

Ive put the string of words as an expression in the query itself:

I may not need an array at all now


SELECT TblModelAlias.sDesc, TblModelAlias.sClient, TblModelAlias.sModel, TblModelAlias.sClientDescGroup, InStr("Business [Professional Media]",[sClientDescGroup])<>0 AS Expr1
FROM TblModelAlias
WHERE (((TblModelAlias.sClient)='Cap') AND ((TblModelAlias.sModel)='3Series'));

Open in new window

ive shared points as youve both put me in the right direction.
Thank you - always happy to assist or, at the very least, be a wall to bounce ideas off of.