You are here:

Using MS Access/Creating Report Templates

Advertisement


Question
QUESTION: Hello,

I use Access 2010. I manage a database, and I find that I frequently need to copy reports and change their "Record Source" to show the appropriate information.

Today I have been working on a series of reports populated with query results. The query criteria for two yes/no fields are required to be true, but there is no purpose for those fields to appear on the reports; they only tell the query if it should return a record among the results. (I hope this makes sense!)

I've tried to copy the report and replace the "Record Source", but because the report copy was based on the first query, it still tries to satisfy the yes/no fields and asks me to define the resulting empty parameters.

Is there a way to create a report template in Access 2010, or another quick way to get around designing three more reports?

Thanks in advance,

Cheryl

ANSWER: Instead of creating new reports, why not create a query that you can dynamically supply criteria for.

For example, if the Yes/No fields are only to select records, then put them on a form and reference the values from the form using the syntax:

=Forms!formname!controlname

Add a button to the form, to run the report.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: A little background:

My DB is to house basic data for participants in 4 studies. Participants can participate in one or more study. There is a table for participants, and tables for each study. I need to provide query results for each study, and I am not experienced enough with complex operations to feel confident that what you have suggested will work with my data.

The yes/no fields do serve a greater purpose in the database, but for this report, I don't need them to appear as a result.

ANSWER: Ok, lets approach this like so. Can you create a query that returns the data you want in the report? If so, what is the criteria for that query, and is the only thing that changes the criteria?

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Yes, I've already created the queries.
Here's the first one:
SELECT SiteRosters.ysnIRC001, SiteRosters.ysnPI001, SiteRosters.ysnMainContact001, SiteRosters.lkupRole, SiteRosters.strName, SiteRosters.strEmail, SiteRosters.ysnLeft001, Sites.strSiteName
FROM Sites INNER JOIN SiteRosters ON Sites.intSiteNumber = SiteRosters.intSiteNumRoster
WHERE (((SiteRosters.ysnIRC001)=True) AND ((SiteRosters.ysnPI001)=True) AND ((SiteRosters.ysnLeft001)=False)) OR (((SiteRosters.ysnIRC001)=True) AND ((SiteRosters.ysnMainContact001)=True) AND ((SiteRosters.ysnLeft001)=False)) OR (((SiteRosters.ysnIRC001)=True) AND ((SiteRosters.lkupRole)="Study Coordinator") AND ((SiteRosters.ysnLeft001)=False));

The criteria are the only things that change. The numbers 001 indicate the study, and that is what changes between the queries.

Answer
Ok, So you create a form that Where the users can enter the criteria. And in your query you enter in the criteria row:

=Forms!formname!controlname

where formname is the name of your form and controlname the name of the control. Then you set the query to be the Recordsource of the report. Add a button on the form to run the report. The user opens the form enters the criteria they want and runs the report.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.