Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 5th, 2003, 02:46 PM
Friend of Wrox
Join Date: Jun 2003
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?
Old September 5th, 2003, 03:39 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts

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.
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"
      While Not .EOF
         {whatever else you want to do if records are present}
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.

Old September 5th, 2003, 04:47 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts

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?

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

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