473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How To Use Dynamic Queries As Report RecordSource?


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.openreport 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
Nov 13 '05 #1
9 14849
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******@pcdatasheet.com
www.pcdatasheet.com
"mooseshoes" <mo********@gmx.net> wrote in message
news:Tb*****************@newssvr21.news.prodigy.co m...

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.openreport 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

Nov 13 '05 #2


<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
Nov 13 '05 #3
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*****************@newssvr21.news.prodigy.co m...

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.openreport 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

Nov 13 '05 #4
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*******************@newssvr29.news.prodigy. 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

Nov 13 '05 #5
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.txtCompareValue)

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.optCriteriaType
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.lstFieldNames & ")"
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.RecordSource = MySQL
End Sub

'------------------------------------------
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click

Dim stDocName As String

stDocName = "rptContacts"
DoCmd.OpenReport stDocName, acPreview, MySQL

Exit_cmdOpenReport_Click:
Exit Sub

Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click

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

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

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*****************@newssvr21.news.prodigy.co m...

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.openreport 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

Nov 13 '05 #6
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*******************@newssvr29.news.prodigy. 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

Nov 13 '05 #7
<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*******************@newssvr29.news.prodigy. com...


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

Helpful article from Allen Browne. Thank you.

Steve
Nov 13 '05 #8
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!MyCriteria & "'"
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.openreport 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

Nov 13 '05 #9
mooseshoes <mo********@gmx.net> wrote in
news:Tb*****************@newssvr21.news.prodigy.co m:

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.openreport 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 fCreateAccountFile(strAccountNumber As String)
Dim qdf As QueryDef
Dim rcs As Recordset
Dim strFullPath As String
Set qdf = DBEngine(0)(0).QueryDefs(qryChargesName)
With qdf
.SQL = "SELECT * FROM tblCharges WHERE Sent = No And Account = " &
_
Chr(34) & strAccountNumber & Chr(34)
Set rcs = .OpenRecordset
With rcs
If .RecordCount <> 0 Then
strFullPath = Environ("temp") & _
"\" & "Chargeable Messages for " & strAccountNumber &
".rtf"
DoCmd.OutputTo _
acOutputReport, "rptCharges", acFormatRTF, strFullPath,
False
End If
End With
End With
Set rcs = Nothing
Set qdf = Nothing
End Function

Sub test()
fCreateAccountFile "905 555-5555"
End Sub
**********

--
Lyle
--
use iso date format: yyyy-mm-dd
http://www.w3.org/QA/Tips/iso-date
Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: ano1optimist | last post by:
Is it possible to create an ADO recordset as set it as the report recordsource? I was able to use this logic on forms but have been unable to use it on reports. HELP.
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: mtech1 | last post by:
Access 2002 I am trying to create a dynamic crosstab report that parameters come from 3 different forms. I get runtime error 3070 - The Microsoft Jet database engine does not recognize...
0
by: Benny | last post by:
Hello Experts, Currently Im working on a web application using asp.net with C#. I need to create a report depends on user's selection of the database fields, i.e. the user may want to display...
1
by: Robert Strickland | last post by:
I need to create a dynamic PDF report based on values pulled from the database. I am using a XSL-FO rendering component that takes my FO and creates a PDF stream. Ii would like to send back the PDF...
1
by: Hank | last post by:
I'm using Access 2000 and want to set up the dataset for a subreport. I trying to use the Forms!SubForms scenario as a guide but I cannot get it. Here is my try: Reports!!.Report.Recordsource =...
3
by: WindowsAndDoors | last post by:
Hey, I'm trying to assign to a report recordsource a variable name but I keep getting a message that the database isn't recognizing the control name. The name of the report is...
1
by: khushbubhalla | last post by:
I am writing a procedure in which i am using dynamic queries. I am assigning the dynamic query to a variable and then executing the query . but as in when the query grows i can t keep it in a...
1
by: vilas556 | last post by:
I am developing Desktop application and i want to use Dynamic Crystal report then how I Assign Dynamic Path Of Database to crystal report.
14
ollyb303
by: ollyb303 | last post by:
Hi, I am trying to create a dynamic crosstab report which will display number of calls handled (I work for a call centre) per day grouped by supervisor. I have one crosstab query (Query1) which...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.