HOW TO RUN THE QUERY AND VIEW THE RESULTS..

[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

 

[FancyPrairie]

I've struggled with this in Access 95. The only way I could get it to work was to delete the query prior to creating it. You will need an OnError statement in case you try to delete the query and it doesn't yet exist.

 

[JPMontreal]

Delete the query, then create it again:

Set dbMyDB = CurrentDb

Dim TheQry As QueryDef
For Each TheQry In dbMyDB.QueryDefs
If TheQry.Name = "MyQuery" Then
dbMyDB.QueryDefs.Delete "MyQuery"
Exit For
End If
Next

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


Jean-Paul
Montreal
mtljp2@sympatico.ca