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

The absolute fastest way to do what you want would be to use SQL statements instead of looping code, but that would, of course, depend on what all you need to do. So for now, I'll assume that doing all this through code will be necessary at some point, and that your current algorithm makes sense for what you ultimately need to do.

Given that, here are a couple of modifications I would make to your code. My comments are out-dented; also watch out for line wrapping, but I assume that'll be obvious:

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

Dim xArray() As dtype

With rs
.Open "SELECT * FROM TblStructure WHERE " & strSelect, CurrentProject.Connection, adOpenStatic, adLockPessimistic, adCmdText
'Changing the SQL statement do do the work on the front end will
'save a lot of time compared to doing multiple .Find's.
.MoveLast
'Makes the .RecordCount accurate
'-----------------------------------------------------
'upsize the array to accomodate incoming data'
'-----------------------------------------------------

i = UBound(xArray)
'Assumes xArray is already used from a previous instance of this code.
'If not, then simply put i = -1

'Re-Dim for the entire recordset all at once...saves a LOT of time.
'This is, however, predicated on the idea that you can bump strSelect
'up to the WHERE clause, as I've done above.
ReDim Preserve xArray(.RecordCount + i)

j = 0
'What's j for?

.MoveFirst
Do Until .EOF
i = i + 1
'Moved up here to pre-increment, which is easier to manage in this instance.

'-----------------------------------------------------
' Load Array
'-----------------------------------------------------
xArray(i).strNameIn = ![FieldName-Input].Value
xArray(i).IntInputOrder = !FieldInputOrder.Value
xArray(i).strDataType = !FieldType.Value
xArray(i).strNameOut = ![FieldName-Output].Value
xArray(i).IntMaxLen = !FieldMaxLen.Value
xArray(i).intOutputOrder = !FieldOutputOrder.Value
xArray(i).strDataSrcTbl = !CalcSourceTbl.Value
xArray(i).strDataSrcFld = !CalcSourceField.Value
.MoveNext
Loop
End With



Rob
.

Relevant Pages

  • Re: User Define Array Data Type - Subscript out of range
    ... Works just DANDY, Robert! ... strDataType As String ... ' Load Array ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to write long string sentences
    ... Do you want to manage long SQL statements or do you want to manage long ... trying to manipulate the SQL statement as a string. ... I still question if you are wanting long string manipulation or simply ... If for some reason you don't want to use Visual Designers for managing your ...
    (microsoft.public.dotnet.languages.vb)
  • Re: User Define Array Data Type - Subscript out of range
    ... strDataType As String ... 'Makes the .RecordCount accurate ... ' Load Array ...
    (microsoft.public.access.modulesdaovba)
  • Re: Writing bzcompressed data into SQL-DB?
    ... > when compressing strings with bzcompress, I have the problem that the ... > result apparently often contains 'letters' that mess up sql statements. ... > Of course I can urlencode the string, but that wont keep it as small. ...
    (comp.lang.php)
  • Re: regex search/replace syntax
    ... > $orderSQL is a string containing a number of SQL statements with the ... the placeholder is TOTAL not &total. ...
    (perl.beginners)