SQL loop works but not when table is empty

Experts, I have some code in Microsoft Access that works fine when my table has data in it.
The problem is when this table is empty.

It errors out at: rs4.MoveFirst.

What is the cleanest way to end this look if Table TEMP_QSTNT_SELECTION has no records in it?

Thanks,

-dsg
Set rs4 = CurrentDb.OpenRecordset("Select QSTNT_SELECTION_PID From TEMP_QSTNT_SELECTION")
rs4.MoveFirst
Do Until rs4.EOF
        NewZ = NewZ + 1
        NewSelVer = rs4(0)
 
        sSQL3SW = "INSERT INTO TEMP_QSTNT_SELECTION_SWAP(OLD_PID,NEW_PID) VALUES(" & NewSelVer & "," & NewZ & ")"
         
        CurrentDb.Execute sSQL3SW, dbFailOnError
        NewSelVer = NewSelVer + 1
                   
rs4.MoveNext
Loop

Open in new window

dsg138Asked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Try this
Set rs4 = CurrentDb.OpenRecordset("Select QSTNT_SELECTION_PID From TEMP_QSTNT_SELECTION")
if not rs4.eof and not rs4.bof then
rs4.MoveFirst
Do Until rs4.EOF
        NewZ = NewZ + 1
        NewSelVer = rs4(0)
 
        sSQL3SW = "INSERT INTO TEMP_QSTNT_SELECTION_SWAP(OLD_PID,NEW_PID) VALUES(" & NewSelVer & "," & NewZ & ")"
         
        CurrentDb.Execute sSQL3SW, dbFailOnError
        NewSelVer = NewSelVer + 1
                   
rs4.MoveNext
Loop
end if
rs4.close
set rs4=nothing

Open in new window

0
 
Jeffrey CoachmanMIS LiasonCommented:
Check to see if the record set has any record first

Set rs4 = CurrentDb.OpenRecordset("Select QSTNT_SELECTION_PID From TEMP_QSTNT_SELECTION")
rs4.MoveFirst
If rs4.RecordCount<1 then
    'Msgbox  Then Exit sub perhaps?
end if
Do Until rs4.EOF
        NewZ = NewZ + 1
        NewSelVer = rs4(0)
 
        sSQL3SW = "INSERT INTO TEMP_QSTNT_SELECTION_SWAP(OLD_PID,NEW_PID) VALUES(" & NewSelVer & "," & NewZ & ")"
         
        CurrentDb.Execute sSQL3SW, dbFailOnError
        NewSelVer = NewSelVer + 1
                   
rs4.MoveNext
Loop
 

0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
dsg138,

In my above code it would be best to add error handling to close the recordset and set it to nothing if you exited the sub, if no recordsr were found.

I can't tell for sure, but you seem to be missing this basic code on the end of your sub:

'Recordset Cleanup code
rs4.Close
Set rs4=Nothing

;-)

JeffCoachman
0
 
dsg138Author Commented:
Thanks guys for your help...

Brandon, checking for rs4.eof and not rs4.bof seems to be the easiest way for me to check that the table is empty.

Jeff, I only included the Loop and didn't include the entire sub.  But you are correct... i wasn't closing the recordset and I didn't know I should have been setting the recordset for nothing.  

Thank you both for your help.
0
 
dsg138Author Commented:
Thanks for your help and the quick response!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.