Temporary Queries (1 Viewer)

[Kenln]

I would like to create a temporary query that a form can use when it opens.
Of course the reason it is temporary is that the query can change each time the form opens.

I found this code in here (this forum).

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strMySQL As String

Set db = CurrentDb
[COLOR="Red"][B]Set qdf = db.QueryDefs("qryMyQuery")[/B][/COLOR]
strMySQL = ""
strMySQL = strMySQL & "SELECT Field1, Field2 "
strMySQL = strMySQL & "From MyTable "

qdf.SQL = strMySQL

Set qdf = Nothing
Set db = Nothing

Which seems like a good start except
I'm getting a "Item not found in this collection" at Set qdf = db.QueryDefs("qryMyQuery")
and... it is temporary?

Any help would be appreciated.

 

[Jeanette Cunningham]

Create a query with just one field from one table and call it qryMyQuery and save it.
The code you have is looking for qryMyQuery.
When it finds qryMyQuery, the code is changing the query to the fields in the query to the ones in strMySQL and saving it.

 

[Kenln]

I thought Access had a limit on how many times an object could be edited.

This query will be changed often.

Is there a way to create a temp query as a variable (in memory) that I can point the form record source to?

 

[Jeanette Cunningham]

Nope, the limits on editing an object have never stopped people using a saved query like qryMyQuery and changing it frequently.
You must use a saved query for the code you posted - that is the reason for the error about item not found in this collection. Access will only look in the saved queries for qryMyQuery and if it can't find it will give you that error message.