Solved

Report uses query recordset as recordsource.

Posted on 2000-04-28
5
496 Views
Last Modified: 2010-05-18
I have a parameter query that looks like this:

PARAMETERS [Project Title:] Text ( 255 ), [Beginning Date:] DateTime, [Ending Date:] DateTime, [Group:] Text ( 255 );
SELECT [Time Sheets].[Control Number], Projects.[Project Number], Projects.[Project Title], [Time Sheets].DBEH, [Time Sheets].[Task Name], [Time Sheets].[Week Ending], [Time Sheets].[Res Name], Resources.StandardRate, Resources.Group, [Time Sheets].[Extra Cost], [Time Sheets].[Act Sun], [Time Sheets].[Act Mon], [Time Sheets].[Act Tue], [Time Sheets].[Act Wed], [Time Sheets].[Act Thu], [Time Sheets].[Act Fri], [Time Sheets].[Act Sat]
FROM Projects INNER JOIN (Resources INNER JOIN [Time Sheets] ON Resources.[Project Res UniqueId] = [Time Sheets].ResourceUniqueId) ON (Projects.[Project Number] = Resources.[Project Number]) AND (Projects.[Project Number] = [Time Sheets].[Project Number])
WHERE (((Projects.[Project Title]) Like [Project Title:]) AND (([Time Sheets].[Week Ending]) Between [Beginning Date:] And [Ending Date:]) AND ((Resources.Group) Like [Group:]))
ORDER BY [Time Sheets].[Control Number], [Time Sheets].DBEH;

I use a form to set the parameters that Calls a Module that contains this code:

Option Compare Database
Public grst As Recordset
Public Function modFinancial(strTitle As String, Date1 As Date, Date2 As Date, strGroup As String)

Dim dbs As Database, qdf As QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("zqryFinancial")

qdf.Parameters("Project Title:") = strTitle
qdf.Parameters("Beginning Date:") = Date1
qdf.Parameters("Ending Date:") = Date2
qdf.Parameters("Group:") = strGroup

Set grst = qdf.OpenRecordset

DoCmd.OpenReport "zrptFinancial", acViewPreview
grst.Close
Set grst = Nothing

End Function

When the report opens, it acts like it doesn't have the parameters.  The report contains "me.recordsource = grst.Name" in the On Open event.  

I have placed stops within the code to see what is happening, everything appears to be working however, the report attempts to run the query again instead of accepting the parameters that should have been passed to it in the "Set grst = qdf.OpenRecordset" execution of the query.

What am I missing?  Is the report looking at the query instead of the recordset?
0
Comment
Question by:mdicks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 9

Expert Comment

by:BrianWren
ID: 2760876
The parameters are not part of the query name property, but are functionally provided in the modFinancial() function.

Brian

0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2760884
Hi mdicks,
you are right, so you can try to save a result of the query in some temporary table and then print report based on this temporary table.
Regards,
Dedushka
0
 
LVL 7

Accepted Solution

by:
Believer earned 50 total points
ID: 2760907
Instead of passing parameters to your query, you can:
DoCmd.OpenReport "zrptFinancial", acViewPreview,, strWhere

strWhere is the criteria clause of an SQL statement, so:
   strWhere = "([Project Title]='" & strTitle & "')"
   strWhere = strWhere & " AND ([Beginning Date]=#" & Date1 & "#)"
   strWhere = strWhere & " AND ([Ending Date]=#" & Date2 & "#)"
   strWhere = strWhere & " AND ([Group]=#" & strGroup & "#)"

These values:
   [Project Title]="My project"
   [Beginning Date]= 1/1/2000
   [Ending Date]= 1/31/2000
   [Group]= "My group"

Will yield:
([Project Title]='My project') AND ([Beginning Date]=#1/1/2000#) AND ([Ending Date]=#1/31/2000#) AND ([Group]='My group')

In place of the names I put in [], use the names of the fields in your query.  (I presume the names I took from your example are the names of the parameters.)
0
 

Author Comment

by:mdicks
ID: 2765832
I love this site!

I've used this OpenReport method plenty of times but, for some reason I just couldn't let go of the parameter query.  Thanks for helping me get back on course.

Matt
0
 
LVL 7

Expert Comment

by:Believer
ID: 2765979
You're welcome, that's what we're here for!
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question