Hi all, been banging my head against a brick wall on this one for a few days. What sounds easy is being suprisingly difficult.

I have a access 2003 adp pointing at a SQL server 2005 instance. The purpose of a the app is to allow users to look at records allocated to them, separated into different categories and shown in multiple datasheets (not wedded to datasheets, its just what I'm using currently), that allows threm to view certain information and filter, sort and ultimately select subsets to then go and work with. All the working is done on other forms so all that is needed is a list for them to view. No others will be working those records, and even if they did it doesn't matter, so a complete snapshot is fine and there is no requirement to write back or update with the underlying data.

This is where it gets a little complicated. What information they want to view varies from user to user, therefore I had to allow each user to select the fields they were interested in (and change them on the fly), which are stored in the DB and used to construct a SP that outputs those choices. All good so far, I'm more of a SQL server developer than an access one. This all works. However, some of the fields they want to see is in a table that has to be pivoted in order to be presented to the user (long story, but its the only option).

The datasheets show all this information, and allows the user to filter and sort fine, EXCEPT those fields that come from the pivot table. They can be filtered, but not sorted. Now I understand that this is probably because of the uncertain nature of the underlying data (theres no actual table or view for it to point at etc.), therefore see the only option to be something like an isolated list on the client machine that is read only (absolutely fine, as said all the actual work will be done through other forms) and treated as an entity in its own right (open to any other suggestions).

I've tried the following that appeared to do this, with no change:

Private Function PopulatePages(sFormName As String, iActionID As Integer)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQueryString As String

'Use the ADO connection that Access uses
Set cn = CurrentProject.AccessConnection

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.CursorLocation = adUseClient
.Source = "EXEC ccsp_GetDataDirectory " & Form_frmCampaign.ComboCampaign & ", " & Me.ComboAgentID & ", " & iActionID & ";"
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.Open
End With

'Set the form's Recordset property to the ADO recordset
Set Me(sFormName).Form.Recordset = rs
Me(sFormName).Form.Visible = True

Set rs = Nothing
Set cn = Nothing

End Function

The only restriction I have is that I must be able to loop through the RS once its been filtered and sorted as I'll be using the PKs to select which records to show in the other forms

Any help would be great.

Ta

Matt

This post has been edited by Sable: Mar 9 2012, 04:36 AM

Aha! Looks like I've been barking up the wrong tree for 3 days now. I stumbled across a reference that you can not sort by a Memo field, and since the pivot'd records come from a a field using VARCHAR(MAX), on converting it to a varchar(255) it becomes sortable!

phew. Glad thats over and done with.