Solved

vba getting proper uboundin an array

Posted on 2016-08-08
6
53 Views
Last Modified: 2016-08-11
access vba 2010

After i get a recordset count
I'm trying to put the items into an array.
The array keeps coming up with only one item.
even though i have 10 records ?
' put the items into an array for emailing each row.
                    dArrp = rs1.GetRows()
                    
                    ' just in case i need it
d = rs1.RecordCount

lngCount = UBound(dArrp, 2) + 1
                                
 For i = LBound(dArrp, 2) To UBound(dArrp, 2) ' my ubound always ends up being "one"  

Open in new window



Thanks
fordraiders
0
Comment
Question by:fordraiders
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41748256
I found that to happen if the recordset is on the last record.
so, please try to use :
rs1.MoveFirst()
dArrp = rs1.GetRows()

Open in new window

1
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41748258
Hi.

First you mustcheck if the recordset has data:

if not (rs1.bof and rs1.eof) then

Open in new window


Later you must fill the recordset
rs1.movelast
rs1.movefirst

Open in new window

and then you can get anytjing from the recordset.

Best regards.

Antonio (Barcelona, Spain)
1
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41748394
GetRows need to know how many records to pull. Thus, to get all records:
rs1.MoveLast
rs1.MoveFirst
dArrp = rs1.GetRows(rs.RecordCount)
                    
lngCount = UBound(dArrp, 2) + 1

Open in new window

/gustav
1
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41748400
Gustav, I will need to disagree...
https://msdn.microsoft.com/en-us/library/ms675120(v=vs.85).aspx
Rows
    Optional. A GetRowsOptionEnum value that indicates the number of records to retrieve. The default is adGetRowsRest.

which means that if you are on the last record, it will only get that one
using MoveFirst, it will get "the rest", no need to specify the number of records you want.

to test .BOF and .EOF is not needed, because if you "moved" the cursor to the last record, you know you have some records indeed.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 41748689
Unable to reproduce the issue:
All the following give the same result:
    Dim trs1 As Recordset
    Dim ta As Variant
    
    Set trs1 = CurrentDb.OpenRecordset("a")
    trs1.MoveLast
    ta = trs1.GetRows()
    Debug.Print UBound(ta)
    
    Set trs1 = Nothing
    Set trs1 = CurrentDb.OpenRecordset("a")
    ta = trs1.GetRows()
    Debug.Print UBound(ta)
    
    Set trs1 = Nothing
    Set trs1 = CurrentDb.OpenRecordset("a")
    ta = trs1.GetRows(trs1.RecordCount)
    Debug.Print UBound(ta)

Open in new window

0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41753382
thnx
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now