Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    How can I perform the following command and have the report display to the last page instead of the first page?

    DoCmd.OpenReport stDocName, acPreview

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    I don't think that is possible. I experimented with SendKeys and got inconsistent results. Also see this newsgroup post.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    You can try this:

    DoCmd.OpenReport stDocName, acPreview
    SendKeys "%vzf" ' to zoom the report to fit to window
    SendKeys "^+{right}" ' to go to the last page

    I have tested a little and it seems to work.
    HTH
    Francois

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Your SendKeys didn't do it for me

    When I'm at the the Print Preview screen on page 1

    If I type the Z Key the screen it fits to window
    If I press the End Key it goes to end of report

    Is that what the following translates to?

    SendKeys "%vzf" ' to zoom the report to fit to window
    SendKeys "^+{right}" ' to go to the last page

    John

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Maybe you have to insert a DoEvents before the Sendkeys.

    DoEvents
    SendKeys "%vzf" ' to zoom the report to fit to window
    SendKeys "^+{right}" ' to go to the last page


    %v = Alt+V = open View Menu
    z = Zoom
    f = Fit to window

    ^+{right} = Control + Shift + Right Arrow.

    Sendkeys behave sometimes very strange. I only test it on one report with only 12 pages.
    I was hoping that it would work for you. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Francois

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    This worked, tested with Northwind.mdb Catalog report:

    Public Sub OpenReportLastPagePreview(strRpt As String)

    Dim strPages As String
    Dim intLen As Integer
    Dim n As Integer

    ' rptCatalog (Northwind)
    DoCmd.OpenReport strRpt, acViewPreview
    ' Determine number of pages (note from Help):
    ' To refer to the Pages property in a macro or Visual Basic, _
    the form or report must include a text box whose ControlSource _
    property is set to an expression that uses Pages. Ex: _
    ="Page " & <!t>[Page]<!/t> & " of " & <!t>[Pages]<!/t>

    strPages = CStr(Reports(strRpt).Pages)

    ' Specify Zoom percentage:
    DoCmd.RunCommand acCmdFitToWindow
    DoCmd.SelectObject acReport, strRpt

    ' Use SendKeys to select Navigation box Page number:
    SendKeys "{F5}", True

    For n = 1 To Len(strPages)
    SendKeys Mid$(strPages, n, 1), True
    Next n

    SendKeys "{ENTER}", True

    End Sub

    As noted, to be able to refer to a report's Pages property in VBA, you must have a control on the report that explicitly references the property, or it returns zero (0). I modified the Catalog report's Page Footer to include the "of [Pages]." Also, as noted, SendKeys can be flakey, but this worked OK - the Catalog report opened on last page (Page 9) (the Order Form). Also tested with report with more than 9 pages (ie, 2 digits) & still worked OK. The main trick is remember that F5 selects the little Page No navigation box.

    HTH

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Hi Francois

    Your original solution worked, I had it in the wrong area when I tested it, my fault

    Mark D's solution also worked.

    I will let you guys duke it out for what which one is politically correct.

    Thanks for your help

    John

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Hi Mark

    Your solution worked.

    Francois solution also worked.

    I will let you guys duke it out for what which one is politically correct.

    Thanks for your help

    John

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Hi Francois

    Once I get to the last page what is the SendKeys command syntax to get back to the first page?

    The reason I ask this is my standard/user interface as reports go is always to Report Preview.

    This is a check writing application, one record per 8 1/2 x 11 form and I have the following SQL in the report Detail_Format:

    strSQL = "UPDATE " & strTableName & _
    " SET strCHECKNO = " & Me.strCHECKNO & ", " & _
    " strOK = " & Chr(34) & "Y" & Chr(34) & ", " & _
    " dteSUMDAY = #" & Me.dteSUMDAY & "#" & _
    " WHERE " & lngID & " = " & Me.idsKeyOfSumServe

    I want to make sure all pages get printed, IE go to last page.

    What are the SendKeys syntax to get back to the first page and open to 100%?

    Thanks, John

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Hi John,

    %v = Alt+V = open View Menu
    z = Zoom
    z = Zoom to 100%

    ^+{Left} = Control + Shift + Left Arrow to go to the first page

    Glad you have it working.

    As for what to use, mine or Mark's, I think it's the same. Up to you.
    Francois

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Here is another option if you want to go back to Page 1 of report. Modified example:

    Public Function OpenReportLastPagePreview(strRpt As String)

    Dim strPages As String
    Dim intLen As Integer
    Dim n As Integer

    DoCmd.OpenReport strRpt, acViewPreview
    ' Determine number of pages:
    strPages = CStr(Reports(strRpt).Pages)
    ' Specify Zoom percentage:
    DoCmd.RunCommand acCmdFitToWindow
    DoCmd.SelectObject acReport, strRpt
    ' Use SendKeys to select Navigation box Page number:
    SendKeys "{F5}", True
    SendKeys strPages, True
    SendKeys "{ENTER}", True

    'Pause 1 second, then go back to Page 1:
    Sleep 1000
    DoCmd.SelectObject acReport, strRpt
    SendKeys "{F5}", True
    SendKeys "1", True
    SendKeys "{ENTER}", True

    End Function

    Another option, scroll thru report one page at time, either forward or backward, pausing for specified interval (similar to timed PowerPoint presentation):

    Option Explicit

    Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Public Function PreviewReportOnePageAtATime(strRpt As String, _
    Direction As Integer, _
    Interval As Long)
    Dim intPages As Integer
    Dim n As Integer

    DoCmd.OpenReport strRpt, acViewPreview
    ' Determine number of pages:
    intPages = Reports(strRpt).Pages

    ' Specify Zoom percentage:
    DoCmd.RunCommand acCmdFitToWindow
    DoCmd.SelectObject acReport, strRpt

    If Direction = 0 Then ' Go forward, pause for specified interval:
    For n = 1 To intPages
    SendKeys "{F5}", True
    SendKeys CStr(n), True
    SendKeys "{ENTER}", True
    SendKeys "{F2}", True
    Sleep Interval ' milliseconds
    Next n
    ElseIf Direction = 1 Then ' Go backward
    For n = intPages To 1 Step -1
    SendKeys "{F5}", True
    SendKeys CStr(n), True
    SendKeys "{ENTER}", True
    SendKeys "{F2}", True
    Sleep Interval ' milliseconds
    Next n
    End If

    End Function

    Example of calling function:
    PreviewReportOnePageAtATime "rptCatalog", 0, 1000 ' forward, pause 1 second
    PreviewReportOnePageAtATime "rptCatalog", 1, 1000 ' backward, pause 1 second

    The second function works OK but will act flaky if user starts clicking stuff while it is running....

    HTH

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenReport stDocName, acPreview Question (a2k (9.0.6926) SP-3 J

    Hi Mark & Francois:

    Both of your approaches and solutions worked perfectly.

    Thanks for the clinic on controlling print preview, I now have a better underdsanding of SendKeys.

    John

Posting Permissions

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