Access 2003 choose selection for report

Posted on 2007-09-28
Last Modified: 2013-11-28
I have a Access 2003 database. I am creating a report. When the report is opened I would like to be able to have the user select from a drop down what they want. Example: the choices to pick from would be DC DC5 or EW. When they picked from one of those it would bring all the recrods up for that. I could put in the query so that they would have to type in those options but it would be nice to have a drop down.
Question by:Pdeters
    LVL 65

    Accepted Solution

    (1)  Create a form that looks somewhat like a message box, with a 'Choose your choices' lablel, and a combo box with these DC, DC5, EW choices.
    (2)  In the query that serves as your form's record source, write criteria in the DC, DC5, whatever column that has [Forms]![That Form Name]![That Combo Box Name] where the user made their selection.
    (3)  Back on the form, add an Ok button that fires the report.  The report calls the query as the record source, which grabs the value from the form, and returns data to the report accordingly.

    Author Comment

    for the (1) form that the message box is in - do I create a query or jsut a combo box.
    LVL 34

    Expert Comment

    you can also pass your DC, DC5 or whatever that you select from the combo box as criteria in your openReport command. Whichever's easier for you.
    Hey Ho J-Ho!
    LVL 65

    Expert Comment

    by:Jim Horn
    >do I create a query or jsut a combo box.
    The form is unbound, so no query.  Just a form that displays a 'Enter something here' label, and a combo box/text box that holds it.
    (hey jw)

    Author Comment

    Is there a way that what is in the combo box to choose from can be automatically pulled from the query?
    I have a query (table) that has a fiield that ony holds thos options DC or EW, but if another option is added can it automatically be a choice in the combo box? For exampel if I were to use th is for a choice of a person that may be in a field more than once.

    Author Comment

    I have the combo box made with the DC EW selections but I am not getting the next step.

    2)  In the query that serves as your form's record source, write criteria in the DC, DC5, whatever column that has [Forms]![That Form Name]![That Combo Box Name] where the user made their selection.

    LVL 19

    Assisted Solution

    normally you would just want to filter the report to the relevant records so you would not need to rebuild or redefint the query sql unless you are getting the reports records from a different table or sets of table altogether
    if its the former, then you just need to use the "WHERE" argument of the docmd.openreport method as shown below. if you need to display numerous "unrelated" records from the underlying query you can insert your combo values into a temp table and reference that in your "where" clause.

    for eg, if your DC, DC5 and EW are values for a field called "myField",
    and this field is naturally one of the fields in your report, try below and replace names accordingly

    put this code in the form where your dc, dc5 combo box is.

    Dim sWhere As String
        sWhere = "myFieldID = '" & me.mycombo & "'"  'whatever combo storing DC DC5 or EW etc
        'or you could insert these combo values into a "temp" table and then set your "where" clause to this temp table
        sWhere = "myFieldID in (select myFieldID from mytempTable)"
        DoCmd.OpenReport "myReport", acViewPreview, , sWhere

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now