Access 97 Database Query won't run

I'm trying to run a query and display the results in a popup window like it would if you ran a query using the query design wizard.

When I run the following, I get a "Microsoft Access cannot find the object: " and then it states the query string.

What am I doing wrong?

I am very new to databases using VBA and VB

Here is my code:

Code:

---

Sub cmdUpdate_Click()
Dim dbs As Database
Dim strVariable As String
strVariable = Forms!StateSelectNew.cmbStateSelect

Set dbs = CurrentDb   
   
strSQL = "SELECT DISTINCT * FROM OTC_SITES WHERE [SITE_STATE] = " & "'" & strVariable & "'"
   
DoCmd.OpenQuery strSQL, acViewNormal, acReadOnly
 
Set dbs = Nothing
End Sub

---

Thanks for your help. :D
JazzBass

You have to add a new query to the querys collection and then open this new query. Unfortunatelly I'm not at my machine, so I can't provide you any code. Hope it helps anyway.

Roger

Roger,
Thanks so much.

That did help. After banging my head against the desk I finally figured out how to do it. :D :D

Check it out:

Code:

---

Sub cmdUpdate_Click()
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef

Dim strVariable As String
Dim strSql As String

'Deletes the previous query
DoCmd.DeleteObject acQuery, "FindState"

'Gets the State variable
strVariable = Forms!StateSelectNew.cmbStateSelect
   
'Now begins the action
    Set dbs = CurrentDb
    strSql = "SELECT DISTINCT * FROM OTC_SITES WHERE SITE_STATE = " & "'" & strVariable & "'"
    Set qdf = dbs.CreateQueryDef("FindState", strSql)
    DoCmd.OpenQuery qdf.Name
 
  Set qdf = Nothing
  Set dbs = Nothing

End Sub

---

Thanks again Roger :D
JazzBass