Form Menu to corral my reports 2

[molly]

I found an interesting free file on the internet that has a report form menu. I am still trying to figure it out.

So I wonder, Can you recommend a sample report form menu ?
that is easy.

i would like to post my report name into a table. then use the Report Form Menu to show all the reports from that table and use the scroll bar to get a report.

And then a double click to open the report from this menu.

And/Or click on the report once and then hit an Open report button.

Any suggestions?
thanks,
Molly

 

[TheAceMan1]

How are ya molly . . .

A link to the all powerful file would be a big help!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[missinglinq]

Here's a routine that creates a combobox that lists all reports in a database along with a button to print the selected one:

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:

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:

Private Sub PrintReport_Click()
   If Not IsNull(ComboReports) And ComboReports <> "" Then
     DoCmd.OpenReport ComboReports, acViewPreview  ' use acNormal to print without preview
   Else
     MsgBox ("You Must First Select a Report To Print!")
     ComboReports.SetFocus
   End If
   ComboReports = ""
End Sub

You should now be set! Select a report and click Print.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[molly]

To my new friend MissinglinQ !!
This is very intriguing and smooth. I like the straight forward small amount of code.

when i select a report in the combo box and then hit the command button, I get a bug. when i run the printreport button, i get a message that i must piok a report. which i did already. (i start my report names with numbers like 3007rpt Plan Report. does that matter to your routine?)

Then i click okay anyway. then i get a debug error and a yellow mark on ComboReports.SetFocus which is before the End IF.

it probably a simple fix for you.
i look forward to your answer since this is a promissing deal.

thaks Molly

 

[PHV]

i get a debug error
Any chance you could post the whole error message and your actual code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

Hi PHV. I made a dummy Access file with the attachment below to download. Just a simply test.

I appreciate your checking out my debug error.

This is a great idea by MissinglinQ and i bet that you get this to work.

Molly

 

[missinglinq]

Somehow you got an added space after your combobox name in the Name Property Box for your combobox. When going into the code window behind a form, if you've named a combobox ComboReports Whatever (with a space between the ComboReports and the Whatever)Access will place an underscore between the words, so that Access would recognize it as ComboReports_Whatever. If you delete the Whatever but don't hilite and include the space, Access will recognize it as ComboReports_ which is what happened here.

Go back into Design View, Right-Click on the combobox, click on Properties then Other. Hilite and delete the Name, making sure to include the space at the end. Now re-enter ComboReports. You should be set, the copy I downloaded now works fine.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[molly]

That's what it was. i had a blank somehow in the line. It works great. To me, you are now Thomas Edison jr. This is smooth and smart. thank you for sharing this tip. You are a true tek-tips hero.

 

[missinglinq]

Glad you got it working, Molly!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[fneily]

Don't know if you're interested, but to get really slick, see: thread702-1512387

Then the user can right click their mouse and get a list of reports. Just a thought.

 

[molly]

thanks Fneily for sharing a different approach. I look forward to seeing how i can apply it. I love these tricks.

so with both yours and Misinglinq's methods, i have reports covered.

now i need to see how i can eliminate a few buttons that i made which use a Query in datasheet view for input or changes. If anyone has an approach, similar to the last 2 methods, that would be neat. for now i am using misinglinq's method but will read up on fneily's. thanks. Molly

 

[fneily]

Not sure about your last request. Do you want to eliminate buttons? How about creating an Option group? The users justs clicks a radio button representing a query to run and using a select case structure in code runs the appropriate query.

 

[molly]

Hi fneily. Sorry for only getting back now. I haven't been at the site the past 2 days.

Yes, you are correct in what i am contemplating.

eliminating most of my buttons.
the method that you and Misinglinq figured out handled my reports.

But as you said, i have buttons which open up a query.

Your idea sounds real good. How do?
Molly

 

[fneily]

In the form's design view, bring up the Toolbox. You'll see a button near the top with a little XYZ on it. That's the Option Group Button. Click on it and draw a rectangle. It's starts the Option Group Wizard and asks for your labels. These would be descriptions of the queries you want to run. Proceed through the wizard. Each little radio button has an Option Value. Don't change them.
Click on the frame of the Option Group and give it a name on its property sheet.
On the AfterUpdate event of the Option Event, or on a command button, put: (substitute your name for OptionGroupName)
Private Sub OptionGroupName_Click()
Select Case [OptionGroupName]
Case 1
DoCmd.OpenQuery "Query1"
Case 2
DoCmd.OpenQuery "Query2"
Case 3
DoCmd.OpenQuery "Query3"
End Select
End Sub

Or in design view of your form, goto Access Help and search on Option Group. You'll see a list of examples.