I have been programming in Access, always with an Access backend, for many years and have used the same technique to allow the user to sort a particular column on a continuous form. I use this routine that uses the Order By property of the form to do this.
Private Function setColumnOrder(passedFieldName As String)
gBogusStr = Me.OrderBy
If Me.OrderBy = passedFieldName & " DESC" Or _
Me.OrderBy = "[" & passedFieldName & "] DESC" Then
Me.OrderBy = passedFieldName
Me.OrderBy = passedFieldName & " DESC"
Me.OrderByOn = True
On my continuous forms the user can sort the contents of any column by clicking on the column header, Clicking once will sort the contents of the column and all records ascending and clicking again will click sort the contents of the column descending, again ascending etc..
Prior to using a SQL backend I always revised the recordsource
of the form using something like
Me.recordsource = “Select …. From tbl… Where….”
Now I am using Access 2013 with a SQL Server backend. I am populating my continuous form with a recordset returned from a stored procedure.
Now instead of revising the recordsource
the logic sets the recordset
using 'Set Me.Recordset = rs '.
In this case the difference being the statement
‘Set Me.Recordset = rs’
‘Me.recordsource = “Select …. From tbl… Where….”
Populating the form works great and is very fast but after executing ‘Set Me.Recordset = rs’ . my procedure for ordering a column does not work.
When using the above ‘SetColumnOrder’ routine, I get a runtime error ‘31’, ‘Data provider could not be initialized’ when I click on any column heading. Any idea why that is happening? Does the ‘Order By’ property of the form not work when it is populated resetting the recordset?
This isn't specific to the fact that it is a recordset returned from a SQL SPROC. It seems like any time the '‘Set Me.Recordset = rs’ is used to chnage the forms recordset my ordering routine kicks the error.
Any idea how this can be resolved?
Is there a way to get the column re-ordering functionality to work when the continuous form is populated by resetting the recordset?