Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select ALL items in list box (Access 2000 all updates)

    I have a list box, multi select, from which I can select names and then print #10 envelopes.

    I have figured out how to add ALL to the top of the list, by using a Union Query as the SQL that populates the list box.

    What do I have to add to the code below, so that when ALL (the top item in the list) is selected, items from the second from the top to the end of the list show as selected?

    Private Sub cmdSelectListBox_Click()
    On Error GoTo Err_cmdSelectListBox_Click
    Dim stDocName As String
    stDocName = "rptSelectFromListBox"
    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim strList As String

    strList = ""
    Set frm = Forms!frmSelectAddressesToPrint
    Set ctl = frm!List0

    For Each varItm In ctl.ItemsSelected
    strList = strList & ctl.ItemData(varItm) & ", "

    Next varItm
    strList = Left(strList, Len(strList) - 2)
    DoCmd.OpenReport stDocName, acPreview, , "[RecordID] IN (" & strList & ")"

    Dim ndx As Integer
    For ndx = 0 To Me.List0.ListCount - 1
    Me.List0.Selected(ndx) = False
    Next
    Me.txtSelected = Null
    Me.Text19 = Null
    Exit_cmdSelectListBox_Click:
    Exit Sub

    Err_cmdSelectListBox_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelectListBox_Click

    End Sub

    Any help is appreciated.

    Tom

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

    Re: Select ALL items in list box (Access 2000 all updates)

    The idea of adding an "ALL" item to a list is to avoid having to select all individual items.In the code you post, a report is opened, then all items are deselected, presumably to prepare for a next selection. It makes no sense to select all items, then have them deselected immediately. Instead, handle the case that the "ALL" item as been selected separately.

    Moreover, it makes no sense to put this code in the On Click event of the list box itself. It would make selecting multiple items impossible, since the first click already opens the report. Instead, put a command button on the form, and put the code in the On Click event of this command button.

    <code>Private Sub cmdOpenReport_Click()</code>
    <code> Dim ndx As Integer</code>
    <code> Dim strList As String</code>
    <code> Dim strWhere As String</code>

    <code> On Error GoTo ErrHandler</code>

    <code> ' Default where-condition is empty</code>
    <code> strWhere = ""</code>

    <code> ' Test is "ALL" is selected</code>
    <code> If Me.List0.Selected(0) = False Then</code>
    <code> ' Loop through items</code>
    <code> For ndx = 1 To Me.List0.ListCount - 1</code>
    <code> If Me.List0.Selected(ndx) = True Then</code>
    <code> strList = strList & Me.List0.ItemData(ndx) & ", "</code>
    <code> End If</code>
    <code> Next ndx</code>
    <code> ' Get out if nothing selected</code>
    <code> If strList = "" Then</code>
    <code> MsgBox "Please select one or more items.", vbExclamation</code>
    <code> Me.List0.SetFocus</code>
    <code> Exit Sub</code>
    <code> End If</code>
    <code> strList = Left(strList, Len(strList) - 2)</code>
    <code> strWhere = "<!t>[RecordID]<!/t> IN (" & strList & ")"</code>
    <code> End If</code>
    <code> DoCmd.OpenReport "rptSelectFromListBox", acViewPreview, , strWhere</code>

    <code> For ndx = 0 To Me.List0.ListCount - 1</code>
    <code> Me.List0.Selected(ndx) = False</code>
    <code> Next</code>
    <code> Me.txtSelected = Null</code>
    <code> Me.Text19 = Null</code>

    <code>ExitHandler:</code>
    <code> Exit Sub</code>

    <code>ErrHandler:</code>
    <code> MsgBox Err.Description</code>
    <code> Resume ExitHandler</code>
    <code>End Sub</code>

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select ALL items in list box (Access 2000 all updates)

    Hans
    I agree that selecting "ALL" and then having them deselected right away would make no sense.

    The code I posted works just fine when one or several items from the list are selected. The piece of the code that begins with the line "Dim ndx as Integer" clears the selection after the report is run, and also clears a text box (Text19) in which the items selected in the list box appear as they are selected.

    Thanks for the code you sent. I'll try it out.

    All the best.

    Tom

  4. #4
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select ALL items in list box (Access 2000 all updates)

    Hans
    A p.s. to my post.

    I should have added that I also have a command button to Preview or Print the selections. The process goes as follows...
    1. Select items in the list box
    2. The selected items appear in a text box (Tex19)
    3. Once the user is satisfied with the selections, press a command button to Preview or Print.

    Tom

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Select ALL items in list box (Access 2000 all updates)

    I personally find it easer to have 2 extra command buttons: "All" and "Clear". The All button selects all lines in the listbox, the Clear button deselects them all. Then have "Print" and "Preview" buttons.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select ALL items in list box (Access 2000 all updates)

    Hans
    The code you sent is working just fine. I'm sorry that I wasn't quite clear enough in my original post. The code I showed was the code behind the command button which is called "cmdSelectListBox".

    In any event, thanks for your help.

    Mark
    Yes, I can see how having a command button for "ALL" would work. I already have a command button for "Clear." But the code behind the cmdSelectListBox also includes lines to clear following the report opening, so that the selections are clear once the user returns to the form. Having "ALL" at the top of the list box works fine. But the command button for it would be a good option as well. I'll think about it.
    Thanks for the tip.

    Tom

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

    Re: Select ALL items in list box (Access 2000 all updates)

    I apologize - the misunderstanding was on my side. I misread your code and thought it was the On Click event procedure of the list box itself, but it clearly isn't, so my comment was out of place. I'm glad the code helped.

  8. #8
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select ALL items in list box (Access 2000 all updates)

    Hans
    Thanks.
    You have been of so much help on this and other posts I have made in the past. Much appreciated.

    Tom

Posting Permissions

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