[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL loop works but not when table is empty

Posted on 2009-02-10
5
Medium Priority
?
1,065 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:dsg138
  • 2
  • 2
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 23606036
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
ID: 23606085
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 600 total points
ID: 23606250
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
 

Author Comment

by:dsg138
ID: 23611713
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
 

Author Closing Comment

by:dsg138
ID: 31545322
Thanks for your help and the quick response!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question