Change Criteria in a QueryDef and Save the query 2

[sxschech]

Hi,

I've done this some years ago in a previous job, but don't have that code now. I would like to make a change to an existing query and have it saved so that if I were to click on the design view of the query, it would look as if the criteria were already there when done in design view. When I run the code, I receive the error 3012 Object already exists, which is true. How can I modify the code in [red]red[/red] to allow it to save the existing query with the changes?

   Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb
...
...
[red]
Set qd = db.CreateQueryDef("qryGraduate", Replace(CurrentDb.QueryDefs("qryGraduate").SQL, "='On';", "='Exclude On';"))
[/red]
...
...

 

[jedraw]

Here's a good reference. It also has links to other parts of the same series of examples/tutorials.
Martin Green Queries "on the Fly"

 

[PHV]

Perhaps this ?
Set qd = db.QueryDefs("qryGraduate")
qd.SQL = Replace(qd.SQL, "='On';", "='Exclude On';")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sxschech]

jedraw - I'll review the site.

PHV, thanks that did the trick and looks cleaner than what I had.