Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 5th, 2003, 02:46 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Trap for empty recordset in a SP

I have an Access 2000 ADP that is based on data returned from a stored procedure on an SQL Server. The SP gets passed a parameter to find the specific record. If the user enters a value that is not in the database, nothing shows up on my form (which is correct) but I need to tell the user that there is no record found for that search.

How do I tell or trap when the stored procedure returns an empty recordset?
__________________
Mitch
Reply With Quote
  #2 (permalink)  
Old September 5th, 2003, 03:39 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

One way to test for an empty recordset is to see if the EOF and BOF properties are both True. If not the recordset contains at least one record.
Code:
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSP As String

Set cnn = "{connection string}"  ' You must define the connection string yourself for SQL Server data

Set rst = New ADODB.Recordset
strSP = "{name of the stored procedure}"

With rst
   .Open strSP, cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProcedure
   If .BOF And .EOF Then
      MsgBox "Recordset contains no data"
   Else
      While Not .EOF
         {whatever else you want to do if records are present}
      Wend
   EndIf
   .Close
End With
Set rst = Nothing
Set cnn = Nothing
You may have to use different parameters for the .Open method. adOpenForwardOnly is for a single pass recordset and adLockReadOnly is for a read-only recordset.


Rand
Reply With Quote
  #3 (permalink)  
Old September 5th, 2003, 04:47 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I thought of that, then I went back and what I am doing is setting the form's record source to that stored procedure (late binding, I think it is called) so I am not opening the recordset in code. Which now changes my original question I think.

Basically right after I set the form's recordsource to the SP I get the error and a blank form (all my fields go away, it is really strange).

Is there some sore of form property to catch this?
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordset Empty Check Coby Access VBA 2 April 27th, 2007 04:09 PM
Recordset empty when opening querydef DeAnzaJig Access VBA 3 November 14th, 2005 09:31 AM
Empty recordset = redirect frankriedel PHP How-To 2 November 12th, 2005 04:49 AM
Executing a SP for each recordset of a Table anandham SQL Server 2000 1 October 19th, 2005 06:34 PM
Can a SP run another SP as sa? dbradley SQL Server 2000 0 July 17th, 2003 08:35 AM



All times are GMT -4. The time now is 10:25 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.