1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved MS Access - select report from combo box

Discussion in 'Business Applications' started by SBaird, Dec 13, 2018.

Advertisement
  1. SBaird

    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).
     
  2. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    235
    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.
     
  3. SBaird

    SBaird Thread Starter

    Joined:
    Dec 13, 2018
    Messages:
    3
    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. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    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. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    235
    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: Dec 14, 2018
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    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.
     
  7. SBaird

    SBaird Thread Starter

    Joined:
    Dec 13, 2018
    Messages:
    3
    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.
     
  8. Sponsor

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 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1220465

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice