asked on

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

Jeffrey Coachman

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

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dsg138

ASKER

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.

dsg138

ASKER

Thanks for your help and the quick response!