Solved

How to View Recordsets in Access Form List Box

Posted on 2014-01-15
14
814 Views
Last Modified: 2014-01-17
I’m using the NEXTRECORDSET method to view (debug.print) the records for up to three separate recordsets generated from a SQL stored procedure.  I'm able to successfully see the results in my debug window, but how do I now see those results in a List Box or 3 list boxes of an Access form?

Here is the function:
Public Function OpenMyRecordset(rstCompound As ADODB.Recordset, strSQL As String, _
    Optional rrCursor As rrCursorType, _
    Optional rrLock As rrLockType, Optional bolClientSide As Boolean) As ADODB.Recordset

    If con.STATE = adStateClosed Then
        con.ConnectionString = "ODBC;Driver={SQL Server};Server=vnysql;DSN=RecordsMgmt_SQLDB;UID=DMP;Trusted_Connection=Yes;DATABASE=RecordsManagementDB;"
        con.Open
    End If

    
    Set rstCompound = New ADODB.Recordset
    With rstCompound
        .ActiveConnection = con
            .CursorLocation = adUseClient
        .CursorType = IIf((rrCursor = 0), adOpenDynamic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockOptimistic, rrLock)
        .Open strSQL
    End With
    
    ' Display results from each recordset
    intCount = 1
    Do Until rstCompound Is Nothing
        Debug.Print "Contents of recordset #" & intCount
        
        Do Until rstCompound.EOF
            Debug.Print rstCompound.Fields(0), rstCompound.Fields(1)
            rstCompound.MoveNext
        Loop
        
        Set rstCompound = rstCompound.NextRecordset
        intCount = intCount + 1
    Loop

End Function

Open in new window

This is the form procedure that calls the function:
Private Sub cmdRun_Click()
'On Error Resume Next

    Dim strSQL As String
        
    'Stored procedure + parameters called from form
    strSQL = "Exec spSQL_SearchDatabase " & "'" & Me.txtTables & "'" & _
        ", " & "'%" & Me.txtSearchTerm & "%'"

    OpenMyRecordset rstCompound, strSQL

    Set Me.lstResults1.Recordset = rstCompound
    
    'debug - view procedure
    Me.lblQuery.Caption = strSQL
    Me.Repaint
    
End Sub

Open in new window

0
Comment
Question by:Darlyne Pitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 37

Accepted Solution

by:
PatHartman earned 400 total points
ID: 39783932
You will probably have to save the recordsets to a table.  You can use a single table or three.  If you use three, you'll need a union query to get the separate data back into a single list.

If the recordsets are small, you could try building a value list as a text string and using that as the RowSource.

If you split your sp into three separate sp, you could use a union of three pass-through queries and save yourself some effort.  Or, since you want one recordset, union them on the server and return only a single recordset from the sp.
0
 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39784319
I shouldn't have to save the recordsets to a table.  I was able to successfully pass the result set to the listbox in the form by creating a statement for each recordset (see code below) but it wasn't as clean and I was getting an "Object Variable or With Block Variable Not Set", every time one of the values I searched for was in the second or third tables.

 Set rs1 = New ADODB.Recordset
    With rs1
        .ActiveConnection = con
            .CursorLocation = adUseClient
        .CursorType = IIf((rrCursor = 0), adOpenDynamic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockOptimistic, rrLock)
        .Open strSQL
   End With
   
    Do Until rs1.EOF
       Debug.Print rs1.Fields(0), rs1.Fields(1)
            rs1.MoveNext
    Loop
    
   Set rs2 = rs1.NextRecordset
    Do Until rs1.EOF
       Debug.Print rs2.Fields(0), rs2.Fields(1)
            rs2.MoveNext
    Loop
        
   Set rs3 = rs2.NextRecordset
   
   Do Until rs3.EOF
       Debug.Print rs3.Fields(0), rs3.Fields(1)
            rs3.MoveNext
    Loop 

Open in new window

Private Sub cmdRun_Click()
'On Error Resume Next

    Dim strSQL As String
        
    'Stored procedure + parameters called from form
    strSQL = "Exec spSQL_SearchDatabase " & "'" & Me.txtTables & "'" & _
        ", " & "'%" & Me.txtSearchTerm & "%'"

    OpenMyRecordset rs1, rs2, rs3, strSQL

    Set Me.lstResults1.Recordset = rs1
    Set Me.lstResults2.Recordset = rs2
    Set Me.lstResults3.Recordset = rs3

End Sub

Open in new window

0
 
LVL 85
ID: 39785104
So your SP returns up to 3 recordsets?

Does your code here:
Set Me.lstResults1.Recordset = rs1
Set Me.lstResults2.Recordset = rs2
Set Me.lstResults3.Recordset = rs3

Open in new window

show all 3 recordsets in the listbox? If so, you could perhaps use the NextRecordset method to return a SINGLE rst from your SP, and then iterate through the returned recordsets and add them to a listbox.
I was getting an "Object Variable or With Block Variable Not Set", every time one of the values I searched for was in the second or third tables.
How were you searching for a value? Can you show that code?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39785346
Yes Scott, the SP returns up to 3 recordsets and the code snippet above shows all three recordsets in the listbox.  I'm not sure how I would use the NextRecordset method to return a single recordset from my SP.  I thought I was already doing that in my function.  My SP searches for a value in all three tables of my SQL database.  Here is the code:
EXEC spSQL_SearchDatabase @Tablenames = '',@SearchStr  = '%ascii%'

Open in new window

USE RecordsManagementDB
GO

IF OBJECT_ID('spSQL_SearchDatabase','P') IS NOT NULL
	DROP PROCEDURE spSQL_SearchDatabase
GO
CREATE PROCEDURE spSQL_SearchDatabase
 @Tablenames VARCHAR(500)
,@SearchStr NVARCHAR(60)
,@GenerateSQLOnly Bit = 0
AS


	SET NOCOUNT ON

	DECLARE @MatchFound BIT 
 
    SELECT @MatchFound = 0
    
	DECLARE @CheckTableNames Table
	(
	Tablename sysname
	)

	DECLARE @SQLTbl TABLE
	(
	 Tablename		SYSNAME
	,WHEREClause    VARCHAR(MAX)
	,SQLStatement   VARCHAR(MAX)
	,Execstatus		BIT 
	)

	DECLARE @sql VARCHAR(MAX)
	DECLARE @tmpTblname sysname
	DECLARE @ErrMsg VARCHAR(100)

	IF LTRIM(RTRIM(@Tablenames)) IN ('' ,'%')
	BEGIN

		INSERT INTO @CheckTableNames
		SELECT Name
		  FROM sys.tables
	END
	ELSE
	BEGIN

		SELECT @sql = 'SELECT ''' + REPLACE(@Tablenames,',',''' UNION SELECT ''') + ''''

		INSERT INTO @CheckTableNames
		EXEC(@sql)

	END
	
	IF NOT EXISTS(SELECT 1 FROM @CheckTableNames) 
    BEGIN 
         
        SELECT @ErrMsg = 'No tables are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
 
    END  
	
	INSERT INTO @SQLTbl
	( Tablename,WHEREClause)
	SELECT QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME),
			(
				SELECT '[' + SC.name + ']' + ' LIKE ''' + @SearchStr + ''' OR ' + CHAR(10)
				  FROM SYS.columns SC
				  JOIN SYS.types STy
					ON STy.system_type_id = SC.system_type_id
				   AND STy.user_type_id =SC.user_type_id
				 WHERE STY.name in ('varchar','char','nvarchar','nchar')
				   AND SC.object_id = ST.object_id
				 ORDER BY SC.name
				FOR XML PATH('')
			)
	  FROM  SYS.tables ST
	  JOIN @CheckTableNames chktbls
				ON chktbls.Tablename = ST.name 
	  JOIN SYS.schemas SCh
	    ON ST.schema_id = SCh.schema_id
	 WHERE ST.name <> 'SearchTMP'
      GROUP BY ST.object_id, QUOTENAME(SCh.name) + '.' + QUOTENAME(ST.NAME) ;

	  UPDATE @SQLTbl 
         SET SQLStatement = 'SELECT * INTO SearchTMP FROM ' + Tablename + ' WHERE ' + substring(WHEREClause,1,len(WHEREClause)-5) 

	  DELETE FROM @SQLTbl
	   WHERE WHEREClause IS NULL
	
	WHILE EXISTS (SELECT 1 FROM @SQLTbl WHERE ISNULL(Execstatus ,0) = 0)
	BEGIN

		SELECT TOP 1 @tmpTblname = Tablename , @sql = SQLStatement
		  FROM @SQLTbl 
		 WHERE ISNULL(Execstatus ,0) = 0

		 

		 IF @GenerateSQLOnly = 0
		 BEGIN

			IF OBJECT_ID('SearchTMP','U') IS NOT NULL
				DROP TABLE SearchTMP
			EXEC (@SQL)

			IF EXISTS(SELECT 1 FROM SearchTMP)
			BEGIN
				SELECT	Tablename=@tmpTblname, * FROM SearchTMP
				SELECT @MatchFound = 1
			
			END

		 END
		 ELSE
		 BEGIN
			 PRINT REPLICATE('-',100)
			 PRINT @tmpTblname
			 PRINT REPLICATE('-',100)
			 PRINT replace(@sql,'INTO SearchTMP','')
		 END

		 UPDATE @SQLTbl
		    SET Execstatus = 1
		  WHERE Tablename = @tmpTblname

	END
	
	IF @MatchFound = 0  
    BEGIN 
        SELECT @ErrMsg = 'No Matches are found in this database ' + DB_NAME() + ' for the specified filter' 
        PRINT @ErrMsg 
        RETURN 
    END 
		
	SET NOCOUNT OFF

go

Open in new window

0
 
LVL 85
ID: 39785399
I'm not sure how I would use the NextRecordset method to return a single recordset from my SP.
I think we're getting off on a tangent. Let's start at the beginning:

Your OpenMyRecordset doesn't return a value, even though the "return value" of the function is an ADODB.Recordset. The correct way to call that would be like this:

Dim rst As New ADODB.Recordset
Set rst = OpenMyRecordset(etc etc)

In OpenMyRecordset, you'd have to provide that return value. At the end of the function add this:

OpenMyRecordset = rstCompound

So when you finish that call, the "rst" variable should be set to a valid ADODB.Recordset.

If you make those changes and run the code, does your Listbox show any values now (assuming your search criteria would actually return values, of course)?

If so, and if the returned "rst" object would have multiple recordsets, you could do something like this:

Do Until rst Is Nothing
  Dim rstRS As new ADODB.Recordset
  Set rstRS = rst.NextRecordset
  YourListBox.Rowsource = rstRS
Loop

I've never used NextRecordset in this manner, so I have no idea if it will correctly show your results. As Pat mentioned earlier, it's generally better to loop through those returned recordsets and create a Value List to use with your Listbox.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39785506
I find that I always have less trouble if I do things the "Access" way.  And, believe it or not, even with high row-count tables, I rarely have to resort to pass-through queries let alone stored procedures,  What you are doing is quite out of the ordinary.  Is there a reason?  What is wrong with creating a view in SQL server and simply binding the RowSource to that view.  You still get to use your sp.  I also don't see the advantage of having the sp create three separate recordsets.  Are you really doing such an intense process that this is necessary?  Couldn't you go the extra step and produce a single recordset.  If the sp produces a single recordset, you can use a single pass-through query to retrieve it and bind the RowSource to the pass-through query.

I've written my million lines of code and I don't need the practice so I never write code when I can use a query to do the job.
0
 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39785999
Scott: I'll do a rewrite and let you know how it turns out.
Pat:  The reason I'm getting 3 separate recordsets because there are three tables that I'm querying.  The SP doesn't create three separate recordsets, it searches the data in three separate tables and provides the results for up to three tables.  I wanted to take those results and produce a single recordset  that I can easily reference, but I was having trouble creating a view or JOINing those tables because of the variation in fields and datatypes.
0
 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39786069
Scott, when I add
OpenMyRecordset = rstCompound

Open in new window

to the end of my function I get an "Invalid Use of Property" error on that line of code.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39786319
Perhaps you could use CAST to change the data types to make them consistent.  Then you can union the three recordsets to get one list if that is what you need.
0
 
LVL 85
ID: 39786355
Try this instead:

SET OpenMyRecordset = rstCompound
0
 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39786484
Okay, that worked so I'm not getting the error, but I'm also not seeing any results in my listbox.  I think my next step will be to either try creating a value list as both you and Pat suggested, or going back to the database in SQL and try to UNION the result sets into one view; incorporating the CAST method as Pat suggested.
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
ID: 39786741
As I mentioned earlier, I'm not sure the methods you're trying will work. I've never worked with NextRecordset in this manner, so I don't know what'll happen when you do.

You can try this:

Comment out code AFTER the "End With" line in your OpenMyRecordset function EXCEPT for the "Set OpenMyRecordset= rstCompound" line - in other words, all the loop code you have in there. I belive what you're getting is the LAST recordset from that compound recordset.

Now try to set the Listbox's RowSource as I suggested earlier:

Dim rst As New ADODB.Recordset
Set rst = OpenMyRecordset(blah blah)

Do Until rst Is Nothing
  MyListbox.Recordset = rst
  Set rst = rst.NextRecordset
Loop

Again, I'm not sure this'll work, and it's not something I'd be comfortable leaving in my code. As Pat suggested earlier, you'd be much better off building up a Value List and using that as the RowSource.
0
 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39786860
Yea, that didn't work either.  I got nothing.  Also not comfortable with it so I will try Pat's suggestion and let you know how it goes.  Thanks for all of these suggestions it was very helpful.
0
 
LVL 1

Author Comment

by:Darlyne Pitt
ID: 39789451
Thanks Pat and Scott for all your help.  I'm going to go ahead and close this question.  I think I got Pat's suggestion to work.
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

728 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