HELP! Select behaves differently inside code and in Query builder

[Laurel]

I have a select statement as follows.

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND
Student_ID <> -1

I use it in a bit of code in this way (as_where works out to be the clause
above.)

li_i = fncRefreshQuery("qryClassSummaryDetailBase",
"qryClassSummaryDetail")
ls_temp = "Select * from qryClassSummaryDetail " & As_where
Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)

For a couple of years, this has worked fine. Now, suddenly, it is returning
only one row when executed in the code above. It is the first row of the
many that I get if I execute the same statement (cut and paste from the
debugger) in the query builder/SQL view. When I execute the statement
there, I get all of the students where class_code = 'ML2'. This is true
whether I use qryClassSummaryDetail or qryClassSummaryDetailBase.

I haven't done any editing of this code recently, and it's been running fine
since the start of school.

What's happening?

 

[John Vinson]

For a couple of years, this has worked fine. Now, suddenly, it is returning
only one row when executed in the code above.

A Recordset does indeed return only one row, to start with; code
execution resumes immediately, before the entire recordset (which
might be of any size, and might take a long time to populate) is
filled.

If you need to see all the records in the recordset, put a line

rs.MoveLast

after the Set line.

John W. Vinson[MVP]

 

[Laurel]

A Recordset does indeed return only one row, to start with; code
execution resumes immediately, before the entire recordset (which
might be of any size, and might take a long time to populate) is
filled.

If you need to see all the records in the recordset, put a line

rs.MoveLast

li_i = fncRefreshQuery("qryClassSummaryDetailBase",
"qryClassSummaryDetail")
ls_temp = "Select * from qryClassSummaryDetail " & As_where
Set rstClassDetail = CurrentDb.OpenRecordset(ls_temp)
rstClassDetail.MoveLast

I did this and it causes a "No current record" error.
Interestingly, I've found that other forms of the SELECT statement work OK,
even though they may return the same number of records. The forms that work
OK inside the code are as follows. Note that the first Select statement
returns the same number of records as the Select statement that's returning
only one record by the time the next code begins.

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND
[Score_Date] Between #9/10/2006# and #9/20/2006# AND Student_ID <> -1

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND
[Score_Date] = #9/19/2006# AND Student_ID <> -1

Select * from qryClassSummaryDetail WHERE [class_code] = 'ML2' AND [Week_of]

 

[Laurel]

I finally solved this by "compact and repair"ing the backend database.