How to locate the end of a recordset
 

"CLamar" wrote:

How do you find the end of a recordset, so it can be used in a loop. I tried
the EOF command but it wont enter the loop when i use it. Here is an example
of the code. The second for loop with the * is the one giving me problems

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT * FROM Table1"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Sheets("DataTable").Range("A1").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next

Sheets("DataTable").Range("A1").Offset(1, 0).CopyFromRecordset
Recordset

** For Row = 2 To Recordset.EOF
Sheets("Sheet1").LstBatchnum.AddItem
Sheets("DataTable").Cells(Row, 1)
Next Row
End With

Thanks

How to locate the end of a recordset
 

Hi

You can use recordset..RecordCount to find the number of rows in the
recordset. But beware, in some versions of office this does not give the
right count until you have moved to the end of the recordset, so preceed it
with recordset.MoveLast

HTH
--
Tony Green


"CLamar" wrote:

"CLamar" wrote:

How do you find the end of a recordset, so it can be used in a loop. I tried
the EOF command but it wont enter the loop when i use it. Here is an example
of the code. The second for loop with the * is the one giving me problems

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT * FROM Table1"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Sheets("DataTable").Range("A1").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next

Sheets("DataTable").Range("A1").Offset(1, 0).CopyFromRecordset
Recordset

** For Row = 2 To Recordset.EOF
Sheets("Sheet1").LstBatchnum.AddItem
Sheets("DataTable").Cells(Row, 1)
Next Row
End With

Thanks

How to locate the end of a recordset
 

Thanks that sounded like a great idea and it should have worked but it
doesnt. It doesnt give me an error when I use the RecordCount but it doesnt
enter the loop. When I use .MoveLast it gives me the error "Expected
Function or Variable". So can you tell me if i am doing something wrong or
give me another suggestion

Thanks CLamar


"ADG" wrote:

Hi

You can use recordset..RecordCount to find the number of rows in the
recordset. But beware, in some versions of office this does not give the
right count until you have moved to the end of the recordset, so preceed it
with recordset.MoveLast

HTH
--
Tony Green


"CLamar" wrote:

"CLamar" wrote:

How do you find the end of a recordset, so it can be used in a loop. I tried
the EOF command but it wont enter the loop when i use it. Here is an example
of the code. The second for loop with the * is the one giving me problems

Set Recordset = New ADODB.Recordset
With Recordset
Src = "SELECT * FROM Table1"
.Open Source:=Src, ActiveConnection:=Connection

For Col = 0 To Recordset.Fields.Count - 1
Sheets("DataTable").Range("A1").Offset(0, Col).Value =
Recordset.Fields(Col).Name
Next

Sheets("DataTable").Range("A1").Offset(1, 0).CopyFromRecordset
Recordset

** For Row = 2 To Recordset.EOF
Sheets("Sheet1").LstBatchnum.AddItem
Sheets("DataTable").Cells(Row, 1)
Next Row
End With

Thanks

How to locate the end of a recordset
 

Hi CLamar,

I think since you're reading the spreadsheet instead of the recordset, you
should be referencing a count of the rows of records.<IMO Try changing the
loop start to:

For Row = 2 To Cells(Rows.Count, 1).End(xlUp).Row

HTH
Regards,
Garry