Appending/Adding values to Array while looping through recordset

access 2003 vba
rsSql  is a query def created in earleier part of code



This code is only adding One Item from the recordset ?

Do Until rsSql.EOF  ' query loop

< CODE>
' trying to add records  to the array here
' only adding 1 item to the array.
Dim intArrayX As Variant
ReDim intArrayX(0 To rsSql.RecordCount)
' start to add values to the array
intArrayX(0) = rsSql.Fields(0).Value ' Item-Grainger Sku
'intArrayX (1)= rsSql.Fields(1).Value ' NOUNPHRS1
'intArrayX (2)= rsSql.Fields(2).Value ' NOUNPHRS2
'intArrayX (3)= rsSql.Fields(3).Value ' NOUNPHR3
intArrayX(4) = rsSql.Fields(4).Value ' RichText
intArrayX(5) = "(found: " & wdFound & " )" ' wdfound nouns
intArrayX(6) = score
'intArrayX (7)
intArrayX(8) = rsCust.Fields("fldDid").Value
intArrayX(9) = rsSql.Fields(9).Value ' WWGMFRNUM
intArrayX(10) = rsSql.Fields(10).Value ' WWGMFGNAME
intArrayX(11) = rsSql.Fields(11).Value  '  DESC
intArrayX(12) = rsSql.Fields(12).Value   ' COMMENTS
intArrayX(13) = rsSql.Fields(13).Value  ' REDBOOKNUM
intArrayX(14) = rsSql.Fields(14).Value  ' XREF
intArrayX(15) = rsSql.Fields(15).Value  ' SPIN
intArrayX(16) = rsSql.Fields(16).Value  ' UOM
intArrayX(17) = rsSql.Fields(17).Value  ' UOM QTY
intArrayX(18) = rsSql.Fields(18).Value  ' SHIP
intArrayX(19) = rsSql.Fields(19).Value  ' SHIP QTY
intArrayX(20) = rsSql.Fields(20).Value  ' ALT1
intArrayX(21) = rsCust.Fields("fldMfgname").Value
    intArrayX(22) = rsCust.Fields("fldMfgnameOrig").Value
     intArrayX(23) = rsCust.Fields("fldMfrnum").Value
   intArrayX(24) = rsCust.Fields("fldMfrnumOrig").Value
  intArrayX(25) = Trim(rsCust.Fields("fldDescription").Value)
intArrayX(26) = rsCust.Fields("fldDescriptionOrig").Value
'intArrayX(0) = rsCust.Fields("fldwwg").Value

< code >

       rsSql.MoveNext
Loop

Loop
Do Until rsSql.EOF  ' query loop
 
< CODE>
 
' trying to add records  to the array here
' only adding 1 item to the array.
Dim intArrayX As Variant
ReDim intArrayX(0 To rsSql.RecordCount)
' start to add values to the array
intArrayX(0) = rsSql.Fields(0).Value ' Item-Grainger Sku
'intArrayX (1)= rsSql.Fields(1).Value ' NOUNPHRS1
'intArrayX (2)= rsSql.Fields(2).Value ' NOUNPHRS2
'intArrayX (3)= rsSql.Fields(3).Value ' NOUNPHR3
intArrayX(4) = rsSql.Fields(4).Value ' RichText
intArrayX(5) = "(found: " & wdFound & " )" ' wdfound nouns
intArrayX(6) = score
'intArrayX (7) =   ' nothing added yet
intArrayX(8) = rsCust.Fields("fldDid").Value
intArrayX(9) = rsSql.Fields(9).Value ' WWGMFRNUM
intArrayX(10) = rsSql.Fields(10).Value ' WWGMFGNAME
intArrayX(11) = rsSql.Fields(11).Value  '  DESC
intArrayX(12) = rsSql.Fields(12).Value   ' COMMENTS
intArrayX(13) = rsSql.Fields(13).Value  ' REDBOOKNUM
intArrayX(14) = rsSql.Fields(14).Value  ' XREF
intArrayX(15) = rsSql.Fields(15).Value  ' SPIN
intArrayX(16) = rsSql.Fields(16).Value  ' UOM
intArrayX(17) = rsSql.Fields(17).Value  ' UOM QTY
intArrayX(18) = rsSql.Fields(18).Value  ' SHIP
intArrayX(19) = rsSql.Fields(19).Value  ' SHIP QTY
intArrayX(20) = rsSql.Fields(20).Value  ' ALT1
intArrayX(21) = rsCust.Fields("fldMfgname").Value
    intArrayX(22) = rsCust.Fields("fldMfgnameOrig").Value
     intArrayX(23) = rsCust.Fields("fldMfrnum").Value
   intArrayX(24) = rsCust.Fields("fldMfrnumOrig").Value
  intArrayX(25) = Trim(rsCust.Fields("fldDescription").Value)
intArrayX(26) = rsCust.Fields("fldDescriptionOrig").Value
'intArrayX(0) = rsCust.Fields("fldwwg").Value
 
< code >
 
       rsSql.MoveNext
Loop
 
Loop

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

go to the last record first

 rsSql.movelast     ' to  get a correct record count

 rsSql.movefirst


Do Until rsSql.EOF  ' query loop
0
FordraidersAuthor Commented:
cap, forgive me, but ?

go to the last record first

rsSql.movelast  ' to...   ' <---  this line before the loop   rsSql.movefirst
do Until rsSql.EOF  ' query loop
0
Rey Obrero (Capricorn1)Commented:
yes you need these two lines

rsSql.movelast  
rsSql.movefirst

do Until rsSql.EOF
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
so just i'am clear...by the time i'am done the records will be added to the array.
If the rssql holds 1,000- records....the array will hold 1,000 records .

0
FordraidersAuthor Commented:
cap, ok  I figured it out...

I have to keep a running total of the rows as I go along the "rssql.movenext"

So I added.
Dim intArrayX() As Variant

rsSql.movelast  
rsSql.movefirst
u = 0
do Until rsSql.EOF
<code>

ReDim Preserve intArrayX(1 To rsSql.RecordCount, 26)
u = u + 1
' start to add values to the array
intArrayX(u, 0) = rsSql.Fields(0).Value
etc...
rsSql.Movenext

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.