MS Access Database Check Box List Filters Missing On SQL Server back end

Question

When I connect Access 2007 to SQL Server (whether by ADO recordset or by linked table) I no longer get check box lists (of available filter values) in the datasheet column filters.

Is this feature available only with MDB/ACCDB and/or DAO?

Answer 1

I think the check box in datasheet view of native Access tables is governed by the "Display Control" property in the table design. I don't recall what's available when the table is in SQL Server. If you provide a form in "datasheet view", you should be able to bind a check box control to the SQL Server column.

Edit: I think I misunderstood your question yesterday. If you click the Office Button, select Current Database, then put a check in the "ODBC fields" box under "Filter lookup options" ... does that do what you want?

Answer 2

Yes, thank you both (for asking the question, and providing the answer). I couldn't find this anywhere. Relieved the solution was so simple. Thanks again.

Answer 3

I have a similar problem. ODBC fields is checked as listed, and if the form pulls directly from a query, the lookup filter works fine. However, if I programmatically change the record source to a SQL statement, the lookup filters don't work anymore.

qryData simply queries dbo_tblMain frmData uses qryData as the record source, lookup filters on split form work fine.

However,

open frmData and set RecordSource = strSQL where strSQL is identical to qryData, and the lookup filters are no longer working.

Any ideas?

Answer 4

I know we're breaking protocol by not opening a new question, but I'm going to answer this nevertheless so this thread will be complete. This is a more complete answer than the previous ones.

I think I have this topic nailed down now.

The lookup filters won't work with a recordsource that is not an Access object, and they don't work in linked tableds directly.

You have to create a query of the linked table, for example: Select * from tblOrders, and use that query as the recordsource in order to get the lookup filters.

HOWEVER, I found a more flexible approach as well. I create passthrough queries to SQL/Server and use that as my recordsource. Then, in code, I set the SQL of the passthrough queries like this:

Currentdb.QueryDefs("qpstOrders").SQL="Select * from Orders where OrderID =" & Me.OrderID

In the current event of my subform, I change the query on the fly to pass the appropriate record -- or it can just be a more generic query. The lookup filters work fine this way and the interaction with SQL/Server is lightening fast.