Create a template report that can be used over and over again

[desdee]

Ok please forgive me, I have only just started using Access for my job, and in the space of a month I have gone from knowing nothing to creating multiple queries, etc (big woop for me). Anyways, I am now expected to create a report template. Let me give you a little background: we have sales teams and loads of products to sell. What we want to do is create easy to use reports, that the sales force can bang out easily depending on their criteria. So far I have created the formula and found out how to filter by criteria and then create a report from that. However; what I want to do now is creata e template report, so that all the sales team has to do is go into the formula, filter what they want and *poof* a report appears. I saved the template I was working on but when I input a new filtered formula in it, I lose some of my formatting and my title.

I hope this makes sense; please bear with my lack of technical knowledge/language.

Many thanks in advance

 

[lameid]

There are a couple different ways to go.

You can make your query that the report runs off of (the report record source) use controls on a form as criteria. Then you make a button on the form to run the report.

The other way is to write code that assembles the filter that runs the report based on what is filled is on the screen and use it to open the report (usually this code is written to run when a button on a form is clicked).

The former is a lot easier for a beginner so let's start there (it will run faster but is less flexible).

First you need to make an unbound form (One that does not have a recordsource / ~not used to enter or edit records).

Create a new form and go into design view (do not use a wizard).

Add controls like text boxes to the form for criteria you want to use giving them meaningful names.

Add a button to the form using a wizard to open/run your report (print preview)

Save your form and leave it open.

Open the query the report is based on (the report's record source) in design view.

Go to the criteria line for a field you want to use for criteria and right click.

Select build from the context menu.

In the window that pops up using the bottom left pane, under forms select Loaded forms and find your form (you could also find it under all forms but it is usually easier to just have the form open).

In the middle pane find and double click your control. Notice how it puts some code in the top window that points to the control. Click OK.

Save and close your query.

Now in form view of your new form, you should be able to enter values on your form and click the button and have the report show exact matches for what is in the query.

You can still modify the query to not do exact matches. The code reference (Forms!FormName!ControlName) is basically a variable so you can use any comparison operators you might want instead.