Hi,
hopefully someone here can help.
I have a bunch of different types of reports for one dbase, and was trying to figure out a way for them to be in a dropdown.
Is this possible?

thanks for the time,
Rpb

If you find out a way to do this please let me know. I've been trying to do this for months now and cant figure it out for the life of me. Iam very new at this and could use the help
Thanks
Tracey

I will suggest a way that you can use at your own peril. Access contains a number of system tables that are generally hidden. The one that contains the information you need is MSysObjects. The following query will produce a recordset containing a list of reports.

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

Microsoft does not document these tables because they do not intend that we use them. That's why you use them at your own risk. As far as I can tell this method will work in A2K as well as A97. But, who knows if the next version of Access will contain a MSysObjects table or that it will be the same format.

I will in include a list of all the object types I have encountered. For some of them I just guessed at what they defined. Your database may not contail all of these types and they also might contain types, I have not found in any of my databases.

Type Description
-32768 Forms
-32766 Macros
-32764 Reports
-32761 Code Modules
-32758 ? Admin
-32757 ? System stuff
1 Local Access Tables
2 MSysDb?
3 system stuff
4 Attached ODBC Tables
5 Queries
6 Attached Access Tables
8 Relationships

Remember! the recommended way to obtain this information is by writing code to read through the collections.

okay thanks for the info. pat it gave me a few ideas I believe I can get it to work.
Tracey-As soon as I finish up the rest of the dbase ill work on that and post a reply if I am successful

thanks,
Rpb

Thanks Robert

i have successfully created a drop down list for all my reports in a simple fashion. from what i read, you guys are looking for a drop down list too pull up your reports right? well if i understand what you guys are asking for then just reply and i will be back to help you guys out.

Did you put all reports in a dropdown and then when its selected have it either print or preview? thats what I'd like plse let me know .

thanks,
Rpb

right now i don't know how far you are at accomplishing this, but first things first.


first, create a table with all your reports. i reccommend creating a table for the combo box because this way you can always add different reports in this drop down combo box.

second, create the combo box and name it "report list". you can name whatever you want, but just make sure you name it something that will easily help you to remember. i'm sure you can do this.

third, create a command button that will print preview your report. here is an example. i named this command button "Download Report" make the neccessary changes for whatever you name this command button. this code goes in the onclick event.

Private Sub COMMAND_NAME_Click()
On Error GoTo Err_COMMAND_NAME_Click
Dim stDocName As String

If Me![NAME OF YOUR COMBO BOX] = "REPORT NAME IN COMBO BOX" Then stDocName = "NAME OF REPORT"

If Me![NAME OF YOUR COMBO BOX] = "REPORT NAME IN COMBO BOX" Then stDocName = "NAME OF REPORT"

If Me![NAME OF YOUR COMBO BOX] = "REPORT NAME IN COMBO BOX" Then stDocName = "NAME OF REPORT"

DoCmd.OpenReport stDocName, acPreview

Exit_COMMAND_NAME_Click:
Exit Sub

Err_COMMAND_NAME_Click:
MsgBox "Select a report!"
Resume Exit_COMMAND_NAME_Click

End Sub

notice that there are 3 if statements in the code. that's because i have 3 reports currently in my combobox.

This is alot of information at one time and i might have not explained it clearly. but if you have any questions... just reply...and i will back.

Gino,
I keep getting this error:
The expression On Click you entered as the event property setting produced the following error:
Ambigous name detected:
Download_Report_Click.

I deleted the command button and put a new one with different names and i get this error if you can help me ASAP i would appreciate it very much.
here is what i have in the OnClick:

Private Sub reports_command_Click()
On Error GoTo Err_reports_command_Click
Dim stDocName As String

If Me![reports list] = "Make" Then stDocName = "rptMake"

If Me![reports list] = "Division" Then stDocName = "rptDivision"

DoCmd.OpenReport stDocName, acPreview

Exit_reports_command_Click:
Exit Sub

Err_reports_command_Click:
MsgBox "Select a report!"
Resume Exit_reports_command_Click

End Sub



[This message has been edited by tracey75 (edited 04-27-2000).]

i am confused what you are asking. what report page? are you saying print previewing a report or are you saying the form where you are going to put this combo box? get back to me. i will be here.


yes, you do have to create a report first in order for this to work....

[This message has been edited by gino (edited 04-27-2000).]

GINO,

My fault i was being stupid and didnt read on. Forget what i said about that i deleted it but posted a new message if you can read it above the one you just wrote.

Greatly appreciated

going to the codes....in the event property...can you see Download_Report_Click? is Download_Report_Click anywhere in your codes?

did you name your combo box to reports lists?

[This message has been edited by gino (edited 04-27-2000).]

You were right it was copied again above that one. Yes reports list is the name of the combo box.
ANyways, now when i click on to preview report i get a message "Select a report" i do and it jsut says the same thing

notice i said did you name your combo box reports lists and you replied a different name as reports list..........please check that....name of combo box is critical.

The name of the combo box is REPORTS and the name of the command is reports command i dont see anything wrong with what i'm doing. Here is what i have. Go figure.Private Sub reports_command_Click()
On Error GoTo Err_reports_command_Click
Dim stDocName As String

If Me![Reports] = "Make" Then stDocName = "rptMake"


If Me![Reports] = "Division" Then stDocName = "rptDivision"

DoCmd.OpenReport stDocName, acPreview

Exit_reports_command_Click:
Exit Sub

Err_reports_command_Click:
MsgBox "Select a report!"
Resume Exit_reports_command_Click

End Sub


WHAT AM I DOING WRONG?????????HELP

i see that the codes are correct. its tough to say whats wrong but...yes the codes are correct but i can't see your combo box and its really tough for me to come up w/ a solution. these codes work well for me.

i know you got the knowledge but sometimes it happens to me where i misspell field names. also, what is in your combo box?

how it going?

In my combo box i have Make, Model, Division and User. My combo box name is Reports. If you want i might be able to send you the db.

go ahead and send it to me...but please make the file as small as possible... send it to frenzy916@usa.net

Wow....that all seemed pretty complicated. My simple mind uses this simple solution to pull and print reports from a combo box pull down list.

1. Create a table with 1 field for the "exact" names of your reports.

2.Create a form or use an existing form and create a combo box in design view.

3. Right click on combo box for properties. (Looking at ALL of the properties) In Row Source Type choose Table/Query. Take note of the name of the combo box before you scroll down to Row Source Type.

4. In Row Source choose Whatever you called your table.

5. Get out of properties (you can also do it from there, but I always get out). Right click on your combo box and click build event (you're going to build code)

6. Type docmd.openreport(tab)the[Name of the combo box you created]Notice the brackets. then type a comma and choose acviewPreview.
(Add error code around this statement if you like.)

7. Get out and chose Form view (or non-design view). Add whatever else you want around it...

Looks like this:
docmd.OpenReport[Name of Table],acViewPreview

you're done!!

Any time you add a report, add the exact name to your table.

That works for me - hope it helps.

i have a drop down list with reports but i cant see the reports desription
where can i find it?

You folks are making this painful. All you need to do is:

1. Put a combo-box on your form. Name it cboReports.
2. In the cboReports Properties, click on the Data Tab.
3. Set the Row Source Type to Table/Query
4. Copy/Paste Pat Hartman's earlier suggested code and
place it in the Row Source.
5. Ensure Bound Column is set to 1.
6. Click on the Event Tab. Create an event procedure for
the After Update event [not the On Click]
7. Place this one line in it:
docmd.OpenReport Me!cboReport, acViewPreview

The procedure in its entirety will look like:

Private Sub cboReport_AfterUpdate()
docmd.OpenReport Me!cboReport, acViewPreview
End Sub

8. Return to form view and select a report from the combo
Box. Upon selecting the report name, the report will
open in preview mode.

And if you don't want to use the Hidden System tables, use the AllReports collection as Pat also suggested.
Private Sub Form_Open(Cancel As Integer)
Dim obj As AccessObject, dbs As Object
Dim strReports As String

Set dbs = Application.CurrentProject
For Each obj In dbs.AllReports
If strReports <> "" Then
strReports = strReports & ";" & obj.Name
Else
strReports = obj.Name
End If
Next obj

Me.cboReports.RowSource = strReports
Me.cboReports = me.cboReports.column(0,0)

End Sub
Then use the afterupdate event procedure that raskew provided.

Dave