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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
so if stringin has "[Professional" in then the following gets put into an array.
element 1 element 2
Auto[Prof [Professional
ASKER
string in might be ="black shadow [professional blue"
ASKER
I am on a doubleclick event of a subform that passes the value of the row
ASKER
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
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'));
ASKER
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.
ASKER