-
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
-
Thanks 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