Constructing / Editing Queries through code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey all,

Quick question, I wanted to change the criteria value of a field in my query
through code. How would I go about this?

Also if you know of any websites showing how to create or edit queries
through code it would be greatly appreciated. I would love to make one query
adaptable through code rather and muck up my database with a bunch of
queries. Thanks!

James O
 
You can set the SQL property of the QueryDef.

If you are just changing the WHERE clause, the stub of the query statement
(SELECT and FROM clauses) remains the same, and the remaining clauses (e.g.
ORDER BY) probably do too. You will therefore end up with something like
this:
Dim strWhere As String
Dim strSql As String
Const strcStub ="SELECT * FROM Table1 "
Const strcTail = " ORDER BY Field1;"

strWhere = "City = ""Springfield"""
strSql = strcStub & "WHERE " & strWhere & strcTail
dbEngine(0)(0).QueryDefs("Query1").SQL = strSql

BTW, in many cases you don't need a saved query at all. Just:
- Assign strSql to the RecordSource of your form or report.
- OpenRecordset() directly on strSql
- Execute strSql, if it is an acton query.
- Assign strWhere as the Filter of a form or report.
- Use strWhere in the WhereCondition of OpenForm or OpenReport.
 
In addition to Allen's suggestions, if you want to build your own SQL, one
way to do it quickly is to use the Query Builder to get the query like you
want it. Then you can switch to SQL view, copy the SQL, and paste it into
your code.

I use this method when the query will change depending on user interaction
and use the method Allen described to modify the SQL to suit the situation.
 
Awesome, I can't wait to start using this, I currently have a ton of queries
to fit different situations, now I can have only a handful. Thanks!!
 
James, below creates a saved query, in this case qtempQuery, based on an
aguement passed to the sub:
If you set a (or many) report's recordsource(s) to qtempQuery, then call
this sub to create the proper qtempQuery
before opening the report, you have one stored query instead of many.
Works great. [qdbtcmast alpha] is, of course, an existing query or table.

Sub subCreateQuery(Arg1 As String)
Dim qthisQuery As dao.QueryDef
Dim strSql As String
'delete the temp queries to avoid warning popups
On Error Resume Next
Docmd.DeleteObject acQuery, "qtempQuery"
On Error GoTo LogErr
Select Case Arg1
Case 2 'Select query to count NEEDS_CARD
strSql = "select * from [qdbtcmast alpha] " _
& "WHERE NEEDS_CARD = TRUE;"
Set qthisQuery = CurrentDb.CreateQueryDef("qtempQuery", strSql)
Case 3 'Select query to count NEEDS_NL
strSql = "select * from [qdbtcmast alpha] " _
& "WHERE NEEDS_NL = TRUE;"
Set qthisQuery = CurrentDb.CreateQueryDef("qtempQuery", strSql)
Case Else
End Select
LogErr:
'whatever
End Sub

hth, UpRider
 
Back
Top