Tech Support Guy banner
Status
Not open for further replies.

MS Access - select report from combo box

Solved 
2K views 6 replies 3 participants last post by  SBaird 
#1 ·
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).
 
See less See more
#2 ·
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.
 
#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.
 
#4 ·
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.
 
#5 · (Edited)
(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
 
#7 ·
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
Not open for further replies.
You have insufficient privileges to reply here.
Top