sql statement doesn't return all recs

[RUdebugged]

Hello - forgive me if this does not constitute a new thread. It's my first post.

I am calling a subroutine from another sub, and creating a recordset from a simple select sql statement. What's completely baffling is why putting in the identical where condition twice works to get all 250 records, while putting in the where statement once, like you would in a query, returns just the first record. I did check the syntax in a new query (SQL view), and the 250 recs are returned with a single condition.

This returns only 1 record:

Set rs1 = mydb.OpenRecordset("Select Station from tblList where Station ='arf';")

Whereas this returns 250 records:

Set rs1 = mydb.OpenRecordset("Select Station from tblList where Station ='arf' or Station = 'arf';")

Any ideas why the sql statement works differently in the subroutine or more specifically why the two Station = 'arf' conditions are necessary? Another little tidbit is that I get the 250 'arf' records with any two combinations in the condition, such as Station ='arf' or Station = 'car'.

 

[jdraw]

No idea why they should be different.
I don't understand why you would have

where Station ='arf' or Station = 'arf';")

If you have

where Station ='arf' or Station = 'car';")

you will get all the records where Station = 'arf' plus all those where Station='car', which is to be expected.

 

[MarkK]

When you open a recordset in code it is not fully populated immediately. Do record navigation in the recordset object to ensure it populates. To fully poulate it, fully navigate it...

   Set rs1 = mydb.OpenRecordset("Select Station from tblList where Station ='arf';")
   with rs1
      if not .eof then
         .movelast
         .movefirst
      end if
      msgbox .recordcount
      .close
   end with

The recordset does not waste time loading data over the network until you explicitly request it.
Mark

 

[RUdebugged]

When you open a recordset in code it is not fully populated immediately. Do record navigation in the recordset object to ensure it populates. To fully poulate it, fully navigate it...

The recordset does not waste time loading data over the network until you explicitly request it.
Mark

Oh thank you MarkK. Now I feel silly. I knew that but uh... oh, I don't know - long day already, I guess. Still, pretty weird that putting in twice seemed to populate it without the .MoveLast.