As you can see from the Title I'm trying to have my cake and eat it too.

I'm building a database in which there are two particular types of forms that I can see coming up over and over again and I'm trying to find a satisfactory way to use as much of the built-in access functionality while still maintaining a bit of flexibility and performance.

Currently I am using an Access 2007 accdb file backing onto a single SQL Server Express 2008 instance. I contemplated using an ADP and haven't completely thrown away the idea, but would need a fairly compelling reason to do so at this stage. (My initial experimentation seemed to indicate that Access 2007 doesn't yet have much in the way of SQLSrv2008 support which made me a bit leery)

The database is intended to track progress on work items and (among other things) provide an interface for supervisors to allocate said workitems to employees. The Work items have a fairly large number of attributes (columnns) which will need to be used to sort/filter the information.

I am quite new to SQL Server but have recently created a couple of views/stored procs/inline table-val functions to deal with much of the querying and am now reasonably confident working around the ADO library.

Unfortunately, I have discovered that binding ADO recordsets (during the form load procedure) to a Datasheet Form seems to disable all the built in Filter, Sort and Refresh functionality. I typically see a lot of "Data provider could not be initialized" type errors.

In some other scenarios I know I have managed to get the column header filters to work, but they are not populated with 'Lists of Values' for want of a correct term.

As a side note I have switched on the "Show List of Values in ODBC Fields" option

The two common forms I'm trying to build are:

1. A basic subform datasheet, that is 'linked' to its parent at the query level (rather than using a filter which seems to be a common method of handling this)

2 A subform in which numerous selections can be performed, the data sorted filtered, etc before performing an action on the selected list of rows.  

As a nice bonus it'd be good if I could use stored procedures to populate these forms as well, although that might be a bit of a stretch.

For Form type 1 I currently have in the On_load method of the parent form something like this:

Me.RecordSource = "SELECT * FROM uvw_PitemAllCategories WHERE PItemID =" & Me.OpenArgs
Me.chdAssign.Form.RecordSource = "SELECT * FROM uvw_Emp_Assignments WHERE PItemID=" & Me.OpenArgs

Which seems to work reasonably well, however I'd prefer to pass the select statement straight through to the SQL server since the two views (prefixed uvw_)  are likely to grow quite large over time and I don't want the whole thing being batted back and forth over the network.

For Form Type 2 I currently have gone down the path of designing a pile of custom classes built around an MSComctlLib.ListView control which I populate manually, however I'm not going to be able to come close to achieving the functionality of a basic Access datasheet in the timeframe required so am looking elsewhere.

One thought I'd had was to use the "Simple Select" method demonstrated by Leigh Purvis on his website ( http://www.databasedevelopment.co.uk/examples/ListSelect.zip ) (Great examples by the way Leigh. Incredibly helpful) but unfortunately my 'category table' is a non-updateable view which prevents me from selecting the checkboxes.

So anyway, I'm a bit stuck on where to go next and am worried about coding myself into a dead-end. 200 points for whoever can help me sort out Form problem 1 and 300 for Form problem 2. (and my undying gratitude)

Any general help or commentary on my design greatly appreciated.