Copying arrays into Recordsets

[Mike Stillman]

Summary: I am able to save several rows of DataGrid bookmarks into an array,
but I cannot find a way
to copy that array into a Recordset for further manipulation.

Details: The goal is to allow the user to export selected rows from a DataGrid
of query results into Excel.
My code works fine when exporting the entire grid, but when I try to copy
the array of bookmarks into a
Recordset, nothing gets copied. I am new to VB, and I suspect that I am using
the "AddNew" method
incorrectly, or that it is the wrong method for my needs.

Thanks for engaging in this dialogue, Mr. Vaughn. I will buy your book as
soon as my local bookstore
gets a copy.

Here is the relevant section of code:

ElseIf blnSelRow = True Then
Dim i As Integer 'counter
Dim rsSel As Recordset
'Set rsSel = New Recordset
Dim intCount As Integer

intCount = frmSimpleFind.dgdQuickResults.SelBookmarks.Count -
1

ReDim aBookmarks(intCount)

For i = 0 To intCount
aBookmarks(i) = frmSimpleFind.dgdQuickResults.SelBookmarks(i)
'populate array with selected rows
rsSel.AddNew aBookmarks(i)
Next i

With rsSel 'loop through bookmarks
Do Until .EOF
For intCounter = 1 To intFieldCount
varField = .Fields(intCounter - 1).Value 'add each field's value
If IsNull(varField) Then 'handle null fields
varField = "<null>"
End If
obWorkSheet.Cells(lngRowCount, intCounter).Value = varField
Next intCounter
lngRowCount = lngRowCount + 1
If lngRowCount > varMaxRecs Then
Exit Do
End If
.MoveNext
Loop
End With
End If

[Bill Vaughn]

Well, there are a couple of ideas that come to mind. First, you can select
rows from a Recordset using the Filter method. That is, once you decide that
you want a row (the user clicks on the grid), you can change a field in the
Recordset to reflect that selection. Once all selections are made you can
set the Filter property to hide all non-selected rows. This Recordset(or
the Fields thereof) can then be exported to anywhere. Saving the bookmarks
to the Recordset did not make sense to me. A bookmark (on a bookmarkable
(scrollable)) Recordset is simply a variant pointer to a row. You can't really
pass these anywhere to use elsewhere.

hth

bv
"Mike Stillman" <mikebvc@ripco.com> wrote:
>
>Summary: I am able to save several rows of DataGrid bookmarks into an array,
>but I cannot find a way
>to copy that array into a Recordset for further manipulation.
>
>Details: The goal is to allow the user to export selected rows from a DataGrid
>of query results into Excel.
>My code works fine when exporting the entire grid, but when I try to copy
>the array of bookmarks into a
>Recordset, nothing gets copied. I am new to VB, and I suspect that I am
using
>the "AddNew" method
>incorrectly, or that it is the wrong method for my needs.
>
>Thanks for engaging in this dialogue, Mr. Vaughn. I will buy your book as
>soon as my local bookstore
>gets a copy.
>
> Here is the relevant section of code:
>
>ElseIf blnSelRow = True Then
> Dim i As Integer 'counter
> Dim rsSel As Recordset
> 'Set rsSel = New Recordset
> Dim intCount As Integer
>
> intCount = frmSimpleFind.dgdQuickResults.SelBookmarks.Count
-
>1
>
> ReDim aBookmarks(intCount)
>
> For i = 0 To intCount
> aBookmarks(i) = frmSimpleFind.dgdQuickResults.SelBookmarks(i)
>'populate array with selected rows
> rsSel.AddNew aBookmarks(i)
> Next i
>
> With rsSel 'loop through bookmarks
> Do Until .EOF
> For intCounter = 1 To intFieldCount
> varField = .Fields(intCounter - 1).Value 'add each field's value
> If IsNull(varField) Then 'handle null fields
> varField = "<null>"
> End If
> obWorkSheet.Cells(lngRowCount, intCounter).Value = varField
> Next intCounter
> lngRowCount = lngRowCount + 1
> If lngRowCount > varMaxRecs Then
> Exit Do
> End If
> .MoveNext
> Loop
> End With
> End If