Reports Help

[danwhitty]

Can anyone help regarding how to use a button on a form to load a query to allow the user to search for data within the database and then display the search results straight onto a report, rather than the query window loading and displaying the results. Any help would be great.

 

[Sum Guy]

I do this frequently by way of a macro.

In the query for your report enter in the criteria line for the field you wish to use on your form to generate the report:
[Forms]![FormName]![textboxname]
Where FormName is the name of the form you are using
and textboxname is the name of the textbox on the form that contains the information.

Now create your macro:
Open Query - the query that controls the report
Open Report - the report you want to run - Enter the name of the query in the filter.
Set the report to print preview until you are sure everything is running OK
Close - Query - the query that controls the report or else it will come up on the screen when you're finished viewing the report.
Optional - Close - Form - the form you were using to enter the query criteria.

Now place a command button on the form to run the macro.

When everything is working the way you want, change the command on the Open Report line of the macro to Print from Print Preview.

 

[boblarson]

I do this frequently by way of a macro.

In the query for your report enter in the criteria line for the field you wish to use on your form to generate the report:
[Forms]![FormName]![textboxname]
Where FormName is the name of the form you are using
and textboxname is the name of the textbox on the form that contains the information.

Now create your macro:
Open Query - the query that controls the report
Open Report - the report you want to run - Enter the name of the query in the filter.
Set the report to print preview until you are sure everything is running OK
Close - Query - the query that controls the report or else it will come up on the screen when you're finished viewing the report.
Optional - Close - Form - the form you were using to enter the query criteria.

Now place a command button on the form to run the macro.

When everything is working the way you want, change the command on the Open Report line of the macro to Print from Print Preview.

You don't need to open the query, and in fact you don't need the criteria in the query. What you can do is have a generic report that has all of the fields you want displayed and then by opening it with a where clause you can limit it to what you want.

Check out my sample here:
http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

This sample lets you open a report based on the input for a form. It includes a combo box, and two dates (Start and End). It will, in the VBA behind the form, let you choose any and or all of those and then open the report based on what you selected.