sql question on form code

[NJudson]

I'm trying to change a criteria on a stored query based on the value of form control; however, it does not seem to be working. I'm using Access 2k and I have a form with a dropdown combobox and a textbox. Based on the values for these 2 form controls I want to change the criteria found in a stored query. below is the code that I've written but I get the error "Run-time error 3251: Operation is not supported for this type of object".



Private Sub cmdGenerateGreaterThan2PercentRBResults_Click()

Dim dbs As DAO.Database
Dim strSwitch As String
Dim strSector As String
Dim strSQL As String
Dim qdf As Object

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryICPBERFinalResults")

strSwitch = Me.cboSelectSwitchforGreaterThan2PercentQuery
strSector = Me.txtSelectSectorForGreaterThan2PercentQuery
strSQL = "SELECT [qryICPBERresults].[sDate], [qryICPBERresults].[sSwitch], [qryICPBERresults].[MTX], [qryICPBERresults].[% RB >2%], [qryICPBERresults].[% FB >2%]" _
& "FROM qryICPBERresults" _
& "WHERE ((([qryICPBERresults.[sSwitch]) = '" & strSwitch & "') And (([qryICPBERresults].[MTX]) = '" & strSector & "'))" _
& "ORDER BY [qryICPBERresults].[sDate];"

qdf.SQL strSQL
DoCmd.OpenQuery ("qryICPBERFinalResults")

End Sub


The error occurs at the line:
qdf.SQL strSQL

Am I getting the error because I'm trying to base a querydef on a query rather than on a table? If so then does anyone have any suggestions on another method to try. Thanks for any help.

 

[Jon K]

Try this:-

Private Sub cmdGenerateGreaterThan2PercentRBResults_Click()

   Dim dbs As DAO.Database
   Dim strSwitch As String
   Dim strSector As String
   Dim strSQL As String
   Dim qdf As DAO.QueryDef

   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("qryICPBERFinalResults")

   strSwitch = Me.cboSelectSwitchforGreaterThan2PercentQuery
   strSector = Me.txtSelectSectorForGreaterThan2PercentQuery

   strSQL = "SELECT [qryICPBERresults].[sDate], [qryICPBERresults].[sSwitch], [qryICPBERresults].[MTX], [qryICPBERresults].[% RB >2%], [qryICPBERresults].[% FB >2%]" _
          & " FROM qryICPBERresults" _
          & " WHERE ((([qryICPBERresults].[sSwitch]) = '" & strSwitch & "') And (([qryICPBERresults].[MTX]) = '" & strSector & "'))" _
          & " ORDER BY [qryICPBERresults].[sDate];"

   qdf.SQL = strSQL
   DoCmd.OpenQuery ("qryICPBERFinalResults")

End Sub

 

[NJudson]

Thank you Jon. Looks like I basically had a few syntax errors that is corrected now. Much appreciated!