How to list all available Reports in a combo box?

**dima69** · Oct 21 '07, 07:51 AM

Originally posted by clarencelai

Hi..

I am constantly creating reports for a group of users.

To simply the user interface, I would like to create a form that will list the reports in a drop down list (combo box).

The user would just need to select the desired report from the drop down list and click on a print button.

How can I create the drop down list that list all available reports?

Regards,
Clarence

1. Set user-defined function for RowSourceType property of your combobox (drop down list) - see Access help for RowSourceType for the details.
2. To create a list of the reports, use something like this:
[CODE=vb]Dim doc as Document
For Each doc in CurrentDB.Conta iners("Reports" ).Documents
AddToMyList doc.Name
Next[/CODE]

**missinglinq** · Oct 21 '07, 01:00 PM

Here's the step-by-steps from a routine I ran up a while ago:

Place a combobox on your form. When the Wizard comes up click on Cancel. Go into the combobox’s Properties

Set Name to ComboReports

Set Row Source Type to Table/Query

In the RowSource box enter this:

Code:

 SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

Create a command button. When the Wizard comes up, click on Cancel.Go into the button’s Properties.

Name the button PrintReport

For the button’s Click event use this code:

[CODE=vb]Private Sub PrintReport_Cli ck()
If Not IsNull(ComboRep orts) And ComboReports <> "" Then
DoCmd.OpenRepor t ComboReports, acViewPreview ' use acNormal to print without preview
Else
MsgBox ("You Must First Select a Report To Print!")
ComboReports.Se tFocus
End If
ComboReports = ""
End Sub
[/CODE]
You should now be set! Select a report and click Print.

As noted in the code, on Line # 3 use acNormal instead of acViewPreview if you want the reports to print without viewing them first.

Welcome to TheScripts!

Linq ;0)>

**clarencelai** · Oct 22 '07, 05:03 AM

Hi Linq,

You have been a great help!

I stumbled into this website and get very prompt response to all my questions posted.

I am impressed!

Cheers!
Clarence

Originally posted by missinglinq

Here's the step-by-steps from a routine I ran up a while ago:

Place a combobox on your form. When the Wizard comes up click on Cancel. Go into the combobox’s Properties

Set Name to ComboReports

Set Row Source Type to Table/Query

In the RowSource box enter this:

Code:

 SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

Create a command button. When the Wizard comes up, click on Cancel.Go into the button’s Properties.

Name the button PrintReport

For the button’s Click event use this code:

[CODE=vb]Private Sub PrintReport_Cli ck()
If Not IsNull(ComboRep orts) And ComboReports <> "" Then
DoCmd.OpenRepor t ComboReports, acViewPreview ' use acNormal to print without preview
Else
MsgBox ("You Must First Select a Report To Print!")
ComboReports.Se tFocus
End If
ComboReports = ""
End Sub
[/CODE]
You should now be set! Select a report and click Print.

As noted in the code, on Line # 3 use acNormal instead of acViewPreview if you want the reports to print without viewing them first.

Welcome to TheScripts!

Linq ;0)>

**clarencelai** · Oct 22 '07, 05:13 AM

Hi...

Thanks for your help!

It works!

Cheers!
Clarence

Originally posted by dima69

1. Set user-defined function for RowSourceType property of your combobox (drop down list) - see Access help for RowSourceType for the details.
2. To create a list of the reports, use something like this:
[CODE=vb]Dim doc as Document
For Each doc in CurrentDB.Conta iners("Reports" ).Documents
AddToMyList doc.Name
Next[/CODE]

**missinglinq** · Oct 22 '07, 12:35 PM

Glad we could help!

Linq ;0)>

**missinglinq** · Nov 1 '07, 02:43 AM

A request was received thru a PM asking if there was a way to modify the above combobox in order to only retrieve certain reports, rather than all reports in a database.

This can be accomplished by assigning a prefix to all reports that you want to appear in the combobox and modifying the RowSource SQL statement to pull only those reports bearing that particular prefix.

If you have reports named

ReportA
ReportB
ReportC
ReportD
ReportE

and you only want the combobox to display

ReportA
ReportC
ReportE

then change the names to to add a 3 character prefix. For demonstration purposes we'll use acc, but you could use any 3 characters.

accReportA
accReportC
accReportE

Then as the RowSource for the combobox use

Code:

SELECT MSysObjects.Name FROM MsysObjects WHERE (Left$([Name],1)<>"~") And [b](Left$([Name],3)="acc")[/b] And (MSysObjects.Type)=-32764 ORDER BY MSysObjects.Name;

The bold part of the SQL statement tells Access to only select reports whose first three characters is acc.

Obviously, when choosing a prefix, care needs to be taken not to choose three characters which are also the first three characters of an existing report that you don't want included!

Linq ;0)>

**mb60** · Jan 20 '10, 08:43 PM

Originally posted by missinglinq

Here's the step-by-steps from a routine I ran up a while ago:

Place a combobox on your form. When the Wizard comes up click on Cancel. Go into the combobox’s Properties

Set Name to ComboReports

Set Row Source Type to Table/Query

In the RowSource box enter this:

Code:

 SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];

Create a command button. When the Wizard comes up, click on Cancel.Go into the button’s Properties.

Name the button PrintReport

For the button’s Click event use this code:

[CODE=vb]Private Sub PrintReport_Cli ck()
If Not IsNull(ComboRep orts) And ComboReports <> "" Then
DoCmd.OpenRepor t ComboReports, acViewPreview ' use acNormal to print without preview
Else
MsgBox ("You Must First Select a Report To Print!")
ComboReports.Se tFocus
End If
ComboReports = ""
End Sub
[/CODE]
You should now be set! Select a report and click Print.

As noted in the code, on Line # 3 use acNormal instead of acViewPreview if you want the reports to print without viewing them first.

Welcome to TheScripts!

Linq ;0)>

sir
the report is not responding

**missinglinq** · Jan 20 '10, 10:27 PM

Well, as you can see from other posts above, stating that it works for those posters, the code obviously is correct! The inference then would be that you've made a mistake in copying or utilizing it, or perhaps something unrelated is involved.

It would probably help us to help you if you would post your exact code. for us to look at.

You should also include the version of Access you're using.

And what does "the report is not responding" mean? The combobox doesn't pull up the names of the reports in your database? Or after pulling the names up the command button doesn't open the chosen report?

Linq ;0)>

**mb60** · Jan 21 '10, 01:26 AM

sir
I am using ms office 2007

1) in the combo box rowsource the code I copied is
SELECT [MSysObjects].[Name] FROM MsysObjects WHERE (Left$([Name],1)<>"~") And ([MSysObjects].[Type])=-32764 ORDER BY [MSysObjects].[Name];
the above one is worked

2) In the button even I copied the following code

Private Sub PrintReport_Cli ck()
If Not IsNull(ComboRep orts) And ComboReports <> "" Then
DoCmd.OpenRepor t ComboReports, acViewPreview ' use acNormal to print without preview
Else
MsgBox ("You Must First Select a Report To Print!")
ComboReports.Se tFocus
End If
ComboReports = ""
End Sub
Thank you

**mb60** · Jan 21 '10, 06:13 PM

Sorry sir
I did mistake in copying the code. So I couldn't get result.
I am very happy to inform you that the code given by you worked excellently.
Thank you very much

**karlgorringe** · May 12 '16, 01:24 AM

Hi I have been trying to get this working for a while now. I can get the combo box working no problem, but the button just won't work. I realise this is an old thread, and I am using Access 2013 so am unsure if the VB code needs to be altered. I have checked and double checked the button setup but to no avail.
Karl

**MikeTheBike** · May 12 '16, 09:58 AM

Hi Karl

It is not terribly helpful just saying it is not working.
I think it is unlikely that the code would be different in A2k13 (having tried the query in A2k13).

Perhaps if you posted the combo box Row Source query and the button code, and what actually happens when you click the button, we might be able to help.

Have you verified what the combo box is returning when you press the click the button via msgbox or debug.print?

MTB

**zmbd** · May 12 '16, 09:51 PM

karlgorringe
This is a very old thread.
Please start a new thread so that we can focus on your particular circumstances.

Thnx

How to list all available Reports in a combo box?

How to list all available Reports in a combo box?

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment