Solved

Report uses query recordset as recordsource.

Posted on 2000-04-28
5
490 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

930 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now