Solved MS Access - select report from combo box

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

SBaird

Thread Starter
Joined
Dec 13, 2018
Messages
3
Utilizing Access 2016.

All this is probably pretty easy for you guys, but I'm a complete novice with grand ideas I am currently unable to implement on my own.

I have a form (splash page).
I have 8 reports that provide summary info for specific departments.
I have 8 reports that provide detailed info for same.
I have 8 reports that provide ALL data for all departments.

All reports are based on their own query's and they work just fine.

What I want is a combo box on the splash page that will allow a user to select a specific report for viewing (and printing if they so desire). I'll need three combo boxes (one for dept summary, one for dept detail, one for all) to try and keep this as button-driven and easy to navigate for the users. Users never manipulate/add/delete data... they only need to view the reports.

So.. what is the easiest way to create a combo box that will pull up a specific, selected, report? Additionally, I would like to have the reports named in the combo-box to be different than the actual report name. I would like the combo box to show/call report "ABCD", even though the report is named "ABCD_ALL_RPT".

I imagine I'll need the combo-box and a "view" button (or something similar).
 
Joined
Jul 9, 2018
Messages
561
First Name
Jack
Hello SBaird,

If you would like to go the VB route, I would have to refer you to one of the other VB gurus on the site. If you want to use Access native functions, this can be accomplished through the following steps.
  1. Create a New Table with your desired Report Names.
  2. Add a Combo Box to the Splash Page (pulling from the Report Name field in the New Table).
  3. To show the Report Names within the Combo Box, modify the Control Source within the newly created Combo Box to reflect the Report Name field.
  4. Add a Button for viewing/opening reports.
  5. Modify the underlying Macro for the New Button with If/ElseIf statements to open a designated report based upon the selected Combo Box entry.
I hope this helps.
 

SBaird

Thread Starter
Joined
Dec 13, 2018
Messages
3
Hello SBaird,
Just starting, so I am sure I am screwing this up.

The below is attached to the button 'command5'... and it's not working.


****************
Private Sub Command5_Click()
If Combo3 = "FEAJ Summary" Then
DoCmd.OpenReport "MIC_summary_rpt_feaj", acViewPreview
ElseIf Combo3 = "FEIW Summary" Then
DoCmd.OpenReport "MIC_summary_rpt_feiw", acViewPreview
End If
End Sub
****************

What am I doing incorrectly?

Oh, and thank you for your time, effort, and understanding. I really do appreciate it.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You need to check that your combo selection actually does equal "FEAJ Summary" and "FEIW Summary".
How many columns does your combo have?
You could add a new first line of code
msgbox Combo3
to find out.
There is an alternative to a combo, ie to have 24 command buttons, each one opening a report.
 
Joined
Jul 9, 2018
Messages
561
First Name
Jack
(Edit - Premature hotkey post - my apologies.)

Not sure how that would look using VBA. Perhaps using an approach of If Me.Combobox.Column(1) = "FEAJ Summary" Then Have to leave that advice up to OBP though. ;)

If you were creating/modifying an underlying macro by using the Property Sheet - Button - On Click - ellipses, it would look like the following (assuming that the combo box field name is "Combo 3"):

If [Combo 3] = "FEAJ Summary" Then

Open Report
Report Name MIC_Summary_rpt_feaj
View Print Preview
Else IF
 
Last edited:

OBP

Joined
Mar 8, 2005
Messages
19,895
Chawbacon, the code that SBaird posted is the standard for opening a report and should work OK, providing the Combo is giving the correct information.
 

SBaird

Thread Starter
Joined
Dec 13, 2018
Messages
3
Chawbacon, the code that SBaird posted is the standard for opening a report and should work OK, providing the Combo is giving the correct information.
I finally figured it out. The combo box is retrieving data (report names) from a table and was, indeed, NOT giving the correct information. I'm not exactly sure why... but something in your response made me look at that. lol

Pro-tip: When using an unbound combo box to provide the source data ("FEAJ Summary"), ensure the proper COLUMN is bound. Column 1 is, by default, the record ID.

Once I change the combo properties to "Bound column" of 2.... where the "FEAJ Summary" report name is... it worked perfectly.

Thank you all so very much.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top