Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Report uses query recordset as recordsource.

Posted on 2000-04-28
5
Medium Priority
?
502 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 200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

824 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