• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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

0
Fordraiders
Asked:
Fordraiders
  • 3
  • 2
1 Solution
 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now