[dba-SQLServer] Combo Boxes not showing Recordset

[David Emerson]

Hi Listers,

Access 2010 accdb, SQL 2014 backend.

I have a screen that gets recordsets from SQL using the following code:

    Dim rstTemp As ADODB.Recordset
    
    Set rstTemp = basRunDataObject("dbo.spfrmDistributorLossCodeFactor " &
Nz(Me!cboFilter, 0), adCmdText)
    Set Me.Recordset = rstTemp
    
    Set rstTemp = rstTemp.NextRecordset
    Set Me!cboFilter.Recordset = rstTemp
    
    Set rstTemp = rstTemp.NextRecordset
    Set Me!cboDistributorLossCodeID.Recordset = rstTemp
    
    rstTemp.Close
    Set rstTemp = Nothing

    Me.UniqueTable = "dbo.tblDistributorLossCodeFactor"
    Me.ResyncCommand = "SELECT dbo.tblDistributorLossCodeFactor.* FROM
dbo.tblDistributor INNER JOIN dbo.tblDistributorLossCode ON
dbo.tblDistributor.ID = dbo.tblDistributorLossCode.DistributorID INNER JOIN
dbo.tblDistributorLossCodeFactor ON dbo.tblDistributorLossCode.ID =
dbo.tblDistributorLossCodeFactor.DistributorLossCodeID WHERE
dbo.tblDistributorLossCodeFactor.ID = ?"
    Me.Requery


The relevant part of the stored procedure is as follows:

ALTER PROCEDURE [dbo].[spfrmDistributorLossCodeFactor] 
	(
		@DistributorID bigint
	)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT dbo.tblDistributorLossCodeFactor.*
	FROM dbo.tblDistributor INNER JOIN dbo.tblDistributorLossCode ON
dbo.tblDistributor.ID = dbo.tblDistributorLossCode.DistributorID
		INNER JOIN dbo.tblDistributorLossCodeFactor ON
dbo.tblDistributorLossCode.ID =
dbo.tblDistributorLossCodeFactor.DistributorLossCodeID
	WHERE dbo.tblDistributor.ID = @DistributorID OR @DistributorID = 0
	ORDER BY dbo.tblDistributor.Name, dbo.tblDistributorLossCode.Code,
dbo.tblDistributorLossCodeFactor.StartDate

	SELECT dbo.tblDistributor.ID, dbo.tblDistributor.Name AS Name,
dbo.tblDistributor.RegistryCode AS Code, CASE WHEN
dbo.tblDistributor.IsActive = 1 THEN 'Yes' ELSE 'No' END AS Active
	FROM dbo.tblDistributor
	ORDER BY dbo.tblDistributor.Name

	SELECT dbo.tblDistributorLossCode.ID,
dbo.tblDistributorLossCode.Code, dbo.tblDistributor.Name AS Distributor
	FROM dbo.tblDistributor INNER JOIN dbo.tblDistributorLossCode ON
dbo.tblDistributor.ID = dbo.tblDistributorLossCode.DistributorID
	WHERE dbo.tblDistributor.ID = @DistributorID OR @DistributorID = 0
	ORDER BY dbo.tblDistributorLossCode.Code, dbo.tblDistributor.Name

/*	SELECT dbo.tblDistributorLossCode.ID,
dbo.tblDistributorLossCode.Code, 'None' AS Distributor
	FROM dbo.tblDistributorLossCode
	ORDER BY dbo.tblDistributorLossCode.Code

*/


When the screen opens the combo box that uses the third record set has
nothing in it.  It shows the correct number of columns but there are no
headers or data.
If I replace the third record set with the remmed out recordset then the
combo box shows the headers and data.  The only difference in the statements
is that there is not a link to a second table.
The data for the screen and the first combo box show correctly.  I have
checked the records in the recordset by looping through them when the
recordset is retrieved and the correct records are there - they are not
showing in the combobox list.

The PK/FK ID fields are all bigint.  It tried changing them to int, and
numeric(18,0) but neither helped.

I have tried recreating the combobox but this didn't help.  I have also
decompiled, compacted and repaired.

This same behaviour happens in similar screens where the source for a combo
box includes more than 1 table.

Is this a peculiarity with SQL 2014?  I have successfully used this method
with SQL 2008.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand