Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    173

    Unanswered: Running Report with Parameters from code

    I've got a report that takes a couple of parameters. (dates for a range) Is there a way to open this report from code and provide the dates from code?

  2. #2
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    you could filter your report's result with [Where Condition] parameter of docmd.openreport action.
    example:
    Code:
    DoCmd.OpenReport "ReportName", , , "[My Date] BETWEEN #1/1/2004# AND #15/6/2004#"
    as you see Where condition used as it is in a SQL statement.
    ghozy.

  3. #3
    Join Date
    Apr 2004
    Posts
    173
    Unfortunately the report is a MPG list. It uses the date range to find results. So unfortunately I couldn't run it wide open with no date param's. The only way I can think to do this is to take the query give it the parameters, open a recordsource with the results, then open the report and give it the recordsource I created. Is that the easiest, or am I overthinking this one.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    another way is using another form as reference for your parameters in your report's recordsource (or your query).
    something like:
    select * from tablename where mydate= [Forms]![myform]![mytextbox]
    ghozy.

  5. #5
    Join Date
    Apr 2004
    Posts
    173
    I tried this code, but I still get prompted for parameters. The query MPG FINAL 2 is a conglomeration of 3 separate queries. Each of the queries has a Start Date and End Date parameter. They are all declared in the parameters for each query. recGlobal is global and in the report's open procedure I set the recordsource property to the name of the query. Any thoughts?


    Private Sub MpgRpts_forDivs()
    Dim divisions As Variant
    Dim x As Integer
    Dim qdef As QueryDef
    Dim dtStart As Date, dtEnd As Date
    dtStart = DateValue(InputBox("Enter a start date: "))

    dtEnd = DateValue(InputBox("Enter an end date: "))

    divisions = Array(10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 23, 24, 25, 26, 27, 28, 29)
    For x = 0 To UBound(divisions)
    Set qdef = CurrentDb().QueryDefs("MPG FINAL2")
    qdef("Start Date") = dtStart
    qdef("End Date") = dtEnd
    qdef("Division") = divisions(x)
    Set recGlobal = qdef.OpenRecordset
    DoCmd.OpenReport "MPG FINAL2", acViewPreview
    Next x
    End Sub

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    frankly, I don't know how to use QueryDefs. but you could refer to form controls instead using parameters in your queries, then could use last query in your report. when you open the report, it would retrieve references from the already open form and use them as parameters.
    ghozy.

  7. #7
    Join Date
    Apr 2004
    Posts
    173
    Does anyone know why I continue to get prompted for parameters despite my code. The recordset that is found is the results I'm after but I can't get it into the report. Any thoughts??

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It sounds to me like you are getting prompted for the dates because they are required in the three queries that MPG FINAL 2 is based on. What I have done in the past is have a Parameters table. In that table there is one record with two fields, BeginDate and EndDate. When you go to create the report, first update the table with the BeginDate and EndDate the user wants. Then modifiy each query to use those dates to filter on. The criteria for your date fields would be something like:

    Between [tblDateParameter].[BeginDate] And [tblDateParameter].[EndDate]

  9. #9
    Join Date
    Apr 2004
    Posts
    173
    That sounds like the best idea so far, I'll give it a shot. Thanks for the help.

Posting Permissions

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