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!
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
'Set sort direction string according to the direction of the sort
If blnAsc Then
strSortDirection = CORR_SORT_ASC
strSortDirection = CORR_SORT_DESC
'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
Me.OrderByOn = True
Select Case Err.Number
Call MsgBox("Number: " & Err.Number & vbCrLf & "Description: " & Err.Description & vbCrLf & _
"Location: Form/Module_Procedure", vbOKOnly + vbExclamation + vbDefaultButton1, "Unexpected Error")