Writing a query from code

[Chris RR]

For a number of nasty reasons, I want to write a query in code, then use that query as the basis for my form. (OK, Ok, I'm trying to create an on-the-fly topvalues query...)

I can write the query, no problem, but I can't seem to get the form to redisplay using the newly modified query. If I close and then reopen the form, it works.

I don't want to change the whole business to run with recordsets. The form has three filters on it & I'd rather leave the filtering alone. I just want the users to be able to say, "Give me the top 5 (or 15 or 50) records."

The query is built in a sub. This part works just fine (here's the basics):
Set WS = DBEngine.Workspaces(0)
Set myDb = CurrentDb

' Delete the existing qCustomized_TV
On Error Resume Next
DoCmd.DeleteObject acQuery, "qCustomized_TV"

' Turn on the regular error handler
On Error GoTo TV_Query_Builder_Error

Set myQ = myDb.CreateQueryDef("qCustomized_TV")
Set baseQdef = myDb.QueryDefs("qCustomized_TV_Base_Query")
strQuery = baseQdef.SQL

' Code that modifies the query goes here

myQ.SQL = strQuery

Set baseQdef = Nothing
Set rstOut = Nothing
Set myDb = Nothing
Set WS = Nothing

In the form, I have tried requery, repaint and refresh. I've tried ApplyFilter and ShowAllRecords. Nothing...

 

[Alexandre]

ChrisRR

Not tested, but:

It is not clear to me what you expect from:
myQ.SQL = strQuery
Set myQ = myDb.CreateQueryDef("qCustomized_TV")

From the form, I would try:

Set myDB = DBEngine.Workspaces(0)(0)

' Delete the existing qCustomized_TV
On Error Resume Next
DoCmd.DeleteObject acQuery, "qCustomized_TV"

' Turn on the regular error handler
On Error GoTo TV_Query_Builder_Error

Set baseQdef = myDb.QueryDefs("qCustomized_TV_Base_Query")
strQuery = baseQdef.SQL

' Code that modifies the query goes here

Me.RecordSource = strQuery

Set baseQdef = Nothing
Set rstOut = Nothing
Set myDb = Nothing

I believe it may de-activate the filter, but you can reactivate it :Me.FilterOn = True


BTW, did you give a try to the ADO recordset and .MaxRecords


Alex