Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Combo Boxes to generate Reports

    This is one of the more difficult programming things I have attempted, and since I am asking for help it means I haven't figured the stupid thing out.

    Here are the Details:

    1. I have 3 Reports
    2. I have a work facility of 27 different sections for which the database is tracking information

    Without having to create 81 distinct reports, or create parameter queries where the user must type in their section name "perfectly" to get it to work, I would like to try and do the following:

    Create a pop-up form. Place 2 combo boxes on it. One with the 27 section names in it. The other with the 3 Report names (which may go to 6). The user would then select their section and what type of report they want, press an OK button and "poof", a great report.

    I would even like to to add a fancy feature of putting (2) date range boxes on the pop-up form so they can search by date range.

    Putting fantasy into reality would be great, but as I stated earlier I am struggling with how to get the section combo box to work like a "filter" while designating which report to send the filtered query information to.

    As always, I do appreciate the help I receive. Thanks.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Boxes to generate Reports

    This could be done a couple of ways. I'd first try a parameter query which would reference the control values on your popup form in the criteria row or where clause of the queries on which your reports are based. Once the form is created use the expression builder to get the correct syntax for referring to the form controls in your queries. As for the date range you can use the Between...And expression in the criteria row or where clause as well.


    Select * From tblWorkFlow
    Where [FacilityID] = [Forms]![FromName]![FormControlName] AND [ProductionDate] Between [Forms]![FormName]![Date1ControlName] And [Forms]![FormName]![Date2ControlName]

    As for opening the selected report in the click event of your "OK" button use the openform method and substitute the control name for the report name argument:

    DoCmd.OpenReport Me.ControlName

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Boxes to generate Reports

    I just got through downloading a database from <A target="_blank" HREF=http://www.HelenFeddema.com/CodeSamples.htm>Helen Feddema's site</A> that does just that. It was code26.zip under the Code Samples section for Access.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Combo Boxes to generate Reports

    I set up a system that, although it doesn't use any fancy VBA, is quite efficient. It uses a table with a field for each potential criteria for generating a report. A form links to this table, allowing the one and only record in the table to be edited, but no record can be added or deleted. The values for the fields are restricted to values in a combo box; the values for the combo boxes come from data tables containing the possible choices. The user opens the form, selects the criteria, and clicks a button on the form to continue processing. The button closes the form and opens the report. The query that generates the reports pulls the values from this table as criteria. Close the report, and the user returns to the input decision form.

    To adapt this to your needs, I'd set up a form that includes a field for Section, with three buttons below. The user chooses a section, and clicks one of the three "Report Type" buttons, which would then fire off the desired report. Use two input forms, and you could designate one as a "Global" report, and the other would allow for date selection.

    Think of it like a custom "switchboard", moving between levels. First form: "Generate report for all dates, or a range of dates?" Second form: [2a] "Choose Section & Report Type" or [2b] "Choose Section, Date Range, & Report Type" depending on the user's first selection.

    We only work with two reports, so it was easier to set up different forms/buttons for each report. The potential is also there to include a report number/description field, and have a report-generating macro read this field in as a "Condition" to the macro. Based on the value of this field, different reports would or would not be generated.

    Your "fantasy" is currently being installed as our reality at a number of our locations -- designed for non-Access users to use with ease. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I think your reality can't be too far off either.

    (One additional benefit of this setup is that criteria do not need to re-entered if different reports are to be run for the same Section, since the criteria values are actually stored in a table and can be referenced repeatedly without re-input.)

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Boxes to generate Reports

    Hi David,

    This may be a little off the subject, but I really like your idea of storing the chosen criteria in a table for later use! I use forms for user input to filter reports all of the time, but it never occurred to me to store it for later use...WOW! Ya' learn something new every day! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    Many thanks <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  6. #6
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using Combo Boxes to generate Reports

    For multiple reports generated on the same basic set of variable criteria, it works like a charm. Also, if you have a date-selection function, you can keep one set of criteria while shifting dates... Conversely, you can keep the dates static and shift other variables to run a series of "Division" reports for the same time period.

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Indiana
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Boxes to generate Reports

    Judy,

    Thanks. The sample database provided just want I needed.

Posting Permissions

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