Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting InputParameters for a report (Access 2002)

    Ok, I'm not sure if this should be in the VB or Access section but here goes. Someone was nice enough to give me one answer, now I'm going for 2. I want to call a report from within VBA that has an input parameter. I've found the object syntax: objRpt.InputParameters but I can't find an example of what goes after that.

    Thanks in advance,

    Mary

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Setting InputParameters for a report (Access 2002)

    Are you working with an Access project (ADP)? The InputParameters property only applies in ADPs because it is used to pass parameters into the SQL statement or stored procedure that populates the form or report. If you're using an MDB, you do it differently.
    Charlotte

  3. #3
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting InputParameters for a report (Access 2002)

    I'm within an MDB using VBA. I need to print (actually preview) a report but this report must be provided a parameter. I'm trying to call the report from code behind a form which asks for the parameter.

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

    Re: Setting InputParameters for a report (Access 2002)

    Access 2002 has a new feature for Reports - OpenArgs argument for the DoCmd.OpenReport method. (Previous versions only had OpenArgs for Forms.)

    You could pass a value to filter using the OpenArgs argument when opening the report. You'll have to remove the filter from the Report's current record source, then you can set the Filter property to the value you've passed. (Don't forget to also set the FilterOn property.)

    Post back if you need help with any of this...

  5. #5
    Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting InputParameters for a report (Access 2002)

    I'm close, but 'no cigar'. Here is my code:

    DoCmd.OpenReport "Retail Cr Invoice (on pre-printed stock)", acViewPreview, _
    "Queries![Ret Ver Billing]", , acWindowNormal, CDate(Text1.value)


    I'm getting the report but no data. I know that the the Text1.value is correct because it's being used in some previous calls and I've checked it with debug. My query name is 'Ret Ver Billing'. I do have the 'Filter On' set to 'yes' in the report properties and I took the query out of the RecordSource field in Properties.

    Thanks!

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

    Re: Setting InputParameters for a report (Access 2002)

    Hi Mary,

    While you can use the Filter argument of the DoCmd.OpenReport object to filter a report or form's recordset, that wasn't what I had in mind for your current situation.

    Remove that part of the OpenReport method. In the Report's OnOpen event, set the Filter property of the Report to the OpenArgs value (your date). Also, in your Query, remove the criteria that's filtering for the date.

    Your OpenReport command would look like this:
    DoCmd.OpenReport "Retail Cr Invoice (on pre-printed stock)", acViewPreview,,,acWindowNormal,Text1.Value

    In the OnOpen sub of the report, you'll want this:

    'Dates need to be surrounded by "#" in Where clauses
    Me.Filter = "mydatefield=#" & CDate(OpenArgs) & "#"
    Me.FilterOn = True

    Hopefully that should do it. You may have to adjust a few things depending on your situation.

    Hope this helps!

Posting Permissions

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