Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Preview report - keeping focus (2000)

    I have a form containing a combobox of supplier names and a combobox of reports.

    I have used the following code:

    For intCurrentRowLines = 0 To ctlSourceSuppliers.ListCount - 1
    If ctlSourceSuppliers.Selected(intCurrentRowLines) Then
    Forms!ChooseSuppliers!Suppliers = ctlSourceSuppliers.Column(0, intCurrentRowLines)
    DoCmd.OpenReport "OrdersBySupplier", acViewPreview
    End If
    Next intCurrentRowLines

    When I select multiple suppliers, the report displays the results for only one of the selected suppliers. If I change fom acViewPreview to ACViewNormal the reports are printed correctly as a number of separate reports (one for each supplier). I presume that I need to prevent the focus moving from the report window until I close it manually.
    How do I make the report keep the focus in this way?

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

    Re: Preview report - keeping focus (2000)

    Is it really a good idea to open a series of reports in preview mode? Wouldn't it be better to open one report containing the data for all selected suppliers?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    How would I do that using this type of multiple selection routine?
    Generally only 2 or 3 suppliers are selected.

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

    Re: Preview report - keeping focus (2000)

    How does the report get filtered now? Is its record source a query with a parameter <code>[Forms]![ChooseSuppliers]![Suppliers]</code>?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    Yes, that's exactly what happens.

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

    Re: Preview report - keeping focus (2000)

    One way to do this would be to remove the parameter from the query, and to change the code like this:

    Dim strWhere as String

    If ctlSourceSuppliers.ItemsSelected.Count = 0 then
    MsgBox "No suppliers selected!", vbExclamation
    Exit Sub
    End If

    For intCurrentRowLines = 0 To ctlSourceSuppliers.ListCount - 1
    If ctlSourceSuppliers.Selected(intCurrentRowLines) Then
    strWhere = strWhere & ", " & ctlSourceSuppliers.ItemData(intCurrentRowLines)
    End If
    Next intCurrentRowLines

    ' Create string of the form IN (12, 23, 35)
    strWhere = "IN (" & Mid(strWhere, 3) & ")"
    ' Open report with where-condition
    DoCmd.OpenReport "OrdersBySupplier", acViewPreview, , strWhere

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    Producing the reports in this way, is there a limit to the number of suppliers who could be selected? What would happen if the where string becomes longer that 256 characters?

    I know you suggested against it, but is there a method of keeping the focus on the report until it is closed? It is bound to come in handy at some time in the future

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

    Re: Preview report - keeping focus (2000)

    1. There is no problem with strings becoming longer than 256 characters (this is not a text field in a table). You can easily select hundreds of suppliers this way.

    2. Upgrade to Access 2002 or 2003. Just like forms, reports have a Modal property that can be set to Yes to prevent the user from clicking outside it.

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    Thanks Hans
    No plans to move to later versions of Access for quite a while.
    I'll use the suggested method of creating a WHERE string for the query.

  10. #10
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    Hans
    I tried the code you suggested, but no joy.
    If I just selected one supplier "AVERY" the following error is returned:

    Run-time error '3175'
    Syntax error (missing operator) in query expression '(IN (AVERY))'

    if I choose AUSTI and AVERY I get the error:

    Run-time error '3175'
    Syntax error (missing operator) in query expression '(IN (AUSTI, AVERY))'

    I presume it is something to do with the supplierid field that needs to be specified

    Can you help

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

    Re: Preview report - keeping focus (2000)

    Yup, I had expected that the bound column of the list box was a number field. Since it is a text field, you must put quotes around the values. Change

    strWhere = strWhere & ", " & ctlSourceSuppliers.ItemData(intCurrentRowLines)

    to

    strWhere = strWhere & ", " & Chr(34) & ctlSourceSuppliers.ItemData(intCurrentRowLines) & Chr(34)

    The Chr(34) generates a double quote ". Hope this works.

  12. #12
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    Still the same error, but with the double quotes around the names to seach.
    Any other thoughts?

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

    Re: Preview report - keeping focus (2000)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> I'm sorry, that is my fault. I forgot an essential part. Change the line

    strWhere = "IN (" & Mid(strWhere, 3) & ")"

    to

    strWhere = "[SupplierID] IN (" & Mid(strWhere, 3) & ")"

    where SupplierID must be replaced by the name of the field that contains AUSTI, AVERY etc.

  14. #14
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    Thanks very much for all that Hans - works perfectly - another (of many I have picked up from you) little trick I can rely on.

  15. #15
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Preview report - keeping focus (2000)

    If you are doing a single report, and not a series like you are doing, this is what I do to set focus on the report:

    DoCmd.OpenReport "MyReport", acViewPreview, , , , "SendingFormName"
    DoCmd.SelectObject acReport, "MyReport"

    Notice I sent the sending form name as an open arg to the report, which is nice if you want to return focus to the calling form:

    Private Sub Report_Close()
    If IsNull(Me.OpenArgs) Then
    Else
    DoCmd.SelectObject acForm, Me.OpenArgs
    End If
    End Sub

    thx
    Pat

Page 1 of 2 12 LastLast

Posting Permissions

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