Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Passing Parameter to Report

    How do I pass a string parameter from a Form (frmStartup)to a Report. In frmStartup I define the string as
    strDateRange = "For the Period " & StartDate & " To " & StopDate
    A command button then initiates the report, i.e.
    DoCmd.OpenReport stDocName, acViewNormal
    I want to put strDateRange in the report header; however, the DoCmd.OpenReport doesn't have any place to put an argument to pass to the Report module.

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Parameter to Report

    Chuck:
    Although DoCmd.OpenReport doesn't have a means of passing the value, the Report can Get the value from the form as long as the form is still open. In the Report Header you can reference the form: i.e. =Forms![frmStartup]![strDateRange]

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Parameter to Report

    Hi Brian,

    That's exactly what I tried, and my report displays #Error in the text box with =Forms![frmStartup]![strDateRange] in it. I'm really puzzled with this one <img src=/S/question.gif border=0 alt=question width=15 height=15>

    In the Sub that calls the report I have Dim strDateRange As String. I then have the expression strDateRange = "For The Period " & Starttxt & " To " & Stoptxt. Starttxt and Stoptxt are the names of text boxes that have the dates.

    What am I missing? BTW I'm running Access97.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Parameter to Report

    I don't understand why you are using a sub with the expression =Forms![frmStartup]![strDateRange], when it seems that the textboxes on the form are named Starttxt and Stoptxt. Have you tried putting a textbox on the report with the source as ="For The Period " & Forms![frmStartup]![Starttxt] & " To " & Forms![frmStartup]![Stoptxt]?

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Parameter to Report

    OK,
    Confessions of a newbie to the world of VBA. Now you're forcing me to get down to the real crux of the problem. The string ="For the Period " & [Forms]![frmStartup]![Starttxt] & " To " & [Forms]![frmStartup]![Stoptxt] works just fine EXCEPT, the reports come out with an extra blank page. Of course, this is usually an indication that the report width exceeds the distance between the left and right margins. Soooo, I checked and sure enough, it was too wide. I tried to drag the right edge of the report back to the left, but it won't budge. Guess what? In the design view the above string runs right off the right edge of the report even though when printed it fits on the page. I then tried to manually set the report width property to the max available space. However, looking back after supposedly saving that entry I find that Access took upon itself to revise it back where it was!!!

    Of course, I could print in a smaller font -- small enough so that the string in design view would fit on the page, but having to accommodate an obvious flaw in Access really bugs me.

    So, my thought was to set up the string as a variable in the form with the calling procedure and use that variable in the report text box because it would be shorter in the report design review. It seems like such a simple concept. Can't a called report acquire variables defined in the calling form? There MUST be a way. <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing Parameter to Report

    Maybe I am misunderstanding you, but the width of the textbox in the design view does not need to accomodate the entire length of the source statement, only the width required for the data to print on the report. So, try decreasing the width of the textbox in the design view, so that it prints on the report properly, but doesn't exceed the page width.
    Then, of course, there is the obvious solution of moving it somewhere else on the page. If the page is 8.5"x 11" wide - can't it fit somewhere else?

  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Passing Parameter to Report

    Thanks Thomas,

    That did it, and the reports all come out looking great. <img src=/S/joy.gif border=0 alt=joy width=23 height=23> I don't know why I didn't think of it. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    However, I remain curious as to how one passes a variable from one object to another. I'm just a beginner regarding Visual Basic and right now I'm slugging my way through VB 6.0 for Dummies which has been helpful but doesn't cover everything. I'll figure it out someday.

    Thanks again for your patience with a newbie.

Posting Permissions

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