General 'on Open' event of a report 1

[Cosette]

Hi all,

I have a few reports based on the same query. So far, I have copied the query ver and over to change the parameters values (some reports are run by date, others by gender etc...)

It dawned on me only now that it would be much easier to select the parameters in the on Open event of the report, duhh!!! However, I don't know if it is possible since a query doesn't have field to refer to like [forms]![frmbla]![txtblah].

How does one go about setting query parameters when opening a report.

David

 

[GingerR]

How about this: You have a form, with perhaps an Option Box. In it are radio buttons, and choices like

By Date
By Gender
By Blah

and a button. When you click the button, you build the WHERE statement for the report.

dim strReportName, strWhere as String
strWhere = ""
Select Case Me.fraReportChoice
   Case 1 'By Date
       strReportName = "ReportByDate"
   Case 2 'By Gender
       strReportName = "ReportByGender"
   Case 3 'By whatever
       strReportName = "ReportByBlah"
End Select
docmd.openreport strReportName,acviewpreview

Say then, say that for ByDate report, the user needs to enter a date range. Next to the "By Date" option in the option box, put two text boxes, names say txtStartDate and txtEndDate. Then the code above for Case 1 would be something like this:

Case 1
    'First check to make sure the user filled in dates
    if isnull(me.txtStartDate) or me.txtStartDate = "" then
        msgbox "Please enter a Start Date!",,"Missing Info"
        me.txtStartDate.SetFocus
        exit sub
    end if
    if isnull(me.txtEndDate) or me.txtEndDate = "" then
        msgbox "Please enter a End Date!",,"Missing Info"
        me.txtEndDate.SetFocus
        exit sub
    end if
    'Dates are filled in; Proceed
    strReportName = "ReportByDate"
    strWhere = "[StartDate] between #" & me.txtStartDate & "# and #" & me.txtEndDate & "#"

and the OpenReport code changes to:

docmd.openreport strReportName,acviewpreview,,strWhere

This way you can pass values from the form. Also, it might be that you can have maybe just one single report (or at least FEWER reports) that can be used for every choice. What is different for each report? If the data source is the same and the data you get can be adjusted using the sample I show here, what else is different? The Report Heading? You can adjust that in code too. Put an invisible text box on your form. In the Select code above, in each Case statement, put something like

me.txtReportHeader = "Report for Dates " & me.txtStartDate & " - " & me.txtEndDate

or

me.txtReportHeader = "Report by Gender"

then on the report, instead of typing a heading into a label, just put a text box that has

 =Forms!FormName!txtReportHeader

just some ideas. Hope this helps--g

 

[Cosette]

Ginger,

This helps tremendously. Thank you very very much. You don't how clearly you have expressed the solutions to a confused thought in my head.

Thank you