Data Provider could not be initialized with ADO recordset and subform

I found my exact problem on the web with no answer.  I am hoping someone in EE can answer it, so I copied their description and added my comments in parentheses:

I am working with Access 2007, SQL server 2005 using MDB and ADO.
I have a main form that has a subform datasheet.
Subform is bound to an ADO recordset.  (Except that mine is a disconnected ADO recordset)
When I try to Sort the subform it works the first time (in the OnLoad event for the form), but the second time
I get an error "Data Provider Could not be initialized"

(Also, when I open the form outside the Parent form, the code works fine)

Any ideas would be appreciated!

Thank you.

Private Sub OrderRecords(strSort As String)
    On Error GoTo ErrorHandler
 
Dim strSortDirection As String
 
    'If sort is on the same field as currently sorted, change direction of sort
    If Replace(Me.OrderBy, CORR_SORT_DESC, "") = strSort Then
        blnAsc = Not blnAsc
    Else
        blnAsc = True
    End If
    
    'Set sort direction string according to the direction of the sort
    If blnAsc Then
        strSortDirection = CORR_SORT_ASC
    Else
        strSortDirection = CORR_SORT_DESC
    End If
 
    'Set Order By string
    Select Case strSort
    Case CORR_SORT_DATE
        Me.OrderBy = CORR_SORT_DATE & strSortDirection   <-----Failure occurs here
    Case CORR_SORT_CATEGORY
        Me.OrderBy = CORR_SORT_CATEGORY & strSortDirection   <-----And here
    Case CORR_SORT_EMP
        Me.OrderBy = CORR_SORT_EMP & strSortDirection    <-----And here, you get the picture
    Case CORR_SORT_TIME
        Me.OrderBy = CORR_SORT_TIME & strSortDirection
    End Select
    
    Me.OrderByOn = True
    
Procedure_Exit:
    Exit Sub
 
ErrorHandler:
    Select Case Err.Number
        Case Else
            Call MsgBox("Number: " & Err.Number & vbCrLf & "Description: " & Err.Description & vbCrLf & _
                        "Location: Form/Module_Procedure", vbOKOnly + vbExclamation + vbDefaultButton1, "Unexpected Error")
    End Select
    Resume Procedure_Exit
    
End Sub

Open in new window

BJTurnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
code executed from the main form affecting the subform uses the following syntax:
Me.YourSubformControl.Form!Control

So where is your code being executed from...main form or subform?        

if the above does not fix the problem, try making the routine Public instead of Private and see what happens.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BJTurnerAuthor Commented:
Took the sort code out and made it a public procedure and it worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.