Automatically storing dynamic sql in a text box

[annysjunkmail]

Hi Group,
Could someone advise me if the following is possible.
I have designed a mini reporting system to record queries/sql that I
run on a database. I have done this as I am asked to run numerous
queries each day and it is not manageable to store them all in the
query window. The sql of each query is stored in a table called
'tblDatabaseQueriesSearchMethods' (there are about 600 stored in my
database so far).

It actually works very well but I have one problem. For example, lets
say I designed a query, stored the sql and was asked to revisit it, but
then was asked to change or update the criteria...I then open it in
design view (using the attached code below), make the necessary
changes, but am forced to copy and paste the new sql back over the old
sql to store my new sql. Is there any clever way to automatically
update my sql with the new sql without the need for copying and pasting
(which I and others sometimes forget to do and we find it a clumsy way
of doing things). I could send a someone a slimmed copy of the
database to provide a working example if would help

(Hope this reads right btw)

Many thanks
Tony

here's the code that call the design view which allows me to make my
changes...)credit to Duane Hookum)

'View the query
'Trap the error if MyQuery exists
On Error GoTo cmdReport_Click_Err


Dim strSQL As String 'Basic SQL String
Dim strQueryName As String
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).Databases(0)
strSQL = Forms![frmDatabaseReports]![txtSQL]
strQueryName = "MyQuery"

Set qdf = db.CreateQueryDef(strQueryName, strSQL)
db.QueryDefs.Refresh
'DoCmd.OpenQuery "MyQuery", acViewDesign
DoCmd.OpenQuery (strQueryName), acViewDesign
Set qdf = Nothing
db.Close
Set db = Nothing

cmdReport_Click_Exit:
On Error Resume Next
Exit Sub

'This rotuine traps the error if MyQuery exists
'and deletes it if it does
cmdReport_Click_Err:
If Err = 3012 Then 'MyQuery wasn't delete from previous usage
db.QueryDefs.Delete "MyQuery"
Resume
Else
MsgBox Err & ": " & Error$, 16, "Error in cmdReport_Click"
Resume cmdReport_Click_Exit:
End If

 

[Albert D. Kallal]

the number of quires you have seems to be complete out of control.....

Why remove ALL conditions from the query,and simply prompt the user for the
conditions, and pass those to the report.

Again...pass conditions to the report..NOT modify the query each time!!!.

That way, every time they need to run a sales report for a date range, or
even in addition to the date range, a particular sales rep, then NO NEW
QUERY need be made.

Further, NO EXISTING query need to be modified...

for a given report, there is useably only 2, perhaps 3 possible conditions
THAT IS of any use. I have application with 160+ forms, and it is VERY
COMPLEX application, and yet they not asked me for a new query or report in
about 5 years of use.

The secret is to simply build a form that prompts the user for a few of the
common conditions/critera. Each form, and reprot has what is called a
"where" clause in which you can pass sql conditions, and htus elmoante the
need to have 600 queires (that is just way too many).

Here is some sceen shots of what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

 

[annysjunkmail]

This is excellent food for thought and a great way forward.
You are correct - there are 4 standard queries that are used
continuously and I see the logic of passing the 'where' clause to the
query rather than changing the query itself.
Sure I will have to write a bit of code to get the forms and logic
going but I like this way forward.
Many thanks for taking the time to provide your extensive
solution...much appreciated

Tony

the number of quires you have seems to be complete out of control.....

Why remove ALL conditions from the query,and simply prompt the user for the
conditions, and pass those to the report.

Again...pass conditions to the report..NOT modify the query each time!!!.

That way, every time they need to run a sales report for a date range, or
even in addition to the date range, a particular sales rep, then NO NEW
QUERY need be made.

Further, NO EXISTING query need to be modified...

for a given report, there is useably only 2, perhaps 3 possible conditions
THAT IS of any use. I have application with 160+ forms, and it is VERY
COMPLEX application, and yet they not asked me for a new query or report in
about 5 years of use.

The secret is to simply build a form that prompts the user for a few of the
common conditions/critera. Each form, and reprot has what is called a
"where" clause in which you can pass sql conditions, and htus elmoante the
need to have 600 queires (that is just way too many).

Here is some sceen shots of what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal