Error reading DB & loading data into an array

[BAMeyer]

I am retrieving an order number entered into an Excel spreadsheet (C3), reading the coitem table and loading the C/O line #'s into an array to be displayed in a drop-down box. The code fails after the 2nd read/loop. Any suggestions as to what I am doing wrong?

Dim rsRpt0 As New ADODB.Recordset
Dim sConn As String
Dim sSQL As String
Dim strOrderNum As String
Dim intOrderLn As Integer
Dim intOrderLnA(99) As Integer
Dim intX As Integer
Dim blnEOF As Boolean

Set rsRpt0 = New ADODB.Recordset

sConn = (Connect to the database)

strOrderNum = Range("C3").Value
intOrderLn = 0

bldEOF = False
Do While bldEOF = False
sSQL = " SELECT coitem.co-line " & _
" FROM symix.coitem " & _
" WHERE coitem.co-num = " & "'" & Trim(strOrderNum) & "'" & _
" AND coitem.co-line > " & intOrderLn

rsRpt0.Open sSQL, sConn, adUseClient, adLockOptimistic

If rsRpt0.RecordCount <> 0 Then
intOrderLn = rsRpt0.Fields("co-line").Value
intOrderLnA(intX) = rsRpt0.Fields("co-line").Value
intX = intX + 1
Else
bldEOF = True
End If
Loop

 

[BAMeyer]

I have revised the query to:

Dim rsRpt0 As New ADODB.Recordset
Dim sConn As String
Dim sSQL As String
Dim strOrderNum As String
Dim intOrderLnA(99) As Integer
Dim intX As Integer
Dim intLoopCnt As Integer

Set rsRpt0 = New ADODB.Recordset
sConn = (Connect to the database)
sSQL = " SELECT coitem.co-line " & _
" FROM symix.coitem " & _
" WHERE coitem.co-num = " & "'" & Trim(strOrderNum) & "'"

strOrderNum = Range("C3").Value

rsRpt0.Open sSQL, sConn, adUseClient, adLockOptimistic

If Not rsRpt0.EOF Then
Do While Not rsRpt0.EOF
intLoopCnt = intLoopCnt + 1
intOrderLnA(intX) = rsRpt0.Fields("co-line").Value
intX = intX + 1
rsRpt0.MoveNext
Loop
Else
MsgBox "Empty Recordset"
End If

I am no longer getting an error, but I'm only getting 1 record returned (I should be getting 4 records). Any suggestions would be appreciated!