Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Join Date
    Jun 2003
    Posts
    76

    Unanswered: Multiple instances of the same report?

    Hello all!

    I have some code that cycles through and opens a report for each employee selected in a list box.

    Behold the code:
    For IntLoop = lstLA_PR.ListCount - 1 To 0 Step -1
    If lstLA_PR.Selected(IntLoop) = True Then
    theListItem = lstLA_PR.ItemData(IntLoop)
    DoCmd.OpenReport "Date and Processor - Lease Assignment" & " " & IntLoop, acViewPreview, , "[Employee] = """ & theListItem & """"
    End If
    Next IntLoop

    From what I understand, it is impossible to open a new instance of the same report, so what I did is made multiple reports (i.e: Date and Processor - Lease Assignment 1, Date and Processor - Lease Assignment 2... and so on). This would open the corresponding report to the loop.

    It there any way to open a new instance of the same report? Or Add a new page of the current report instead of opening a new different report as I am having to do?

  2. #2
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51

    Re: Multiple instances of the same report?

    Can you put all of the data on the same report with a page break between each person? if so create a Group for that catagory and in the group header properties set the [Force new Page] to "Before Section"
    Bob

  3. #3
    Join Date
    Jun 2003
    Posts
    76
    Thank you for the reply! But.. i'm a bit confused as to how I would go about doing that.

    Here is a screenshot of the grouping and the form itself. Please let me know what I would have to alter...
    Attached Thumbnails Attached Thumbnails theform.jpg  

  4. #4
    Join Date
    Jun 2003
    Posts
    76
    The grouping...
    Attached Thumbnails Attached Thumbnails sortingandgrouping.jpg  

  5. #5
    Join Date
    Jun 2003
    Posts
    76
    Nevermind. I got the grouping portion...

    I'm sure I would have to alter my code to accomadate for the change.

    Any ideas on that?

  6. #6
    Join Date
    Jun 2003
    Posts
    76
    I would somehow have to send all of the items that are selected from the listbox to the query...

    How would I go about doing that?

  7. #7
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    Try using code that looks somthing like this in your button_Cilck event

    ---------------------------------------------------------------------
    Private Sub ViewReport_Click()
    Dim strDocName As String
    Dim strOut As String
    Dim strWhere As String
    Dim varItem As Variant
    Dim First As ListBox

    On Error GoTo ErrTrap

    strDocName = "Name of the report to Open"
    Set First = Me!ListBoxName

    If First.MultiSelect > 0 Then
    If First.ItemsSelected.Count > 0 Then
    For Each varItem In First.ItemsSelected
    '-- If you are using a string value
    '-- you need to wrap your values in Chr(34) -- this is a Quote
    strOut = strOut & "," & First.ItemData(varItem)
    Next varItem
    strOut = Mid(strOut, 2)
    strOut = "In(" & strOut & ")"
    Else
    MsgBox "Make a selection"
    Exit Sub
    End If
    End If

    strWhere = "SQLWHERE statement without the WHERE keyword" & strOut
    '-- Somthing like
    '-- "(" & "([Employee])" & strOut & ")"
    DoCmd.OpenReport strDocName, acViewPreview, , strWhere

    Exit Sub
    ErrTrap:
    Select Case VBA.Err.Number
    Case 2501
    Resume Next
    Case Else
    MsgBox "Error : " & VBA.Err.Number & VBA.vbNewLine & _
    VBA.Err.DESCRIPTION, , "Error in AuditReport Form Module"
    End Select
    End Sub
    -------------------------------------------------------------------------

    I hope this helps
    By using the IN clause you can refere to multipule items at once.
    Bob

  8. #8
    Join Date
    Jun 2003
    Posts
    76
    irwinb - I LOVE YOU!!!!!
    Ugh.. you have no idea how much your help means to me. Thank you! Now on to the last portion.
    If you can find the solution to this problem.. i'll paypal ya a couple bucks. :-)

    As you can see, I have 2 list boxes. 1 is being populated by a table of employees and 2 is being populated with all of the reports in the reports section that begin with "LA".

    This code:
    For IntLoop = lstLA_EMP.ListCount - 1 To 0 Step -1
    If lstLA_EMP.Selected(IntLoop) = True Then
    theListItem = lstLA_EMP.ItemData(IntLoop)
    For Each X In lstLA.ItemsSelected
    DoCmd.OpenReport lstLA.ItemData(X), acViewPreview, , "[Employee] = """ & theListItem & """"
    Next
    End If
    Next IntLoop

    The code will open all of the selected reports in listbox 2 only for the LAST item selected in listbox 1.
    What would I have to do to make all of the reports open in listbox 2 for each name selected in listbox 1?
    Attached Thumbnails Attached Thumbnails reports.gif  
    Last edited by Sucoyant; 12-08-03 at 21:18.

  9. #9
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    Sucoyant -- Ok, If I understand correctly you want each of the reports you select to open for all of the employees that are selected? Is that correct?

    If that is true then you simply need to take the code(Above) that I sent you yesterday and nest two groupings of that code so that the first code loops through the reports listbox and sets the "strDocName" to the listbox data. Then as it gets each report name it loops through the Employee listbox and opens that report for all employees.

    You will also need to create additional variables for the listbox and variant such as
    Dim varItem2 As Variant
    Dim Second As ListBox

    so that you don't use duplicate nested variables. Also on the first nesting of code the strDocName will replace the strOut variable.

    I hope this makes sence. if not let me know and I can help you write the procedure.
    Bob

  10. #10
    Join Date
    Jun 2003
    Posts
    76
    Crummy!

    The code works at home, but not here at work!
    I don't have the ".MultiSelect" option.

    What can I do?

  11. #11
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    What versions are you running Home vs Work?
    Bob

  12. #12
    Join Date
    Jun 2003
    Posts
    76
    Both VB 6.3 with the same references enabled.

    At work: Windows 2000 with Office XP
    At home: Windows XP with Office XP

  13. #13
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    do you have the Mulitselect Property set to "Extended"
    Bob

  14. #14
    Join Date
    Jun 2003
    Posts
    76
    How do I do that?

    I'm sorry! I'm really not that good with VB!

    If you mean the listbox... yes, its multiselect extended.

  15. #15
    Join Date
    Dec 2003
    Location
    WA, USA
    Posts
    51
    in design view you should have a property for the listbox called Multiselect.
    Bob

Posting Permissions

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