Access 97 Database Query won't run

[JazzBass]

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:

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.
JazzBass

[RogerH]

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

[JazzBass]

Roger,
Thanks so much.

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

Check it out:

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
JazzBass