Re: User Define Array Data Type - Subscript out of range

At some point before you actually start using the array, yes, you have to ReDim it. The most efficient way is normally to find out how many elements you need (using .RecordCount, presumably), ReDim the arrays, then move into your loop.

A less-efficient, but sometimes-necessary way, is to ReDim the array to be one larger than it used to be on each iteration. To do that, you also have to use the Preserve keyword, so...

ReDim Preserve xArray.strNameIn(UBound(xArray.strNameIn) + 1)

You would have to do that for each element.

The other way to approach it would be to make the various members of dtype to standard datatypes (i.e., not arrays), then make an array of dtype instead of having different arrays within it.


Rob

PatK wrote:
Hi! I am making my first foray into arrays and am running into a problem. WHen I run this code, I am getting an error: Runtime Error 9, subscript out of range (I have noted the point where DEBUG is pointing to the error. Is the problem my user defined datatype? Do I have to "ReDim" the array somehow, inside the subroutine? ARGGGG...! Help

Thanks! PatK




Option Compare Database
Type dtype
strNameIn() As String
IntInputOrder() As Integer
strDataType() As String
strNameOut() As String
IntMaxLen() As Integer
intOutputOrder() As Integer
strDataSrcTbl() As String
strDataSrcFld() As String
End Type
------------------------------------------------------------------------
Sub xformData()

Dim xArray As dtype
Dim rs As ADODB.Recordset
Dim strSelect As String
Dim strDTypein As String
Dim strOutTbl As String
Dim strYourDB As String
Dim i As Integer

strDTypein = "EVOpen"
strOutTbl = "Tbl-Tickets"
strSelect = "DataType='" & strDTypein & "'"

Set rs = New ADODB.Recordset
With rs
.Open "TblStructure", CurrentProject.Connection, adOpenStatic, _ adLockPessimistic
.MoveFirst
.Find strSelect
i = 0
Debug.Print strSelect;
Do Until .EOF
Debug.Print i; <- this is equal to zero on first/failing loop
xArray.strNameIn(i) = rs.Fields("FieldName-Input") <- Getting error here
xArray.strDataType(i) = rs.Fields("FieldType")
MsgBox "Fieldname: " & xArray.strNameIn(i) & " DataType: " & _ xArray.strDataType(i)
i = i + 1
.Find strSelect, 1
Loop
End With
rs.Close
Set rs = Nothing
End Sub
.

Relevant Pages

  • Re: Array compile error "Expected: As"
    ... if you redim a dynamic array without using the optional preserve ... ReDim nameAs String ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Array compile error "Expected: As"
    ... if you redim a dynamic array without using the optional preserve argument all the data is wiped from the array. ... ReDim nameAs String ... Dim rst As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • RE: Structure conversion from C++ to VB-2008?
    ... One of the most important structures is AmiVar structure. ... point number, the array of floating point numbers, a string or IDispatch ... Dim 13012679 as Integer ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Structure conversion from C++ to VB-2008?
    ... Public Structure AmiVar ... Dim type As Integer ... Public *array as Single ... Public *string as String ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Structure conversion from C++ to VB-2008?
    ... One of the most important structures is AmiVar structure. ... point number, the array of floating point numbers, a string or IDispatch ... Dim 13012679 as Integer ...
    (microsoft.public.dotnet.languages.vb)
Loading