Loop EOF errors

[diannk]

Morning all,

I have code that runs a sql statement to grab data for my worksheet, the sql statement runs fine but if it returns nothing (because of an empty table) it gives an error and ends. I need it to just skip to the next sql. Here's the code....any help would be greatly appreciated.

Please Login or Register  to view this content.

Thanks in Advance,
Di

[Leith Ross]

Hello diannk,

When using the SQL Select statement, the Table name is not used as a prefix. Only the column names are used. The From clause specifies the Table name.

Please Login or Register  to view this content.

When moving or looping through the record set, if the current record is the EOF then calling MoveNext will cause an error. You should always check that the recordset isn't empty before executing any move statement or loop to prevent generating an error. The recordset is empty if both BOF and EOF are True or if the recordset property RecordCount = 0. When referencing the Fields collection, you need to use the Item property to return a single object from the collection.

Please Login or Register  to view this content.

Make these changes throughout your code, and it should run.

Sincerely,
Leith Ross

[T-J]

To test for an empty recordset use

Please Login or Register  to view this content.

I see your code already has the test in one part of the but it needs rearranging to the above logic.