mdicks
asked on
Report uses query recordset as recordsource.
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("zqryFinanci al")
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?
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("zqryFinanci
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
You're welcome, that's what we're here for!
Brian