Run the same parameter query/report from multiple input forms in Microsoft Access 2010

Question

I have a Microsoft Access 2010 report that requires the user to enter start and end dates and a user ID.  I currently have a form set up to provide those inputs and launch the reports but now I need to create a second form that can launch the same reports.   The current form is visible only to the management team, who can see and launch all of the reports and the second form will be used for another group who will only be able to launch and view a limited selection of the reports.  

So my report query currently has criteria set as [forms]![frmMgmt_RptLaunch].[fldEmpID] and [forms]![frmMgmt_RptLaunch].[startdate] and [forms]![frmMgmt_RptLaunch].[enddate]. 

How do I tell it to use [forms]![frmTeam_RptLaunch].[fldEmpID] and [forms]![frmTeam_RptLaunch].[startdate] and [forms]![frmTeam_RptLaunch].[enddate] when I launch it from the new Team form?

Or am I going to have to set up a second copy of each report that uses the parameters from the Team launch form?

Or can someone provide a much better and more elegant solution that I just am not thinking of in my current state of stupiditty?

Thanks in advance!!

Answer 1

Instead of referencing the controls as parameters in the report's query, open the report from the form filtered to the date range by means of the WhereCondition argument of the OpenReport method, e.g.

Dim strCriteria As String

strCriteria = "EmpID = " & Me.fldEmpID & _

" And TheDate >= #" & Format(Me.StartDate,"yyyy-mm-dd") & _

"# And TheDate < #" & Format(Me.EndDate,"yyyy-mm-dd") & "# + 1"

DoCmd.OpenReport, "TheReport", _

View:=acViewPreview, _

WhereCondition:=strCriteria

Another option, and the one which was adopted in similar circumstances in my organisation, is to provide different classes of users with different front ends, each linking to the same back end.  A copy of the appropriate front end was installed in each authorised user's personal folder on the system, to which only they and the system administrators had access, the back end in a shared location.  In each front end the form, report and query definitions were tailored to the level of access for which the user was authorised, or those objects for which a user had no need were simply omitted from the relevant front end.   In your current case you'd simply use a form of the same name in each class of front end, designing it in each case for the relevant level of access.  The report and query would be unchanged.

Answer 2

Hi,

you can use rhe openargs option in DoCmd.Openreport to pass criteria to a report.

Also you can define TempVars to pass variable to the query.

Mimmo