Solved

Report uses query recordset as recordsource.

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

636 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