Selecting table data to array - Only one row fetched

Question

I am attempting to fetch data from a table EmailList and place it into an array, which will be passed to the "To:" field of an outlook email message (the script for the email is made). I plan on using the Join() function to combine the array into a string as so: Join(varEmailList, "; ").

My Code:

Private Sub Propose_Click()

Dim MyDB As DAO.Database
 Dim rstEmails As DAO.Recordset
 Dim varEmails() As Variant
 Dim intRowNum As Integer
 Dim intColNum As Integer

 Set MyDB = CurrentDb
 Set rstEmails = MyDB.OpenRecordset("select email from EmailList", dbOpenSnapshot)

 'Let's retrieve ALL Rows in the rstEmails Recordset
 varEmails = rstEmails.GetRows()

 MsgBox ("Number of Fields Retrieved: " & UBound(varEmails, 1) + 1)

 rstEmails.Close
 Set rstEmails = Nothing

End Sub

The issue I am having is that only one record is being found by the code, where there should be at least 10 at a time.

Try removing rstEmails.RecordCount from the varEmails call. — Gareth
– Gareth, Commented Jun 5, 2015 at 15:23
@Gareth Same issue - although it's good to know that wasn't necessary. — Ryan_W4588
– Ryan_W4588, Commented Jun 5, 2015 at 15:24

HansUp · Accepted Answer · 2015-06-05 16:28:38Z

4

The DAO.Recordset.GetRows method returns no more than one row unless you explicitly tell it to return more.

Ask GetRows to retrieve all the rows:

'Let's retrieve ALL Rows in the rstEmails Recordset
'varEmails = rstEmails.GetRows()
With rstEmails
    .MoveLast
    .MoveFirst
    varEmails = .GetRows(.RecordCount)
    .Close
End With

Here is another issue ...

MsgBox ("Number of Fields Retrieved: " & UBound(varEmails, 1) + 1)

The first dimension of that array is the fields --- in this case only one. The second dimension has the values of those fields:

MsgBox ("Number of Fields Retrieved: " & UBound(varEmails, 2) + 1)

edited Jun 5, 2015 at 16:28

answered Jun 5, 2015 at 15:53

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ChipsLetten · Accepted Answer · 2015-06-05 15:52:26Z

0

According to MSDN:

Use the GetRows method to copy records from a Recordset. GetRows returns a two-dimensional array. The first subscript identifies the field and the second identifies the row number.

You actually want the number of items in the 2nd dimension:

UBound(varEmails, 2)

answered Jun 5, 2015 at 15:52

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

Comments

Brad · Accepted Answer · 2018-05-07 15:53:37Z

0

If you supply instructions to GetRows like

rs.GetRows(100)

but there is an error in your record set such as divide by zero (#NUM), and that error is on row 50, only 49 rows will be returned and GetRows will silently stop early.

answered May 7, 2018 at 15:53

Brad

12.4k4 gold badges51 silver badges82 bronze badges

Collectives™ on Stack Overflow

Selecting table data to array - Only one row fetched

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related