Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Angry Unanswered: Select Reports via a Combo box and Listbox - Help!!!!!!!!

    Hi

    On my form I want to be able to select either the headings 'reports' or 'charts' in my database via a combobox (which I can do), so if I select 'reports' from the combobox, the list box displays the names of all the reports in the database. If I select 'charts' from the combobox, the list box displays the names of all the charts in the database.

    I want the names of the reports or charts displayed in the list box to have more user friendly names than those in my database eg 'rprt _by_date' should be displayed as "By Date Report" to make easier for the user.

    How can I do this as i'm right at the end of designing my database and im stuck!!

    Cheers

    Tom

  2. #2
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Tom,

    I have an idea... I also have another but that involves more code; so lets try this one first.

    There's a system table name 'MSysObjects'; everything you create in your db is stored in some manner there (sorry for explaining if you already know what it is).

    I'm assuming that all of the 'type' attributes are give a value of '-32764' for reports, however there doesn't seem to be a value distinguishing between the types of reports.

    So, for your reports that are charts include the string 'chart' in the name, i.e. r_chart_mr_moss_chart.

    Now create two seperate queries:
    "SELECT name FROM MSysObjects WHERE type = -32764 AND name LIKE '%chart%' "
    "SELECT name FROM MSysObjects WHERE type = -32764 AND name NOT LIKE '%chart%'"

    So once you select chart or report use the appropriate query to pull the names... once you have the name you can open the one you want.

    If this isn't feasible let me know, I have another idea...

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    I think i understand what your saying, but I cant go changing all the names of the reports or charts because there is so many and they are linked to queries etc..

    Do you know of any examples I can download? I really need the combo box option aswell to select chart or report headings..then have either the charts or reports shown in the list box ready to be selected.

    Cheers

    Tom

  4. #4
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Create a separte table which maintains the report name and type...

    t_chart_objects
    id, name, type
    1, "r_moss_report", "Chart"
    2, "r_moss_report2", "Report"
    etc...

    It'll suck depending upon how many charts you have... and then you'll also have to update it on every new chart.

    I gotta run, if you still haven't figured anything out by the time I'm back I'll take a little deeper look.
    -Warren
    Hack the Planet.

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Thanks Warren, the table thing is exactly what ive been thinking of myself, gonna try it now, but im not too good at entering code in the events and queries, so if you could maybe have a think about it and get back to i'd really appreciate it!

    Cheers

    Tom

  6. #6
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Tom,

    Layout some detail and I'll layout some code...
    -Warren
    Hack the Planet.

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    Hi Warren

    To make it easy I will say Ive got 2 reports and 2 charts -

    The actual database names of these are -

    REPORTS -
    Count_report
    reportbycounty

    CHARTS -
    piechart1
    chart_area

    I need a combobox (called "comboreportchart") to select whether the listbox displays the names of either reports or charts (the listbox is called called "lstreportchart").

    I cant have the actual names of the reports and charts displayed in the listbox as Ive not named them in a user friendly way. So a table(s) could be linked to the listbox holding more user friendly titles -

    Tblchart -
    Pie Chart
    Chart Of Area

    TblReport -
    Report Of Count
    Report By County

    So If you select "charts" from the listbox, either "Report Of Count" or "Report By County" is displayed and you can click on either heading to open the actual chart or report.

    Hope this is helpful enough for you?

    Regards

    Tom

  8. #8
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Tom,

    First off lets get this table that lists your reports squared away:

    t_report_list
    report_name (text) (pk) - Access named report; since you can't have two reports named the same think, this makes it a good candidate for a primary key. If you feel unconfortable with this just create an autonumber as pk.
    report_display_name (text) - This is the user friendly display name you want.
    report_description (text) - This is optional, I don't know if you also want to have a description displayed for the user.
    report_type (depends) - this can either be a foreign key to a table containing the chart types, such as tabular, chart, bar, pie, or whatever. If you just want to differentiate between either report/chart then you can set this as text and use values such as 'chart'/'report'

    Here's the event code which populates the combo box which displays the list of charts/reports after selecting which you'd like to see:

    Code:
    Dim report_type As String
        
    report_type = Me.Combo0.Value
        
    Me.Combo2.RowSourceType = "Table/Query"
        
    Me.Combo2.RowSource = "SELECT report_display_name FROM t_report_list WHERE report_type = '" & report_type & "';"
    
    Me.Combo2.Requery
    That's just the start, let me know if you need more from there...

    Sorry it took so long, was watching football all day.
    -Warren
    Hack the Planet.

Posting Permissions

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