Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open report with items selected (Access 2000)

    Could somebody help me with the following.I want to open a report based on selected items, i.e. the row source oft he report to be based on the list box LstSelected, according to the attachement i am sending. I could not find a place where this question is discussed.

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

    Re: Open report with items selected (Access 2000)

    Rename the command button to cmdReport (you should always give controls meaningful names) and use this code:

    Private Sub cmdReport_Click()
    Dim aSelected() As Variant
    Dim strWhereCondition As String
    Dim varItem As Variant

    ' Get an array filled with the selected items.
    aSelected = mmp.SelectedItems
    If UBound(aSelected) = 0 Then
    ' Nothing selecte - get out
    Exit Sub
    End If

    For Each varItem In aSelected
    strWhereCondition = strWhereCondition & ",'" & varItem & "'"
    Next varItem

    ' Remove leading comma
    strWhereCondition = Mid(strWhereCondition, 2)

    ' Make condition
    strWhereCondition = "CompanyName In (" & strWhereCondition & ")"

    ' Open the report
    DoCmd.OpenReport "rptCustomers", acViewPreview, , strWhereCondition
    End Sub

  3. #3
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open report with items selected (Access 2000)

    Dear Hans

    I want to inform you that your reply was sucessfulyy applied by me and i am very proud of what i have achieved. of course i will not forget who has helped me. I think i have finished with my problems but i need to send to you my last words of apreciations to you and to the Forum, you are all great !

  4. #4
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open report with items selected (Access 2000)

    Dear Hans,

    I am so sorry to disturb you again, i thought i have finished but now it looks i have overlooked an important aspect of my target.Actually i have to collect the data from two and not one independent tables, namely customers and tbl clients.These two tables are not connected in any way, but in spite of that i have to show them together on the report, because this report is meant to show the schedule for the visits next day, regardless whether the point of visit is a
    Customer or a client. So it is possible somehow to include in the report both the selected customers and the selected clients ? I am sending you the attachement with the tables tblClients and Customers.This also includes the problem of the multiple selection.

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

    Re: Open report with items selected (Access 2000)

    Instead of using two list boxes, create a query that combines clients and customers, and use that to fill a single list box, and as record source for the report.

    1) Create a query in design view. Don't add any tables.
    Switch to SQL view (View | SQL)
    Enter the following SQL statement:

    SELECT CustomerID, CompanyName FROM Customers
    UNION SELECT ClientID, CompanyName FROM tblClients

    Save this query as qryCombined.

    2) Change the line

    mmp.SetData "customers", "CompanyName"

    in the code of the form to

    mmp.SetData "qryCombined", "CompanyName"

    3) Open the report in design view and set its Record Source property to qryCombined.

  6. #6
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open report with items selected (Access 2000)

    Dear Hans,
    Would you tell me please the following.is it possible to save the union query "qryCombined" that you have made for me,according to the selections i have made? if it is possible and if i can send this query with the results
    of the selection, than this application will do the job for me.it suits to my purposes,if can send off the results obtained.Because if i can send the query, the report will be no problem, it will have a source.

    Canett

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

    Re: Open report with items selected (Access 2000)

    Do you mean that you want to save the result of the union query in a table? You can create a make-table query based on the union query, and run it either manually or using code.

  8. #8
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open report with items selected (Access 2000)

    It would be a good solution ,or it wll be the best solution,but i think it is impossible for me to tamper with the complicated code i have copied to add a make table query .Is it possible to send me an example based on the attachemnt i have sent ? Shall i give up the original idea of using the interface from the developers book or just to modify it with a make table query ? I think the last is the best solution,but it is outside of my tether.. Would you send me the make table query ?

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

    Re: Open report with items selected (Access 2000)

    The code from the Access Developer's Handbook is powerful and elegant, but it also makes it harder to manipulate the list boxes unless you understand exactly what the code does. Do you really need to store the selection in a table?

  10. #10
    Lounger
    Join Date
    Jan 2006
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open report with items selected (Access 2000)

    I will never understand what the code i have copied does. I want somehow to send off the results of the union query.So yes that is what i want.If you send me the add on with the make table i will be able to a[pply it
    Thank you in advance
    regards

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

    Re: Open report with items selected (Access 2000)

    I'm sorry, I'm not going to do your work for you. You should either try to understand the code, or use simpler code, or forget about it.

Posting Permissions

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