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

Works just DANDY, Robert!

Thanks a ton!

PatK

"PatK" wrote:

Thanks...I am going to make these changes. I will let you know how it goes.
It may solve a problem I am having now, that I did not before, mainly, on the
.open, it is expecting a select statement (or somesuch error I have not seen
thus far), and I see you have one (altho now I am scratching my head as to
why the .open worked before, and not now).

There is not a lot of data to load into the table...about 40 entries, but I
am all for coding things efficiently, as I leverage my past work, in the
future.

Oh, and J was for a debug loop I was using later, that I exclude from the
code....LOL (sorry about that!).

Patk

Thanks again!

"Robert Morley" wrote:

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
    ... strDataType As String ... 'Makes the .RecordCount accurate ... ' Load Array ...
    (microsoft.public.access.modulesdaovba)
  • 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. ... strDataType As String ... ' Load Array ...
    (microsoft.public.access.modulesdaovba)
  • Re: _ecvt, _gcvt and _fcvt problem
    ... The question is...Do you understand what Robert ... >>>But I have problem about conversion double to string. ... >> problem I strongly recommend taking some time to learn about UNICODE. ... >> please indicate which newsgroup and message). ...
    (microsoft.public.windowsce.embedded.vc)
  • Re: String.each
    ... On 3/3/07, Robert Klemme wrote: ... >>> each character of a string and then processes it using a block. ... String's enumeration is a bit weird and inconsistent. ... String as array of lines does have it's uses at times but I wonder ...
    (comp.lang.ruby)
  • Re: Improving Adas image - Was: 7E7 Flight Controls Electronics
    ... Robert I. Eachus wrote: ... function Imagereturn String is ... type Color is (Red, Orange, Yellow, Blue, Green, Violet, ...
    (comp.lang.ada)