Actual problem in SQL Statement?

Applek
Applek used Ask the Experts™
on
HI.
I have no idea what is my actual problem in this SQL statement if I would like to retrive all records which fulfil the particular condition:

TblMenu:

Field1     UserID
menu1      123
menu2      123
menu3      123

SQL:
Select Field1 from TblMenu where UserID = '"& ID & "'

In VB, it only retrive only one records as i used the loop/for loop to retrieve the data. But i tested it in Query analyser, it retrieves all record where ID = 123.

snippet code:

rs.Open strSQL, cn
For i = 0 To intNo_Item - 1
  MsgBox i
  str = rs.Fields("Field1")
  MsgBox str
next

Any idea?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Looks like you forgot to move the recordset pointer to the next record ?

Correction :

rs.Open strSQL, cn
For i = 0 To intNo_Item - 1
 MsgBox i
 str = rs.Fields("Field1")
 MsgBox str
 rs.movenext
next

By right, you should be checking against the EOF property of the Recordset object :

Do While Not RS.EOF
   Msgbox RS.Fields("Field1")
   RS.MoveNext
Loop

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial