HOW TO RUN A QUERY WITHOUT SAVING IT...

[rbasram]

HI I HAVE WANT TO MAKE A PARAMETER QUERY WHOSE PPARAMETERS ARE PASSED BY THE USER AFER SELECTING IT FROM THE COMBOBOX. I HAVE USED THE CODE BELOW, BUT THE QUERY ONLY WORKS THE FIRST TIME THE USER CLICS FIND THE SECOND TIME WHEN THE USER TRIES TO RUN THE QUERY HE GETS AN ERROR, SAYING THAT MyQuery ALREADY EXISTS. IS THERE ANY OTHER WAY TO RUN THE QUERY, I DON'T WANT TO SAVE OR MAKE A QUERY EVERYTIME THE USER CLICKS ON FIND, JUST WANT TO RUN IT..

PLEASE HELP...

Private Sub cmdFind_Click()

Dim dbMyDB As Database
Dim qdMyQuery As QueryDef
Dim rsMyRS As Recordset
Dim strMySQL As String
Dim lngParam As Long
Dim RItem As Integer

strMySQL = " Select * from ClientDisplayQuery WHERE [Product].[ClientDisplay] = '" & cmbCName.Column(RItem) & "';"

Set dbMyDB = CurrentDb
Set qdMyQuery = dbMyDB.CreateQueryDef("MyQuery", strMySQL)

DoCmd.OpenQuery qdMyQuery.Name

End Sub

 

[rewdee]

As I see it, you have two options. The easiest and simplest is to delete the object after opening it as:

docmd.DeleteObject acQuery,"MyQuery"

The other option is to use a replace procedure of the parameters but saving the SQL as:

Sub Something
Dim sSavedSQL as string
dim sSQL as string

with currentdb.QueryDefs("MyQuery")
  sSQL=.SQL
  sSavedSQL=.SQL
  sSQL=SomeCustomReplaceFunction(sSQL,"[Param1]",cmbCName.Column(RItem))
  .Execute 'Or Open Recordset or Whatever
  .SQL=sSavedSQL
end with

If you need a copy of the Replace function, I have a generic one that is very useful in many string routines, that I could post if you need it.

Hope this helps,
Rewdee

 

[rbasram]

HI REWDEE THANX FOR YOUR REPLY BUT I THNK I WILL USE THE FIRST PROCEDURE IT LOOKS MORE SIMPLE. I TRIED OUT WITH THE FIRST CODE BUT I GET THE ERROR, "YO CANNOT DELETE THE QUERY WHILE IT IS OPEN" IS I HAVE TO SET FOCUS TO SOMTHING ELSE BORE DELETING IT. DO YOU HAVE ANY SUGGESTIONS,

THANX

 

[Taff07]

You can`t delete the object while you have it open. I`m assuming the user will want to actually view the query results.
What you need to do is put that bit of code at the start of the code so that it deletes the query on calling the code before you try to create it.

Ian

 

[rbasram]

HI TAFFO7 BUT HOW CAN U DELETE THE QUERY IF THE USER IS RUNNING THE QUERY FOR THE FIRST TIME. I MEAN THERE WIL BE NO QUERY NAMED MYQUERY IF THE USER CLICKES IT THE FIRST TIME, DO YOU HAVE ANY SUGGESTIONS I DON'T KNOW HOW TO CHECK IF THE QUERY ALREADY EXISTS USING IF STATEMENT, IS THERE A WAY...I AM GETTING AN ERROR..

THANX

 

[JPMontreal]

Look at the code I post into AccessQuery for you. Jean-Paul
Montreal
mtljp2@sympatico.ca