I'm trying to use a variant array in a For next statement and I can't
get the UBound of the variant array.

Any time I run this, l comes back as 0. But if i transpose the array
and add it to a listbox on a form, I get the data I'm looking for. How
does an array have elements contained within, but have a UBound of 0?

Code posted below

' Constant declarations
Const dbPath = "C:\XXXXXXXXXXX.mdb"
Const sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbPath & ";"

' Variable declarations
Dim sTitle As String
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcArray As Variant
Dim sArray() As String
Dim sSQL As String
Dim j As Long, l As Long

' Get batch title
sTitle = frmSelection.cboBatch.Value

' Build the SQL query.
sSQL = "SELECT [tbl RBT Ladder Batches].PrnNo " _
& "FROM [tbl RBT Ladder Batches] " _
& "WHERE ((([tbl RBT Ladder Batches].Title) = """ & sTitle
& """)) " _
& "ORDER BY [tbl RBT Ladder Batches].PrnNo;"

'Open connection to the database
cnt.Open sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows()

'Get the number of elements in the array
l = UBound(rcArray)

Does .GetRows return a 0-based array? If so, it looks like you're retrieving
a single row in your recordset.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


wrote in message
oups.com...
I'm trying to use a variant array in a For next statement and I can't
get the UBound of the variant array.

Any time I run this, l comes back as 0. But if i transpose the array
and add it to a listbox on a form, I get the data I'm looking for. How
does an array have elements contained within, but have a UBound of 0?

Code posted below

' Constant declarations
Const dbPath = "C:\XXXXXXXXXXX.mdb"
Const sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbPath & ";"

' Variable declarations
Dim sTitle As String
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rcArray As Variant
Dim sArray() As String
Dim sSQL As String
Dim j As Long, l As Long

' Get batch title
sTitle = frmSelection.cboBatch.Value

' Build the SQL query.
sSQL = "SELECT [tbl RBT Ladder Batches].PrnNo " _
& "FROM [tbl RBT Ladder Batches] " _
& "WHERE ((([tbl RBT Ladder Batches].Title) = """ & sTitle
& """)) " _
& "ORDER BY [tbl RBT Ladder Batches].PrnNo;"

'Open connection to the database
cnt.Open sConnect

'Open recordset and copy to an array
rst.Open sSQL, cnt
rcArray = rst.GetRows()

'Get the number of elements in the array
l = UBound(rcArray)