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
        blnAsc = True
    End If
    'Set sort direction string according to the direction of the sort
    If blnAsc Then
        strSortDirection = CORR_SORT_ASC
        strSortDirection = CORR_SORT_DESC
    End If
    'Set Order By string
    Select Case strSort
        Me.OrderBy = CORR_SORT_DATE & strSortDirection   <-----Failure occurs here
        Me.OrderBy = CORR_SORT_CATEGORY & strSortDirection   <-----And here
        Me.OrderBy = CORR_SORT_EMP & strSortDirection    <-----And here, you get the picture
        Me.OrderBy = CORR_SORT_TIME & strSortDirection
    End Select
    Me.OrderByOn = True
    Exit Sub
    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

Who is Participating?
puppydogbuddyConnect With a Mentor Commented:
code executed from the main form affecting the subform uses the following syntax:

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.

BJTurnerAuthor Commented:
Took the sort code out and made it a public procedure and it worked.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.