replace query by pressing button on form

[travellinman]

i'm trying to create a report that is based on selections from the user. i'm using a form which creates a query, by passing on the sql statement (from a hidden textbox) into the new query "query1". The report then uses this query as a source. however, the next time i want to carry out this routine the old query1 is not replaced by the new one. so the report displays the old information.

is it possible to replace the first query1 after pressing a button on the form? maybe deleting it first, before creating a new one?

thanks!

[travellinman]

maybe the code that i'm using will clarify things some more (please tell me if the code is not entirely correct, i'm not an expert. i just scraped bits and pieces together):

Private Sub Command5_Click()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strName As String
   'first get a unique name for the querydef object
   strName = Application.Run("acwzmain.wlib_stUniquedocname", "Query1", acQuery)
      Set db = CurrentDb
      Set qdf = db.CreateQueryDef(strName, Me.Text1)
      qdf.Close
 
ExitHere:
   On Error Resume Next
   qdf.Close
   Set qdf = Nothing
   db.QueryDefs.Refresh
   Set db = Nothing
 
ErrHandler:
   Resume ExitHere
 
 
 
   On Error GoTo Err_Command5_Click
 
    Dim stDocName As String
 
    stDocName = "TestMAINreport"
    DoCmd.OpenReport stDocName, acPreview
 
Exit_Command5_Click:
    Exit Sub
 
Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
 
End Sub

[travellinman]

never mind, found the code to do this:

DoCmd.DeleteObject acQuery, "Query1"

[pks00]

Personally I wouldnt bother creating and deleting new queries.

I would simply just set the sql each time
eg

dim qdf as dao.querydef

set qdf=currentdb.querydefs("Query1")
qdf.SQL = Me.Text1


voila

[travellinman]

you're right, that's a lot better.

thanks!