Report "List"

[Very Basic User]

Hello,

I currently have about 15 reports that are all completed and ready to view.
What I want the user to have is a form that has a dropdown box. In the
dropdown box is a list of all available reports. After they select the report
they want to see, they hit run and up comes their canned report. Any help
would be appreciated!

 

[Maarkr]

from SteveS in another question in the group:

Set the combo box row source to (all on one line):

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=-32764));

 

[Very Basic User]

Thank you for helping, I'm not sure I'm following, So I would not use a query
at all? And are there sections of this code that I need to modify for my
database?

 

[Danny J. Lesandrini]

What SteveS wrote about MSysObjects is true, but probably not a realistic
solution.

What you first need is a list of reports, and Steve's query of MSysobjects
will give you that, but not in a way you want to present it to users. What you
want will look more like this ...

ReportName Description Active
rptCustList Current Customer List 1
rptSalesPerf Sales Performance Report 1


To launch the report, you need the Access report object name. If you name
them like the Description column, then Steve's suggestion to use MSysObjects
table will work, but even then, it limits you to a simple list.

Some day, you're going to want to do something like remove things from the
list (make them inactive) or you're going to have subreports which you don't
want to show up in the list. Anyhow, I suggest you create a report table that
looks pretty much like what I show above.

Now, you create a combo box with this SQL for the Row Soruce:

SELECT ReportName, Description
FROM tblReports WHERE Active=1
ORDER BY Description


Let's say your combo box is named cboReportSelect. You need to set its
column count to 2 and the bound column to 1. Set the column width property
to 0;2 and set it's length to 2". This will hide the first column, the one with the
object name, and show the description.

To get the report name, you reference the first column of the combo box.
By default, it will give the bound column, so this should work ...

Dim strRptName As String

strRptName = Me!cboReportSelect

Alternatively, you can reference columns by number, starting with Column 0

strRptName = Me!cboReportSelect.Columns(0)

Now you just launch the Report ...

DoCmd.OpenReport strRptName

 

[Very Basic User]

Okay, I feel like I'm 50% of the way there. I have the combo box listing the
reports by what I want them to be named, the only thing I"m struggling with
is how do I link the name I gave the report in my table to the actual report?
--
Thank you for your time!
John

What SteveS wrote about MSysObjects is true, but probably not a realistic
solution.

What you first need is a list of reports, and Steve's query of MSysobjects
will give you that, but not in a way you want to present it to users. What you
want will look more like this ...

ReportName Description Active
rptCustList Current Customer List 1
rptSalesPerf Sales Performance Report 1


To launch the report, you need the Access report object name. If you name
them like the Description column, then Steve's suggestion to use MSysObjects
table will work, but even then, it limits you to a simple list.

Some day, you're going to want to do something like remove things from the
list (make them inactive) or you're going to have subreports which you don't
want to show up in the list. Anyhow, I suggest you create a report table that
looks pretty much like what I show above.

Now, you create a combo box with this SQL for the Row Soruce:

SELECT ReportName, Description
FROM tblReports WHERE Active=1
ORDER BY Description


Let's say your combo box is named cboReportSelect. You need to set its
column count to 2 and the bound column to 1. Set the column width property
to 0;2 and set it's length to 2". This will hide the first column, the one with the
object name, and show the description.

To get the report name, you reference the first column of the combo box.
By default, it will give the bound column, so this should work ...

Dim strRptName As String

strRptName = Me!cboReportSelect

Alternatively, you can reference columns by number, starting with Column 0

strRptName = Me!cboReportSelect.Columns(0)

Now you just launch the Report ...

DoCmd.OpenReport strRptName

 

[Danny J. Lesandrini]

I'm not sure I understand the term "link" in your question.

Just enter the data in the table. The column [ReportName] will have the name
of the report object in the Access object window. The [Description] column
will have the pretty, display name.

By setting up the combo box so it has 2 columns, with the first one made
invisible by setting its column width to 0, you effectively hide it, but the combo
box is still "linked" to the ReportName column of the table, right?

Sorry I'm probably not helping with this description, but write back and I'll
try to catch on to your question better.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Okay, I feel like I'm 50% of the way there. I have the combo box listing the
reports by what I want them to be named, the only thing I"m struggling with
is how do I link the name I gave the report in my table to the actual report?

 

[Very Basic User]

This is where my lack of experience is going to make this difficult, but I
appreciate your help! So if I just copy and paste the name of the report into
"ReportName" and use the code you gave me to only see the description column,
that gets me what I want to see in the dropdown combo box.

I'm having trouble making the command button work though. Starting from
where you typed ... Do I have to add teh DIM..... to somewhere in the
properties?

To get the report name, you reference the first column of the combo box.
By default, it will give the bound column, so this should work ...

Dim strRptName As String

strRptName = Me!cboReportSelect

--
Thank you for your time!
John

I'm not sure I understand the term "link" in your question.

Just enter the data in the table. The column [ReportName] will have the name
of the report object in the Access object window. The [Description] column
will have the pretty, display name.

By setting up the combo box so it has 2 columns, with the first one made
invisible by setting its column width to 0, you effectively hide it, but the combo
box is still "linked" to the ReportName column of the table, right?

Sorry I'm probably not helping with this description, but write back and I'll
try to catch on to your question better.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

 

[Very Basic User]

I may have gotten closer, I did all that you stated, but now when I click on
the command button, it tries to send the report to the printer instead of
just opening it to view.
--
Thank you for your time!
John

I'm not sure I understand the term "link" in your question.

Just enter the data in the table. The column [ReportName] will have the name
of the report object in the Access object window. The [Description] column
will have the pretty, display name.

By setting up the combo box so it has 2 columns, with the first one made
invisible by setting its column width to 0, you effectively hide it, but the combo
box is still "linked" to the ReportName column of the table, right?

Sorry I'm probably not helping with this description, but write back and I'll
try to catch on to your question better.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

 

[Danny J. Lesandrini]

Yeah, I figured that might come up. You need to add a parameter to the DoCmd call
to tell it to preview. (Print is the default)

docmd.OpenReport "rptReport",acViewPreview

 

[Very Basic User]

Thank you so much! you have been so helpful that I'm alomost afraid to ask
one more question. Every thing is working perfect now, but I wonder if there
is a code that goes to preview (Not Print Preview) I have some edit command
buttons on the preview that don't work in "Print" preview.

Thanks again! you have been fantastic!
--
Thank you for your time!
John

Yeah, I figured that might come up. You need to add a parameter to the DoCmd call
to tell it to preview. (Print is the default)

docmd.OpenReport "rptReport",acViewPreview

 

[Danny J. Lesandrini]

No problem, we're glad to help.

I'm not sure I understand what you mean by "Preview" verses
"Print Preview". I mean, I know Access has a special preview
that shows a fraction of the data as a way to preview without
having to load a bunch of records, but is that what you had in
mind?

I think the acViewPreview is the full print preview, not the
abbreviated preview. Does it seem not to be so to you?
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Thank you so much! you have been so helpful that I'm alomost afraid to ask
one more question. Every thing is working perfect now, but I wonder if there
is a code that goes to preview (Not Print Preview) I have some edit command
buttons on the preview that don't work in "Print" preview.

Thanks again! you have been fantastic!

 

[Very Basic User]

The reports that you helped me with all have a command button that can be
used to open a form to allow for edits. In print preview these command
buttons are not able to be used. I just changed your line from...

docmd.OpenReport "rptReport",acViewPreview

To...

docmd.OpenReport "rptReport",acViewReport

And now it is working 100%. I got lucky on this one...

Thanks again, you have been very helpful!

 

[Danny J. Lesandrini]

John:

Now I'm getting confused. Reports don't have command buttons, but forms
can be viewed in Print Preview mode. Is it an Access Report or Form that
you are launching in code?

Also, I checked the object browser and can't even find a constant named
acViewReport. If it's working for you, then great, but I can't explain this.

 

[Hans Up]

Now I'm getting confused. Reports don't have command buttons, but forms
can be viewed in Print Preview mode. Is it an Access Report or Form that
you are launching in code?

Also, I checked the object browser and can't even find a constant named
acViewReport. If it's working for you, then great, but I can't explain this.

I don't have Access 2007 to confirm, but my previous Google search for
acViewReport led me to believe that constant is new with 2007.

Maybe 2007 reports also allow more interactive features like command
buttons.

Hans

 

[Danny J. Lesandrini]

Hans, thanks for that ... I think you're right.

 

[Very Basic User]

I'm more of a hacker in access to be able to help with why, but I do have
2007. I have a button at the top of my "report" that opens a form for editing
the record. In fact, I have three buttons, one to open a form for editing,
and two to open additional more detailed reports. So I have the record # in
the report, you hit the button and I have a criteria statement in the query
that asks what item # you want to edit, you type in the # and it opens the
form for editing. Then I have an on close macro that closes and opens the
report again so the record is immediatly updated on the report for all users
to immediately see the change they made. I just tried the (docmd.OpenReport
"rptReport",acViewReport) change to report, because that was one of the
options in view for a report. It all seems to be working.