Recordset becomes NULL after first read/use

[hehe panda]

I have Access as FrontEnd and SQL server as Backend. After I call a stored procedure on the SQL server, I try to read the fields in the recordset. However, one of the column can only be used once. After the first use/read, it becomes NULL for no reason.

-----VBA in Access------
strSQL = "EXEC SP_GetDetails '" & strInput & "'"
Set recSQL = ConnSQL.Execute (strSQL)

Do While Not recSQL.EOF
Debug.Print "Customer1 = " & recSQL!Customer
Debug.Print "Customer2 = " & recSQL!Customer
...
Loop

-----Stored Procedure in SQL Server-----
CREATE PROCEDURE SP_GetDetails
@RQFNumInput varchar(20)
As
select Customer From List where Number = @RQFNumInput Order by Date Desc

-----------------------------------------
The problems is the first output gives the value and the second output shows nothing.
Customer1 = My Customer
Customer2 =

This is the same to the following thread. However, there was not answer to it.
http://bytes.com/topic/access/answers/198611-ado-recordset-field-becoming-null

Please help!

[ck9663]

Execute your SP in a query window and look at the result. It is probably because there is only one record in your table that satisfies your where clause.

Good Luck!!!

~~ CK

[hehe panda]

I did that and it has two records. The second one's value is NULL though. Will the recordset go to the second record automatically without reaching the row of "recSQL.MoveNex t? I was testing the same row in both debug.print.

[ck9663]

It should not. But Access forum will be help you more on that one...

Good Luck!!!

~~ CK