Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Multipick (Access 2000)

    Looks as though your example didn't get attached. Be sure to attache the file as the last step. And you will probably need to zip the file before attaching it.
    Wendell

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    See if the attached example does what you want. Look specifically at the code in the list box's after update event.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    Thank you fvery much for your attachement.I think your suggestion will perfectly suit me.Would you have a look at what i have done? I have some mistakes but if i can correct them that will met my targets.
    Thank you in advance

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multipick (Access 2000)

    Would you describe what the mistakes are.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    My attachement contains the mistakes. The mistake is items are not found in the collection.Obiously i have not copied correctly the code suggested by NYIntenisty

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Multipick (Access 2000)

    I have made some corrections, don't override your old database with this one, save the old one so you can compare the changes I made.

    I hope it does what you want.

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

    Re: Multipick (Access 2000)

    I wouldn't use the After Update event of the list box, and I wouldn't create a query. It can be done in a much more simple way in the On Click event of the command button:

    Private Sub Command2_Click()
    Dim varItem As Variant
    Dim strWhere As String
    For Each varItem In Me.lstProducts.ItemsSelected
    strWhere = strWhere & ", '" & Me.lstProducts.ItemData(varItem) & "'"
    Next
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "grade in (" & strWhere & ")"
    End If
    DoCmd.OpenReport "RPT1", acViewPreview, , strWhere
    End Sub

    See attached version.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multipick (Access 2000)

    I would like to extent my question:
    i have copied a wonderful example from Access 200 developers handbook and i need help to modify it to my needs.I am attaching the example.In this example i get the list of the products according to
    the grade.I want however to show also the size and the pack, as obtained in the query qryProducts. I have tried the following but i got red letters:
    mmp.SetData "qryProducts", "Grade","size","pack"

    I want to open a report based on the selection in the list box of a form.Can i do it ? According to my attachement, the form is called frmMultick and the listbox is called LstSelected.
    In the OnClick event i have written the folloiwng :
    Dim aSelected() As Variant
    Dim varItem As Variant

    ' Get an array filled with the selected items.
    aSelected = mmp.SelectedItems
    For Each varItem In aSelected
    strShowIt = strShowIt & varItem & vbCrLf
    Next varItem
    DoCmd.OpenReport "Rpt1"

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    i beg to be apologized, i have sent my reformulated question before having seen your both anwers.So please do apologize me. I study them now.
    Thank you very much indeed

  10. #10
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    Works great ! imply superb !

  11. #11
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    Hans,

    Thanks for that great bit!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  12. #12
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    Dear Hans,

    The code works great! . i came accross the following details..If i have products with one and the same name but with different sizes and packs, then even i choose one product, i get all the products in the report.For example, if the first three products are called pears,but with different sizes,then on choosing only one products all the three products appear on the report.How can i avoid that ?

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

    Re: Multipick (Access 2000)

    You can set the Bound Column property to 1 instead of 2, and change the code behind the command button as follows:

    Private Sub Command2_Click()
    Dim varItem As Variant
    Dim strWhere As String
    For Each varItem In Me.lstProducts.ItemsSelected
    ' Note the absence of quotes, since ProductID is a number field
    strWhere = strWhere & ", " & Me.lstProducts.ItemData(varItem)
    Next
    If Not strWhere = "" Then
    strWhere = Mid(strWhere, 3)
    strWhere = "ProductID In (" & strWhere & ")"
    End If
    DoCmd.OpenReport "RPT1", acViewPreview, , strWhere
    End Sub

    See attached version

  14. #14
    Star Lounger
    Join Date
    Feb 2001
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multipick (Access 2000)

    I am having the same error 'items are not found in the collection'. Would someone mind please taking a look at the attached to see what I am doing wrong? At this point all I am trying to do is pass the variants to the query.

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

    Re: Multipick (Access 2000)

    You shouldn't use the After Update event of the list box, because it fires each time the user selects or deselects an item. Instead, you should put a command button on the form and put the code in the On Click event of the button.

    Your query is based on tblCurrent only, but you try to set a condition on the Job field from tblJoblist. I think you want a condition on the Activity field instead.

    If an error occurs, the query qryRTM1 may already have been deleted. Next time, the instruction db.QueryDefs.Delete "qryRTM1" will cause the error message you mention.
    You don't really need to delete the query, you only need to change its SQL statement. Or put a line On Error Resume Next before the delete statement.

    You should build in a check for the situation where the user hasn't selected any items.

    Here is revised code:

    Private Sub cmdProcess_Click()
    Dim varItem As Variant
    Dim strTemp As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim qry As DAO.QueryDef

    On Error GoTo ErrHandler
    If Me.lstProducts.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more products!", vbInformation
    Exit Sub
    End If
    strSQL = "SELECT Activity, Cost_Code, Date, Timesheet_Date, Hours FROM tblCurrent "
    For Each varItem In Me.lstProducts.ItemsSelected
    strTemp = strTemp & "Activity = '" & Me.lstProducts.ItemData(varItem) & "' Or "
    Next varItem

    strSQL = strSQL & "WHERE " & Left(strTemp, Len(strTemp) - 4)
    Set db = CurrentDb

    On Error Resume Next
    db.QueryDefs.Delete "qryRTM1"
    On Error GoTo ErrHandler
    Set qry = db.CreateQueryDef("qryRTM1", strSQL)
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

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
  •