Open a report from a form

[kitty77]

I have a form (test) and I would like to open a report (records) based on the record I'm in on my form.
I would like to use a command button on my form. VBA please.

Thanks.

 

[theDBguy]

Something like?

DoCmd.OpenReport "ReportName", , , "RecordID=" & Me.RecordID

 

[kitty77]

Any way to be prompted for the ReportName?

 

[The_Doc_Man]

There is such a thing as an InputBox.

InputBox Function - Microsoft Support

support.microsoft.com

OR you could build a table listing the reports that you would allow to be opened in this way and make that list the target of a list box. Select one and click, there you go.

It is even possible to build a list of all available reports by scanning one of the system tables, but I don't recommend that since if you had one still under development and not ready for public use quite yet, it would be visible too.

 

[Pat Hartman]

You don't want users to have to know the EXACT name for your report. You can make a query that selects reports and lets the user choose from that. However, isn't there always a "however". To use this method, you need to create a naming scheme so that the RowSource query only selects main reports and not subreports as well as only the reports you want to include in this option.

A slightly better technique, is to make a separate table and add to that the report name which should be named using the naming standard for all object names and will not be user friendly and a user friendly name. The combo will then show the sorted user friendly names, and the OpenReport, will use the hidden computer friendly name.

That leaves us with filtering options. If the forms don't require filters, then there is nothing more to say but if the report does require filters, you need to provide a way on that form for the user to enter them. I've included a picture of one of my report open dialogs. Notice that the highlight is on the third item in the list. It shows four select options,
Care Manager which is optional.
Care Plan Status which is required - you can tell that because the name is bolded and underlined.
Status (which refers to items in the care plan) and is shown as an option group
Thru DT which defaults to the current date.

Then there is an Output To option and an Run button.

The report list and the options are defined in a table that powers the form. I can post a copy of the form and the table that powers it. It is not generic. I customize it for each new application. It is technically not normalized since the table will change with each new application but it is based on the fact that even complex applications have a limited set of criteria options used for most reports and that number is relatively small. I've never used more than a dozen so it is a quite manageable number.
The gray background samples are from a different app. And following that is a picture of the form used to enter the selection criteria and the last form is the recordsource behind the gray picture as well as the data entry form.

 

[kitty77]

You don't want users to have to know the EXACT name for your report. You can make a query that selects reports and lets the user choose from that. However, isn't there always a "however". To use this method, you need to create a naming scheme so that the RowSource query only selects main reports and not subreports as well as only the reports you want to include in this option.

A slightly better technique, is to make a separate table and add to that the report name which should be named using the naming standard for all object names and will not be user friendly and a user friendly name. The combo will then show the sorted user friendly names, and the OpenReport, will use the hidden computer friendly name.

That leaves us with filtering options. If the forms don't require filters, then there is nothing more to say but if the report does require filters, you need to provide a way on that form for the user to enter them. I've included a picture of one of my report open dialogs. Notice that the highlight is on the third item in the list. It shows four select options,
Care Manager which is optional.
Care Plan Status which is required - you can tell that because the name is bolded and underlined.
Status (which refers to items in the care plan) and is shown as an option group
Thru DT which defaults to the current date.

Then there is an Output To option and an Run button.

The report list and the options are defined in a table that powers the form. I can post a copy of the form and the table that powers it. It is not generic. I customize it for each new application. It is technically not normalized since the table will change with each new application but it is based on the fact that even complex applications have a limited set of criteria options used for most reports and that number is relatively small. I've never used more than a dozen so it is a quite manageable number.
The gray background samples are from a different app. And following that is a picture of the form used to enter the selection criteria and the last form is the recordsource behind the gray picture as well as the data entry form.View attachment 106898View attachment 106899
View attachment 106900View attachment 106901

Wow, that's impressive. Thanks!

 

[Pat Hartman]

It's more than you probably need but if you can get your head around it, it isn't hard to implement and you can keep reusing the concept and stop having to reinvent the wheel. I can't tell you how much time it saves on each new project when I don't have to come up with new designs for common parts of every application. EVERY application need to print reports, why not use something like what I posted. Once you develop it, change the column names and reuse it with a new theme that the new customer likes.

 

[HillTJ]

@Pat Hartman , very neat. Until now i've used a bit of code to populate a combo box with a recordset of 'actual' report names filtered by a 'like*' . That way it's dynamic but you have to get the real report name right for the 'like*' code to populate the combo box & the real report name is not the best description for the user. I like your approach a lot.

As you indicated, a technique you can reuse is important. My report selection goto has always been my bit of code that i recycle & just change the report name filter.

Have you ever thought of putting together a demo of this method?

It's amazing what i learn from reading other peoples threads!

 

[Pat Hartman]

I forgot I built one a long time ago. I've also attached an earlier, simpler version. The second (earlier) version only supports 4 variables in a popup form. I would actually combine the ideas if I were to do it again so rather than having the data entry fields on the list form, I would have them on a popup. That eliminates the need for the hidden form that the first example uses and means that the popup can be used whether it is visible or not. You just have to pass in the name of the report to be opened.

The older option has a different feature because it was intended to be used to run batch reports. So, the user selected all the reports he wanted to print (viewing works better one report at ta time) and entered the variables and pressed run. Then all the reports were sent directly to the printer. The newer option assumes the reports are printed/viewed one at a time.

Have fun.

 

[HillTJ]

Pat, thanks for sharing. Will definitely have a look at these. Thanks

 

[kitty77]

Yes, thanks.