Subform connected to stored procedure cannot be sorted twice...

[Steven Kogan]

I have a subform connected to a stored procedure in SQL Server. I can sort it once, but when I try to sort it a second time I get an error 'Data provider could not be initialized'.

The code to link the subform to the stored procedure is:

Private Sub Form_Current()
 
    Dim com As ADODB.Command
    Dim rst As ADODB.Recordset
 
    Set com = New ADODB.Command
    With com
        .ActiveConnection = CONNECTION
        .ActiveConnection.CursorLocation = adUseClient
 
        .CommandText = "spCorpGroupOrgStructure"
        .CommandType = adCmdStoredProc
        .Parameters.Refresh
        .Parameters("@intOwnedCompanyID") = Me.intCompanyID
 
        Set Forms!frmCorpGroupOwnership.[Owned Companies].Form.Recordset = .Execute
    End With
 
End Sub
 

Any ideas on how to fix this?

[jimatqsi]

Add some error handling, maybe one of these statements prior to the .execute is failing and you have no notification of that.

Also you need to close the connection and cleanup
Add

com.close
set com = nothing

[Steven Kogan]

@jimatqsi
Thanks. The data error comes up during a sort or when choosing 'Refresh All'. I've caught the error in the Form_Error event. The Current event doesn't come up with any errors.

I ended up using a pass-through query, modifying the command text with vba on the current event of the main form. The subform connects to the pass-through query which allows for multiple sorts just as if it is a regular query. It runs slower than the previous solution, but at least the error doesn't occur.