Results 1 to 10 of 10
  1. #1
    Join Date
    May 2004
    Posts
    73

    Arrow Unanswered: How do you pass an InputParameter to a report from a form???

    I have successfully learned how to pass a RecordSource to a report from a form using VB. But now I would like to know how you do the same thing except with an InputParameter?? And further combine the two. My current code is:

    THE FORM:
    Private Sub Button_Click()
    DoCmd.OpenReport "RPT_RMA", acViewPreview,
    OpenArgs:="SP_ADVANCES_NOT_RECEIVED"
    End Sub

    THE REPORT:
    Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "exec [" & Me.OpenArgs & "]"
    End Sub

    Any help would be appreciated.

    Thanks.
    Last edited by X-Centric; 03-01-05 at 10:32.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    There are a number of ways you can use to accomplish this. Here is one that is rather flexible:

    In your Form
    Code:
    'skip any errors (you should put error trapping here).
    On Error Resume Next
    'just to make sure the current record is saved.
    DoCmd.RunCommand acCmdSaveRecord 
    'Turn OFF system warnings (ie: save dialog)
    DoCmd.SetWarnings False 
    'Open the report in design view but hide it.
    DoCmd.OpenReport "myReportName", acViewDesign, , , acHidden 
    'Install whatever RecordSource you want.
    Reports![myReportName].RecordSource = "Main" 
    'Close the report. Changes are automatically saved.
    DoCmd.Close acReport, "myReportName" 
    'Open the report for preview and pass your parameter in the OpenArgs property.
    DoCmd.OpenReport "myReportName", acViewPreview, , , , "myInputParameter" 
    'Turn ON system warnings 
    DoCmd.SetWarnings True
    In your Report's OnOpen event (or whatever event you want), you can process your InputParameter:

    If Not IsNull(Me.OpenArgs) then MsgBox Me.OpenArgs

    Ahhhhh....food for thought I suppose.


  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Marked for deletion.
    Last edited by r123456; 02-28-05 at 20:33.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    May 2004
    Posts
    73

    Arrow

    Just curious but what are some other ways this can be accomplished?

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Depends on whether you are using an MDB or an ADP.

    With an ADP, you can call the corresponding property that manages input parameters.

    However, this option is not available in an MDB, but you can work around this by creating a Pass-Through Query (PTQ), and setting it as the report's recordsource. Then, to configure input parameters to the function (I presume), you can alter the text of the PTQ through ADO.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    May 2004
    Posts
    73
    I am using an ADP. My goal is trying to keep the report unbound to anything and specifying the record source and input parameters from vb in the form. Maintaining more than one report is too much work.

    Please give me an example of this "call the corresponding property that manages input parameters"

    Thanks
    Last edited by X-Centric; 03-01-05 at 10:34.

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Apply your RowSource and Parameters within the OpenArgs property (when opening the report) utilizing a delimiter (such as a comma) to seperate the various components, then parse the components and apply them as required within the OnOpen event of your report.


    Utilize the use of creating a temporary table to accomplish your task then delete the table when the report is closed.


    Place code functions within the ControlSource property of all your field controls to perform the task at hand (pull the required data).


    Just the rattle off a few...I'm sure others here can add to this.


  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you open a report from a form, and keep that form open whilst the report is loading then you can refer to any variable (that is public) in the calling form from the called object (be that a form or report [or query for that matter]). works for VBA

    the syntax is forms!<formname>!<variable> (or functionname). If you need to call the report from more than one form then you can set these variables up as part of a public function in a common module, that way round the report usage becomes more consistent - you are not required to go through the same entry point.

    I have used something similar to:-
    public function MyParameter(ParameterID as paramID,optional GetParam as boolean = TRUE) as variant
    select case ParameterID
    if GetParam=true then
    'return the parameter
    else
    'store the parameter in a static variable
    endif
    where paramID was an enumerated list of the parameters that module supported

  9. #9
    Join Date
    May 2004
    Posts
    73
    What about something like this? I got this from MS's website.

    Private Sub Report_Open(Cancel As Integer)
    Dim strRecordSource As String
    strRecordSource = "Exec [Sales By Year] '1/1/97','12/31/98'"
    Me.RecordSource = strRecordSource
    End Sub

    Now this is calling a static stored procedure and sending a static inputparameter to the stored procedure with it. cant we change them to variables instead? Maybe something like:

    THE FORM:
    Private Sub Button_Click()
    DoCmd.OpenReport "RPT_RMA", acViewPreview,
    OpenArgs:="SP_ADVANCES_NOT_RECEIVED"
    End Sub

    THE REPORT:
    Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = "exec [" & Me.OpenArgs & "] THE INPUTPARAMETER VARIABLE"
    End Sub

    Please tell me if i am getting warm or cold. Thanks

  10. #10
    Join Date
    May 2004
    Posts
    73
    Will something like the above work or am I trying to invent my own language?

Posting Permissions

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