Solved

Report uses query recordset as recordsource.

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 35
sql statement - 9 27
backup programme - VBA 3 24
Combo Box with a control source is "locked" 10 9
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

832 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