Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2007
    Posts
    10

    Red face Unanswered: Creating Report from drop down

    Hi,

    I have created a database that seems to be coming along nicely.

    However I have came across a stumbling block. Hence why I'm here

    I would like to be able to produce reports from one of my tables. Simple enough. However I would like the user to select from a dropdown or something to that effect. The soecific information on that report.

    Hopefully this example will make sense:

    If you have a database for all students in a school. And you only want a report from the students within "Maths". Now I know I could use a query, but I need the user to be able to select what the filter is.

    Does this make sense?

    Please assist

    Thanks in advance

    jmcall10

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so use a filter
    I'd suggest he best way to handle this is to have a parameters form with all your relevant filter/selection criteria on (eg class, age, gender / whatever), then pull thiose filters through ontot he report.

    base the report on the relevant query
    then apply filters when you run the report.. havea look at the docmd.openreport function....

    set the filters for the report and then on a button trigger the report to open with the required filter to limit data accordingly. the wizard behind the button should get you to open the repoprt, you just need to apply the filter correctly.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I think the basics of what you want is a custom dialog box (reporting dialog) that has a combo box on it that allows the user to select Maths. Then when the user click OK, you code the openreport action to open the report with a filter based on the combo box selection.
    Last edited by StarTrekker; 02-13-08 at 08:06.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2007
    Posts
    10
    Ok, these all sound like its what I'm after. However Im a little bit a beginner when it comes to access.

    I used the school example but really I should prob give my example.

    I have a table with

    ProjectId <-- primary key
    Location
    Priority

    Basically the Location will be what Id like the user to choose from.

    So When the user clicks the drop down, it lists all locations within the table.

    Now a project may be in the same location so I wouldne want it to list duplicates.

    Please keep in mind I am not the best at this so would need a laymans term explanation.

    Sorry if its not clear, please feel free to ask any questions

    Thanks in advance

    jmcall10

  5. #5
    Join Date
    Aug 2007
    Posts
    10
    Thot I would show a small example of the table, obviously its a short version and some fields are missing.

    REF Location Priority
    ==== ======= =======
    G007 Spain 6
    G015 France 3
    G026 USA 1
    G027 Spain 4
    G031 USA 3
    G032 UK 2

    so when the user selects the drop down it only displays each location once

    Hope this makes it clearer?

    thanks

    jmcall10

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so create a form so that a user can select what parameters they want
    on that form put list or combo box(es) populated by the relevant table(s) so that the user can select what parameter(s) they require
    put a button on the form, set that to open the required report

    select the button, look at the properties and change the code/build event to read something like....

    Code:
        'build our where clause
        Dim strWhere As String 'defien a variable to hold our where clause
        If <nameofparameter1control> > 0 Then 'has the user selected a parameter 1?
            strWhere = "Manu_ID=" & <nameofparameter1control>
        End If
        If <nameofparameter2control> > 0 Then 'has the user selected a parameter 2?
            If Len(strWhere) > 0 Then 'have we already got a manufacturer where clause?
                strWhere = strWhere & " and " 'if so add AND to make the where clasue make sense
            End If
            strWhere = strWhere & "Prod_Type=" & <nameofparameter2control>
        End If
        Dim stDocName As String
    place this before the
    Code:
    Dim stDocName As String
    line

    note rather than use a different query for the report (which we could by supplying the third parameter in docmd.openreport, we instead supply the fourth parameter, a where clause without the "where"
    ie in SQL we would use
    Code:
    select blah from mytable where Manu_ID = x
    , so the where clause would
    Code:
    "be where Manu_ID = x"
    here we supply
    Code:
    Manu_ID = x
    Modify the openreport line to read..
    Code:
        DoCmd.OpenReport stDocName, acPreview, , strWhere
    save the form
    open the form select the parameters as required


    jobsagoodun

  7. #7
    Join Date
    Aug 2007
    Posts
    10
    Thank you, i will try this May take me a while

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •