My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Proper Way To Exit If Recordset Empty
|
|
 Mar 18 2012, 04:04 AM
Post
#1
|
|
|
UtterAccess Ruler Posts: 1,291 From: Kampala,Uganda The Pearl of Africa  |
Hello UA
I want to know the proper way to exit if a recordset turns out to be empty. examples can be automation of excel. I have been using Exit sub There is always a test like CODE ''''test if recordset not empty If not rst.EOF Then '''''code to export data here Else MsgBox " No Records to Export",vbInformation Exit Sub End if rst.close ExitHere: '''''assuming these objects below set rst=Nothing set objwkb=Nothing set objSheet=Nothing objExcel.Quit set objExcel=Nothing Exit Sub my question is that is not better to use " GoTo ExitHere " in the else part ? using example above CODE Else MsgBox " No Records to Export",vbInformation GoTo ExitHere End if Ronald |
 |
 
|
|
Mar 18 2012, 04:17 AM
Post
#2
|
|
|
UtterAccess Ruler Posts: 1,090 |
That's good enough, only test your objExcel if for some reason an error occurs and the object is not instantiated.
If (objExcel Is Nothing) = False Then objExcel.Quit End If ... rest of the code |
|
|
|
|
Mar 18 2012, 04:23 AM
Post
#3
|
|
|
UtterAccess Ruler Posts: 1,291 From: Kampala,Uganda The Pearl of Africa |
QUOTE (arnelgp @ Mar 18 2012, 12:17 PM)  That's good enough, only test your objExcel if for some reason an error occurs and the object is not instantiated. If (objExcel Is Nothing) = False Then objExcel.Quit End If ... rest of the code Hello what is good enough in this case? I have just tested and using Exit sub in the Else bit Excel remains running But if i use GoTo ExitHere. Excel quits Ronald |
|
|
|
|
Mar 18 2012, 04:49 AM
Post
#4
|
|
|
UtterAccess Ruler Posts: 1,090 |
Im referring to your second code.
CODE Else
MsgBox " No Records to Export",vbInformation GoTo ExitHere End if |
|
|
|
|
Mar 18 2012, 11:19 AM
Post
#5
|
|
|
UtterAccess VIP Posts: 18,396 From: Oklahoma City, Oklahoma |
Ronald,
I would not use the Exit Sub. You need to go to the ExitHere to do the housekeeping/clean up. I would reorganize the ode to avoid the need for the Exit Sub or GoTo I would use something like this: CODE ''''test if recordset not empty If not rst.EOF Then '''''code to export data here objExcel.Quit set objwkb=Nothing set objSheet=Nothing set objExcel=Nothing Else MsgBox " No Records to Export",vbInformation End if rst.close set rst=Nothing Exit Sub |
|
|
|
|
Mar 18 2012, 11:54 AM
Post
#6
|
|
|
UtterAccess Ruler Posts: 1,291 From: Kampala,Uganda The Pearl of Africa |
QUOTE (HiTechCoach @ Mar 18 2012, 07:19 PM) Ronald, I would not use the Exit Sub. You need to go to the ExitHere to do the housekeeping/clean up. I would reorganize the ode to avoid the need for the Exit Sub or GoTo I would use something like this: CODE ''''test if recordset not empty If not rst.EOF Then '''''code to export data here objExcel.Quit set objwkb=Nothing set objSheet=Nothing set objExcel=Nothing Else MsgBox " No Records to Export",vbInformation End if rst.close set rst=Nothing Exit Sub Thank HiTechCoach' I now know better I have been using Exit Sub.... Ronald |
|
|
|
|
Mar 18 2012, 12:15 PM
Post
#7
|
|
|
UtterAccess VIP Posts: 18,396 From: Oklahoma City, Oklahoma |
Ronald,
You're welcome. Glad we could assist. |
|
|
|
|
Mar 23 2012, 10:54 AM
Post
#8
|
|
|
UtterAccess VIP Posts: 1,888 From: UK |
it is often tidier to use a single exit point for a sub. you can include all housekeeping in there. close files, recordsets etc. It won't matter on trivial subs,
but adding goto exitpoint, rather than just exit sub is a good habit to get into. |
|
|
|
|
Apr 3 2012, 10:26 AM
Post
#9
|
|
|
UtterAccess Enthusiast Posts: 50 |
I have always used
CODE If not rst.eof and not rst.bof It is possible for the point to be sat at either bof or eof and still contain records, it is however impossible to be sat at eof and bof at the same time with records inbetween |
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 22nd May 2013 - 11:49 PM |