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. :D
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. :D :D

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 :D
JazzBass