Show List of Multiple Reports to Print/Preview with Command Button 2

[Shaz1]

Hi,

I'm looking for some help on showing a list of reports with the click event procedure of a Command Button.

Code Usedrivate Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String

stDocName = "R-Equipment List"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130Preview_Report_Click:
Exit Sub

Err_Command130Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Command130Preview_Report_Click

End Sub

 

[FancyPrairie]

This query will list all of the reports in your database:

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

 

[Shaz1]

Hi FancyPrairie,

I thank you for you prompt response. Regretfully, I'm not too sure how it helps me out. I'm using Access and my posting applies to a form in VB. I believe you gave me a SQL statement and I don't how to apply that to VB.

 

[TheAceMan1]

How are ya Shaz1 . . .

You could use [blue]FancyPrairie's[/blue] SQL as the [blue]RowSource[/blue] for a combobox or listbox and use the [blue]AfterUpdate[/blue] event to open the proper form.

See Ya! . . . . . .

 

[Shaz1]

Hi Aceman1,

I'm doing well thanks. Thanks for adding to FancyPrairie's Posting. I do understand now how that will work. You both have been of great help. Thanks again for your suggestion.

Cheers!

 

[Shaz1]

Hi Aceman1 again,

I am creating a database on the go and using a Form as a "user data input" interface.
So I have found a new appreciation for learning code quickly!

Okay I've tried the suggestions - adding a combobox to my form and then added the SQL query to the Rowsource

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


1. The above gives me a blank list which I am unsure of how to add the Reports that I would like to see in the list for the user to select for printing or previewing.

2. I'm not clear on what should be entered in the AfterUdate Event.

 

[TheAceMan1]

[blue]The above gives me a blank list . . .[/blue]

Then [blue]your saying you have no reports in the reports window[/blue] of the DB. Is this true?

How did you setup the combo?

How did you enter the SQL in the [blue]RowSource[/blue] (should've been a simple copy & paste)?

See Ya! . . . . . .

 

[Shaz1]

Hi Aceman1,

I have a database with many existing reports that I would like to see in the combobox list. Unfortunately, I don't know how to link the reports to the combobox list. How should the combobox be setup?

Secondly, the SQL statement I cut and pasted the same text that FancyPrairie posted.

Shaz1

 

[TheAceMan1]

Shaz1 . . .

I know what I want to tell ya, but have no solid base to go by. So lets make one:
[ol][li]Copy [blue]FancyPrairie's[/blue] SQL.[/li]
[li]Open a new query in design view (do not select any tables, just close the [blue]Show Table[/blue] Dialog)
Note: I'll be referring to the view button on the toolbar, which is normally on the far left.[/li]
[li]Switch to [blue]SQL View[/blue] by clicking the view button

.[/li]
[li]Delete [blue]SELECT;[/blue] in SQL view and [blue]paste the copied SQL.[/blue][/li]
[li]Click the view button

.[/li][/ol]
Do you see your reports now?

See Ya! . . . . . .

 

[Shaz1]

Hi Aceman1,

I've had some problems with my computer and email. My computer is now running fine.
The solution you supplied works beautifully. Originally I pasted the SQL in RowSource of the Properties window. That is why is didn't work. Thanks so much for your dilegence and patience.

Cheers