You are here:

Using MS Access/One date range form, multiple reports

Advertisement


Question
QUESTION: Hello,

I have a question regarding the best practice on how to use a date range form.  I like using a date range form to enter start and end dates for report queries.  Because I am using a different report each time, this is the process I was using to accomplish this task:

1.  Create the report in design view
2.  Create a query to select out the information I want for the report.
3.  Create a new date range entry form.
4.  In the query, enter the start and end dates as criteria.
5.  Using a macro in the On Open event of the report, I open the date range form, created in #3, in edit mode.
5.  Using a macro on the On click event of the date range form, I open the report from a command button "Run report" on the date range form and then close the date range form.

The problem with this process is that I have to create a new date range form for every report I write.  This is creating a lot of unnecessary forms I think, and not good database design.  I would like to be able to have one generic date range form for any report needed.  I have been told this is best done in VBA, but I am just learning that.  For most stuff I find code on the Internet, study it, and adjust it to meet my needs.

I have not found anything for what I want to do that is detailed enough for me to learn from and adapt to my use.

Can you point me in the right direction?

Thanks,

Rick

ANSWER: First, I would not open the form from the report. That's a wasted step. You don't say how you are launching your reports, but I would open the date range form directly.

The next point is where things can be a bit different depending on how you work. I'm going to assume that this is an app used by others, not just yourself. If its just yourself some of this doesn't matter.

What you can do is put buttons on your date range form to launch each of the reports that would use the date range. No VBA required here as you can just create a macro to run the report. However, if you want to not confuse users with a lot of buttons, you can hide all but the button needed or set up a button to run a specific report.

I can expand on this and give you some detail if you want to let me know whether its multi-user, how you launch your reports etc.

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: Sorry about not responding sooner, I thought I had entered follow up information correctly, but it never showed up.

This is a multiuser environment. The report is launched from a switchboard item.  For example,

1. Report 1
2. Report 2

Once the user selects which report they want, I use a macro to open the specific date range form.  They enter the start and end dates, and a on-click event for the form control runs the report.

This creates a lot of redundant date range forms.

Rick

Answer
You refer to a switchboard, Was this switchboard created using Switchboard Manager or just your own.

One way to deal with this, is to use the OpenArgs argument of the OpenForm method to pass the name of the report to the form.

So you have one form with one button, but the code behind the button would look like:

strDocument = Me.OpenArgs
DoCmd.OpenForm strDocument

This makes the form more dynamic and allows you to use one form for multiple reports.

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.