Report list in a drop-down box

[BrookieOU]

Hey all, I have a problem I am trying to figure out. I have a form that has
a drop-down list of my reports. What I am trying to do is limit the
drop-down list to only certain reports (i.e. I have a form with a list of
employees and only want the drop-down to show employee related reports).
Anyone know if this is possible or the code I would use to do it? The coded
event I am using for the reports is the MySysObjects one.

I know how to set-up a cascading combo box based on tables where someone can
choose a category and then choose a report based on that category, but I was
wondering if there was a way to limit the reports from the outset without
having to choose a category first.

Thanks,
Brooke

 

[Nicholas Scarpinato]

If you wouldn't mind, we can help you out more effectively if we could see
the code you're using to do this. I'm pretty sure I can help you get this
sorted once I've had a look at the code.

 

[BrookieOU]

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like
"*Subreport*") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;

That is what I'm currently using, but it shows all the reports I have and I
want to be able to limit it to specific reports. I also tried this way:

SELECT "-1" as [ID], "(Manage Reports)" as [Report Name] FROM Reports UNION
SELECT "0" as [ID], "(Clear Reports)" as [Report Name] FROM Reports UNION
SELECT ID, [Report Name] FROM Reports WHERE ((([Object
Name])=[Application].[CurrentObjectName])) ORDER BY [Report Name];

I got that idea from a filter list I saw where there was a table set up with
the filters and the string linking the filters. I thought I could just set
up a table with the report names and the string to the report, but that
didn't work either.

And finally, I have set up a table with the report names and categories I
want and such and set up two combo boxes. In the first one the user selects
the category of reports they want and then that populates the 2nd one with
the reports available for that category and then that opens the report. But
I want my employee list form to only show employee reports without the user
first having to choose the employee category. Make sense?

Thanks for your help!
Brooke

 

[Nicholas Scarpinato]

I'm assuming the user selecting the reports is the user you want to filter
the report list by, correct?

If that's the case, then what you need is a field in your table containing
the list of users which designates their report category. Then you can use
code to look up the current user's category in that main table, and pull any
reports which belong to that category.

Here's a sample of some code I wrote to pull the list of reports using the
AllReports method:

*****
Dim obj As AccessObject, db As Object, ReportList() As Variant, i As Integer
Set db = Application.CurrentProject
ReDim ReportList(db.AllReports.Count)
ReportListBox.ColumnCount = 1
For Each obj In db.AllReports
If i = Empty Then i = i + 0
If obj.Name = "Submission Confirmation Sheet" Or obj.Name = "Shipping
Confirmation Sheet" Or obj.Name = "Credited Items Report" Or obj.Name =
"Barcode List" Or obj.Name = "Aging Table" Then GoTo SkipReportListing
ReportList(i) = obj.Name
ReportListBox.AddItem ReportList(i)
SkipReportListing:
i = i + 1
Next obj
*****

For your purposes, change this section:

to something along these lines:

ReportCategory =
Dlookup("[ReportCategory]","[Report_CategoryTable]","[ReportName] = '" &
obj.Name & "'")
If ReportCategory = Dlookup("[UserCategory]","[UserListTable]","[Username] =
'" & CurrentUser & "'") Then
ReportList(i) = obj.Name
ReportListBox.AddItem ReportList(i)
End If

You'll need to add a dim for ReportCategory, and if you don't already have a
global variable that stores the current user (or a location on a form that
holds this value), you'll need to add that as well. This method will build
your combo box list items report by report, and only add those items where
the user's assigned category matches the category assigned to the report in
the report/category table.


HTH,

Nicholas Scarpinato

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like
"*Subreport*") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;

That is what I'm currently using, but it shows all the reports I have and I
want to be able to limit it to specific reports. I also tried this way:

SELECT "-1" as [ID], "(Manage Reports)" as [Report Name] FROM Reports UNION
SELECT "0" as [ID], "(Clear Reports)" as [Report Name] FROM Reports UNION
SELECT ID, [Report Name] FROM Reports WHERE ((([Object
Name])=[Application].[CurrentObjectName])) ORDER BY [Report Name];

I got that idea from a filter list I saw where there was a table set up with
the filters and the string linking the filters. I thought I could just set
up a table with the report names and the string to the report, but that
didn't work either.

And finally, I have set up a table with the report names and categories I
want and such and set up two combo boxes. In the first one the user selects
the category of reports they want and then that populates the 2nd one with
the reports available for that category and then that opens the report. But
I want my employee list form to only show employee reports without the user
first having to choose the employee category. Make sense?

Thanks for your help!
Brooke

If you wouldn't mind, we can help you out more effectively if we could see
the code you're using to do this. I'm pretty sure I can help you get this
sorted once I've had a look at the code.

 

[Nicholas Scarpinato]

My apologies... I forgot to explain how to use the code!

The code I posted is VBA code. You'll need to add it to your form with the
combo box you wish to populate with the report list. I would add the code to
the Form Load section in the form's Properties. Also, make sure the combo box
is unbound (there's no need to bind the combo box to a query since this code
will generate the list), and you'll need to set the Combobox's Row Source
Type property to "Value List".

SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Name) Not Like
"*Subreport*") AND ((MSysObjects.Type)=-32764)) ORDER BY MSysObjects.Name;

That is what I'm currently using, but it shows all the reports I have and I
want to be able to limit it to specific reports. I also tried this way:

SELECT "-1" as [ID], "(Manage Reports)" as [Report Name] FROM Reports UNION
SELECT "0" as [ID], "(Clear Reports)" as [Report Name] FROM Reports UNION
SELECT ID, [Report Name] FROM Reports WHERE ((([Object
Name])=[Application].[CurrentObjectName])) ORDER BY [Report Name];

I got that idea from a filter list I saw where there was a table set up with
the filters and the string linking the filters. I thought I could just set
up a table with the report names and the string to the report, but that
didn't work either.

And finally, I have set up a table with the report names and categories I
want and such and set up two combo boxes. In the first one the user selects
the category of reports they want and then that populates the 2nd one with
the reports available for that category and then that opens the report. But
I want my employee list form to only show employee reports without the user
first having to choose the employee category. Make sense?

Thanks for your help!
Brooke

If you wouldn't mind, we can help you out more effectively if we could see
the code you're using to do this. I'm pretty sure I can help you get this
sorted once I've had a look at the code.

 

[aaron.kempf]

wow that sounds like a lot of work.

Did you hear that SQL Server Reporting Services does all of that for
you and it's free and faster?

-Aaron

 

[Tony Toews [MVP]]

Hey all, I have a problem I am trying to figure out.

Please ignore Aaron's posting as Aaron's answer to just about every
question is SQL Server and ADPs. No matter how appropriate his
response.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[BrookieOU]

Thanks everyone, I think I've figured it out! I've been trying to post a
reply for 3 days not, and it kept telling me the service was unavailable.
But, thanks for all the help.