How To Use Dynamic Queries As Report RecordSource?

[mooseshoes]

All:

I'm using Access 2000 on a Windows XP platform.

My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.

What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.

Using docmd.openrepor t is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.

Your advice is appreciated and I'm happy to provide further information upon
request.

Best,

Steve

[PC Datasheet]

Steve,

Take a look at QueryDef in the Help file. Basically you have a query with a
name saved in the database. With QueryDef you dynamically create the SQL for
the query which can add or subtract fields from the saved query as well as
addm or delete criteria. Then you just use the name of the query as the
recordsource of your reports. You are actually doing the same thing as you
would do if you dynamically changed the record source of the reports.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"mooseshoes " <mo********@gmx .net> wrote in message
news:Tb******** *********@newss vr21.news.prodi gy.com...

All:

I'm using Access 2000 on a Windows XP platform.

My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.

What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.

Using docmd.openrepor t is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.

Your advice is appreciated and I'm happy to provide further information upon request.

Best,

Steve

[mooseshoes]

<snip>

Take a look at QueryDef in the Help file. Basically you have a query with
a name saved in the database. With QueryDef you dynamically create the SQL
for the query which can add or subtract fields from the saved query as
well as addm or delete criteria. Then you just use the name of the query
as the recordsource of your reports. You are actually doing the same thing
as you would do if you dynamically changed the record source of the
reports.

Thanks. No "QueryDef" in my Help Index or Answer Wizard. I'm actually

using MS Access 2002 (10.2627.2625) which came with MS Office Pro. It
looks like it is dummed down to A2K for some reason which is fine because
I'm developing for an A2K box, but I was wondering about the odd
configuration. Nevertheless, I can't surface any querydef help content.
Can you suggest how I might open the door to these capabilities on my
Access version
Steve

[Barry Young]

Hey MooseShoes..

If the query isn't too complex, you can build a SQL statement using a global
variable. Define in a Module file.

Then in the Open Event in the report you can use:

Me.Recordsource = gstr_SQL << This is the SQL string that you build
dynamically

Now you have to be really careful the fields in gstr_SQL match the fields in
the report. Otherwise it will error.

Hope this helps..

Barry

Then in the report.open
"mooseshoes " <mo********@gmx .net> wrote in message
news:Tb******** *********@newss vr21.news.prodi gy.com...

All:

I'm using Access 2000 on a Windows XP platform.

My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.

What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.

Using docmd.openrepor t is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.

Your advice is appreciated and I'm happy to provide further information upon request.

Best,

Steve

[PC Datasheet]

Did you look in VBA Help? Open a standard module in design view and try Help
there.
"mooseshoes " <mo********@gmx .net> wrote in message
news:un******** ***********@new ssvr29.news.pro digy.com...

<snip>

Take a look at QueryDef in the Help file. Basically you have a query with

a name saved in the database. With QueryDef you dynamically create the SQL for the query which can add or subtract fields from the saved query as
well as addm or delete criteria. Then you just use the name of the query
as the recordsource of your reports. You are actually doing the same thing as you would do if you dynamically changed the record source of the
reports.

Thanks. No "QueryDef" in my Help Index or Answer Wizard. I'm actually

using MS Access 2002 (10.2627.2625) which came with MS Office Pro. It
looks like it is dummed down to A2K for some reason which is fine because
I'm developing for an A2K box, but I was wondering about the odd
configuration. Nevertheless, I can't surface any querydef help content.
Can you suggest how I might open the door to these capabilities on my
Access version
Steve

[Don Leverton]

Hi Steve,

I like to this by:
1.) contructing the entire SQL string in code.
2.) use that SQL string as the recordsource for a datasheet subform to
"preview" the data that will be used for the report.
3.) Use the same SQL string as the recordsource for the report.

*************** ****** Code **************
Option Compare Database
Option Explicit
Dim MySQL As String
Dim whr As String

'------------------------------
Public Sub GetSQL()

'--------------- Step One ------------------
'-------- Build the SELECT portion ---------
MySQL = ""
MySQL = MySQL & "SELECT tblContacts.* "
MySQL = MySQL & "FROM tblContacts"

'--------------- Step Two ------------------
'- Determine data type of the compare value -
'-------- and format it accordingly. --------
Dim CV
CV = (Me.txtCompareV alue)

If IsNumeric(CV) Then
CV = CLng(CV)
ElseIf IsDate(CV) Then
CV = "#" & CDate(CV) & "#"
Else
CV = Chr(34) & CV & Chr(34)
End If
'--------------- Step Three ------------------
'-- Insert the selected comparison operator ---
'-- and ready the string for assembly into ----
'------------- the WHERE statement ------------
whr = ""
Select Case Me.optCriteriaT ype
Case 1 'Equal To
whr = whr & " = "
whr = whr & CV
Case 2 'Greater Than
whr = whr & " > "
whr = whr & CV
Case 3 'Less Than
whr = whr & " < "
whr = whr & CV
Case 4 'Like _____
'WHERE (((tblContacts. ContactName) Like "D" & '*'));
whr = whr & " Like "
whr = whr & CV
whr = whr & " & '*'"
Case 5 'Contains ____
'WHERE (((tblContacts. ContactName) Like '*' & "on" & '*'));
whr = whr & " Like "
whr = whr & "'*' & "
whr = whr & CV
whr = whr & " & '*'"
Case Else
whr = ""
End Select
'--------------- Step Four ------------------
'--- IF the user has entered any Criteria ----
'--- construct a WHERE clause and insert -----
'-------- it into the SQL statement ----------
If Len(whr) > 0 Then
MySQL = MySQL & " WHERE (((tblContacts. "
MySQL = MySQL & Me.lstFieldName s & ")"
MySQL = MySQL & whr & " ))"
End If
'--------------- Step Five ------------------
'----- "Close out" the SQL statement ---------
'-- There may be those that argue that this --
'-- step is unnessecary, but it's just good --
'------- programming practice, IMHO ----------
MySQL = MySQL & " ;"
'Debug.Print MySQL

'--------------- Step Six ---------------------
'- Use this SQL statement as the RecordSource -
'- for the subform (and a report if you like) -

Me.sbfContacts. Form.RecordSour ce = MySQL
End Sub

'------------------------------------------
Private Sub cmdOpenReport_C lick()
On Error GoTo Err_cmdOpenRepo rt_Click

Dim stDocName As String

stDocName = "rptContact s"
DoCmd.OpenRepor t stDocName, acPreview, MySQL

Exit_cmdOpenRep ort_Click:
Exit Sub

Err_cmdOpenRepo rt_Click:
MsgBox Err.Description
Resume Exit_cmdOpenRep ort_Click

End Sub
*************** *************** *************** *************

--
HTH,
Don
=============== ==============
E-Mail (if you must) My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

=============== =============== =============== =============== ==============
"mooseshoes " <mo********@gmx .net> wrote in message
news:Tb******** *********@newss vr21.news.prodi gy.com...

All:

I'm using Access 2000 on a Windows XP platform.

My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.

What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.

Using docmd.openrepor t is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.

Your advice is appreciated and I'm happy to provide further information upon request.

Best,

Steve

[Don Leverton]

Hi Steve,

Install the optional DAO component and the related help files.
Microsoft really blew it by messing with the help files that were included
with Access97.

Allen Browne has summed the details on this subject in this post:
http://groups.google.ca/groups?q=%22....net.au&rnum=2

"mooseshoes " <mo********@gmx .net> wrote in message
news:un******** ***********@new ssvr29.news.pro digy.com...

<snip>

Take a look at QueryDef in the Help file. Basically you have a query with

a name saved in the database. With QueryDef you dynamically create the SQL for the query which can add or subtract fields from the saved query as
well as addm or delete criteria. Then you just use the name of the query
as the recordsource of your reports. You are actually doing the same thing as you would do if you dynamically changed the record source of the
reports.

Thanks. No "QueryDef" in my Help Index or Answer Wizard. I'm actually

using MS Access 2002 (10.2627.2625) which came with MS Office Pro. It
looks like it is dummed down to A2K for some reason which is fine because
I'm developing for an A2K box, but I was wondering about the odd
configuration. Nevertheless, I can't surface any querydef help content.
Can you suggest how I might open the door to these capabilities on my
Access version
Steve

[mooseshoes]

<snip>

Install the optional DAO component and the related help files.
Microsoft really blew it by messing with the help files that were included
with Access97.

Allen Browne has summed the details on this subject in this post:
http://groups.google.ca/groups?q=%22....net.au&rnum=2
"mooseshoes " <mo********@gmx .net> wrote in message
news:un******** ***********@new ssvr29.news.pro digy.com...

VBA Help and DAO Library now installed. I'll proceed with QueryDef.

Helpful article from Allen Browne. Thank you.

Steve

[Bri]

Steve,

Leave the Recordsource for the report blank and then in the Open Event
of the Report build the SQL string and assign it to the Recordsource.
The string can be built using info from the form. Here's an aircode example:

Sub Report_Open()
Dim stSQL as String

stSQL = "SELECT MyData.* FROM MyData " & _
"WHERE MyField='" & Forms!MyForm!My Criteria & "'"
Me.RecordSource = stSQL

End Sub

Hope this helps.

Bri

mooseshoes wrote:

All:

I'm using Access 2000 on a Windows XP platform.

My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or more
reports and use the query as the recordsource.

What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.

Using docmd.openrepor t is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.

Your advice is appreciated and I'm happy to provide further information upon
request.

Best,

Steve

[Lyle Fairfield]

mooseshoes <mo********@gmx .net> wrote in
news:Tb******** *********@newss vr21.news.prodi gy.com:

All:

I'm using Access 2000 on a Windows XP platform.

My goal is to use a form to gather user criteria which I will then parse
into a useable SQL string. At this point I would like to open one or
more reports and use the query as the recordsource.

What would be a common approach to doing this? I have been using ADO
methods up until this point for other tasks.

Using docmd.openrepor t is easy, but it doesn't accommodate a change in a
report's recordsouce from what I can tell although it does allow for SQL
filtering.

Your advice is appreciated and I'm happy to provide further information
upon request.

Best,

Steve

This is very old and rather clumsy code. But the idea is, I believe, sound.
RptCharges has, as its recordset, QryCharges. The code changes the SQL for
QryCharges, checks to see if there are any records, (<- the clumsy part)
when that query is opened, and if it finds any, opens the report, which, of
course uses the query whose SQL has been modified, and then does some other
stuff. Be careful of new client inserted line breaks.

**********
Const qryChargesName As String = "qryCharges "
Function fCreateAccountF ile(strAccountN umber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0). QueryDefs(qryCh argesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account = " &
_
Chr(34) & strAccountNumbe r & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumbe r &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges ", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountF ile "905 555-5555"
End Sub
**********

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date