VBA - if SELECT THEN SELECT

A

AJ

I am very new to VBA and access and have a question regarding a SELECT
statement. I am going to run a select (recordset - so I can loop through and
process each record) but on my SELECT I want to make sure I return at least
20 rows, otherwise I am going to change my WHERE clause to include more rows.
Is there an easy way to do this or do I need to just do an inital SELECT
COUNT(*) and then decide which WHERE clause to use??
Do you have an example I can look at?
Thank you in advance!
AJ
 
T

Tom van Stiphout

On Fri, 14 Dec 2007 17:14:00 -0800, AJ <[email protected]>
wrote:

I would not first run the Count query. Rather, after you open your
recordset, write this:
(assuming your recordset variable is 'rs')
rs.MoveLast
rs.MoveFirst
if rs.RecordCount < 20 then
Msgbox "Aarrrccchhh, not enough records!"
else
'Normal processing
end if

The purpose of the MoveLast/MoveFirst pair is to get an accurate count
of records. Access "lazily loads" a recordset and the RecordCount
property can be inaccurate if you don't include these two lines.

-Tom.
 
Top