Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: Input parameters for stored procedure

    I have a stored procedure that I would like to use as the data source for a report. This SP requires multiple parameters that I pass in with Open Args and then parse with a function. I would like to have this happen during the report_open event but I'm open to suggestions. So far I tried-

    Report_Open

    Me.InputParameters = "@Employee = '" & strEmployeeName & "'" & ", " & _
    "@Type = '" & strType & "'"

    Me.recordsource = "Stored_Procedure"

    End Sub

    That sure didn't work. Is there a way to do this?
    Thanks,
    Bill

  2. #2
    Join Date
    Apr 2004
    Posts
    18
    When I started to use ADP's and reports I had the same problem. You cannot seem to change the input parameters whilst opening a report itself and within report mod code.

    I have since moved to using MDB's and the Local tables within to store report data returned from the server using parameters in a similar way to you. This means you can set up a local table to take the returned data and the report looks directly at this table. Forms can then be used to retireve this data and then open the relevant report at the users request....

    But in your case I would recommend that you:

    1. create a form that users select the parameters on from and then within the form code

    2. open the desired report in design mode using docmd...

    3. set the input parameters...
    reports("reportx").inputparameters = "@Employee = '" & strEmployeeName & "'" & ", " & _"@Type = '" & strType & "'"

    4. save and close the report using docmd...

    5. Open the report back in preview and tada !

    You can use echo to hide this happening on the screen.

  3. #3
    Join Date
    Mar 2004
    Posts
    139
    Thanks for the tip...I think that will do the trick...

    I've managed to incorporate some code but I keep getting an "invalid use of Null" error and then the parameters prompt comes up looking for a the sp parameters. It looks to me like the parameters aren't getting passed. I've tried the stored procedures with the parameters and I know it works. Does anyone see a problem with the following:

    DoCmd.OpenReport "rptRealEstate", acViewDesign
    Reports("rptRealEstate").InputParameters = "@EmployeeName = 'John Smith'" & "," & "@Type = 'RA'"
    DoCmd.Close acReport, "rptRealEstate", acSaveNo
    DoCmd.OpenReport "rptRealEstate", acViewPreview
    Thanks,
    Bill

  4. #4
    Join Date
    Apr 2004
    Posts
    18
    DoCmd.OpenReport "rptRealEstate", acViewDesign
    Reports("rptRealEstate").InputParameters = "@EmployeeName = 'John Smith'" & "," & "@Type = 'RA'"
    DoCmd.Close acReport, "rptRealEstate", acSaveNo
    DoCmd.OpenReport "rptRealEstate", acViewPreview[/QUOTE]


    I think this should be acSaveYes to save the changes you made....
    DAO/ADO/VB.net/VBA/Access2K/Excel2K/SQL2000/Essbase/OFA/Oracle OPM, IM, EAM/Discoverer/ SQLPlus

Posting Permissions

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