Solved

How to View Recordsets in Access Form List Box

Posted on 2014-01-15
14
798 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:cartb4horse
  • 7
  • 4
  • 3
14 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 400 total points
Comment Utility
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:cartb4horse
Comment Utility
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 84
Comment Utility
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
 
LVL 1

Author Comment

by:cartb4horse
Comment Utility
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 84
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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:cartb4horse
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Author Comment

by:cartb4horse
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 84
Comment Utility
Try this instead:

SET OpenMyRecordset = rstCompound
0
 
LVL 1

Author Comment

by:cartb4horse
Comment Utility
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 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 100 total points
Comment Utility
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:cartb4horse
Comment Utility
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:cartb4horse
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
MS Access query date sorting question 7 11
SQL 2016 Setup - Connectivity Issues 4 12
Azure SQL DB? 3 13
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now