recordset.movefirst "Rowset position cannot be restarted"

Question

I have a function that runs a stored procedure that returns only a single row and column (so one result).

I'm trying to get that one result into a variable so I can return it. I'm trying to use recordset.MoveFirst but I get the "Rowset position cannot be restarted." error. I tried just removing it, since I only have one result, but I then get an overflow. My statement looks like this:

If recordset.EOF = False Then
    recordset.MoveFirst
    temp = rs!ID 
 End IF

temp is an integer. I've checked the stored procedure to make sure it only returns the single result, and it does. Am I doing something wrong? Is there a better way to pass the result into a variable? It's possible the recordset is forward only (which means it's read only?) but I can't seem to find an answer as to how to fix that.

Answer 1

There is usually no reason to MoveFirst if you have not previously navigated the record set.

The overflow is unrelated to the database code and is caused by rs!ID not fitting in a VBA integer (16 bit) so make temp a Long instead (32 bit) and remove MoveFirst.