Lookup Filter dropdown incomplete in programmatically altered SQL backend Access database

Question

In this thread a user had problems with the lookup filter being missing in an acess database that had a SQL server backend. The problem was easily solved simply by checking on an option in the current database settings that allowed ODBC fields to also provide lookup filter dropdowns.

For those confused, the lookup filter is the excel like function in a datasheet view that allows you to click on the drop down of the field name and select individual values from that field for filters by a checkbox.

I, however, have a slightly different problem. The checkbox to allow ODBC field filter lookups is active in the settings, so that's not an issue. If I have a form that pulls data from a query, the lookup filters work fine, and are pre-populated with values in that field for filter selection. If that record source is changed in VBA, however, say for example, a SQL statement that exactly matches that query, the lookup filter no longer works. I tried creating a recordset and attaching it to the same form, creating a SQL statement and attaching it to the record source, and opening the form with arguments which are then used within the form's on load event to change the record source, all with the same result of no lookup filter.

Am I overlooking something?

Answer 1

After HansUp replied, I floundered about trying to figure out what he meant and ran into it rather accidently.

Since the form is opened by another form where the SQL statement is pregenerated, I simply assigned the form's underlying query to a QueryDef object and applied the pregenerated SQL statement to the object's SQL property. When the form was subsequently opened, the new SQL statement was used and all the lookup filters worked properly. I was quite pleased :D

Here's a quick run down of the code:

Dim db as Database
Dim qDef as QueryDef
Dim strSQL as String

'generate SQL statement and assign it to strSQL here'

Set qDef = db.QueryDefs("qryMyQuery")
qDef.SQL = strSQL
DoCmd.OpenForm "frmMyForm"  'frmMyForm is based of qryMyQuery'

Thanks, HansUp!