Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39

    Listbox and "select all" option - How to adapt this code?

    Hello,

    On my project I'm using this code from this site http://www.tek-tips.com/faqs.cfm?fid=6099 to allow multi selection in a listbox . It works fine.

    My list box lstClients is feeded by the field Client Name from my only table.
    I have added the option (All) to my list box by using a union query (not sure this is the best way to do this by the way).

    The issue is that I don't know how to adapt the code to basically tell "If the user selects "(All)" then you can consider that this is equivalent to select everything"

    I tried to replace this part:

    If lbo.ItemsSelected.Count = 0 Then
    IsSelectedVar = True 'return all if no items selected



    by

    If strListBoxName.Value = "(All)" Then
    IsSelectedVarClients = True 'return all if no items selected

    ... without success.

    Any idea?

    Many thanks,


    Here is the code:

    Code:
    Function IsSelectedVarClients( _
            strFormName As String, _
            strListBoxName As String, _
            varValue As Variant) _
                As Boolean
        'strFormName is the name of the form
        'strListBoxName is the name of the listbox
        'varValue is the field to check against the listbox
        Dim lbo As ListBox
        Dim item As Variant
        If IsNumeric(varValue) Then
            varValue = Trim(Str(varValue))
        End If
        Set lbo = Forms(strFormName)(strListBoxName)
         If strListBoxName.Value = "(All)" Then
        IsSelectedVarClients = True 'return all if no items selected
        Else
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Then
                IsSelectedVarClients = True
                Exit Function
            End If
        Next
    And my union query:

    SELECT T_NewLaunches.[Client Name] FROM T_NewLaunches UNION SELECT "(All)" FROM T_NewLaunches

  2. #2
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by Grek View Post
    Hello,

    On my project I'm using this code from this site http://www.tek-tips.com/faqs.cfm?fid=6099 to allow multi selection in a listbox . It works fine.

    My list box lstClients is feeded by the field Client Name from my only table.
    I have added the option (All) to my list box by using a union query (not sure this is the best way to do this by the way).

    The issue is that I don't know how to adapt the code to basically tell "If the user selects "(All)" then you can consider that this is equivalent to select everything"

    I tried to replace this part:

    If lbo.ItemsSelected.Count = 0 Then
    IsSelectedVar = True 'return all if no items selected

    by

    If strListBoxName.Value = "(All)" Then
    IsSelectedVarClients = True 'return all if no items selected

    ... without success.

    Any idea?

    Many thanks,


    Here is the code:

    Code:
    Function IsSelectedVarClients( _
            strFormName As String, _
            strListBoxName As String, _
            varValue As Variant) _
                As Boolean
        'strFormName is the name of the form
        'strListBoxName is the name of the listbox
        'varValue is the field to check against the listbox
        Dim lbo As ListBox
        Dim item As Variant
        If IsNumeric(varValue) Then
            varValue = Trim(Str(varValue))
        End If
        Set lbo = Forms(strFormName)(strListBoxName)
         If strListBoxName.Value = "(All)" Then
        IsSelectedVarClients = True 'return all if no items selected
        Else
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Then
                IsSelectedVarClients = True
                Exit Function
            End If
        Next
    And my union query:

    SELECT T_NewLaunches.[Client Name] FROM T_NewLaunches UNION SELECT "(All)" FROM T_NewLaunches
    Instead of putting a "All" value in the list items, place a "Select All" button above or next to the list box. Use this to make all items in the list box selected. In the on click event of the button, put the following. This code was made for a list box with column headers. If yous do not have a column header replace this line "For Y = 1 To X - 1" with this "For Y = 0 To X":

    Private Sub btn_SelectAll_Click()
    Dim X As Integer
    Dim Y As Integer
    X = Me.YourListBox.ListCount
    For Y = 1 To X - 1
    Me.YourListBox.Selected(Y) = True
    Next Y
    End Sub

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I would like to see the code that calls this function. Boolean might not be the best data to return.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    Thanks Toyman, will keep this as plan B!
    But since I'll have several list boxes, i would really like to keep the "all" value within the list box (mainly because it's nicer than a button in my opinion), this must be possible?
    Many thanks

  5. #5
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    Quote Originally Posted by June7 View Post
    I would like to see the code that calls this function. Boolean might not be the best data to return.
    Hi,

    I have this as additional query field: IsSelectedVar("F_MainSearch","lstClients",[Client Name])

    I'm also attaching the database if it helps

    Many thanks for your help
    Last edited by Grek; 10-29-2011 at 01:27 PM. Reason: typo

  6. #6
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by Grek View Post
    Hi,

    I have this as additional query field: IsSelectedVar("F_MainSearch","lstClients",[Client Name])

    I'm also attaching the database if it helps

    Many thanks for your help
    Which form do you have running this code. I don't see any list box containing a union join

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Do the UNION for the listboxes row sources.

    Then try this code change in the functions.
    If lbo.ItemData(item) = varValue Or lbo.ItemData(item) = "(All)" Then
    Last edited by June7; 10-30-2011 at 10:37 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    @Toyman: form F_MainSearch then click on the list box lstClients then "row source"

    @June7: Thanks for this. I tried but now it keeps returning all the results no matter what I select. I'm attaching a new version of the databse, with your code and where I have removed unnecessary forms ans list boxes.

    Tkx a lot for your help guys

  9. #9
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by Grek View Post
    @Toyman: form F_MainSearch then click on the list box lstClients then "row source"

    @June7: Thanks for this. I tried but now it keeps returning all the results no matter what I select. I'm attaching a new version of the databse, with your code and where I have removed unnecessary forms ans list boxes.

    Tkx a lot for your help guys
    Instead of messing with the actual code, I just added several lines of codes to the module. When the "All" is selected, the code selects every value in the listbox except the line containing the "all". It then cycles through the rest of the module to give you the record queried.

  10. #10
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    Thanks a lot Toyman!

    I sligthly modified your code and now it does exactly what I want:

    The modified code

    Code:
    Function IsSelectedVarClients( _
            strFormName As String, _
            strListBoxName As String, _
            varValue As Variant) _
                As Boolean
        'strFormName is the name of the form
        'strListBoxName is the name of the listbox
        'varValue is the field to check against the listbox
     
        Dim lbo As ListBox
        Dim item As Variant
        Dim X As Integer
        Dim Y As Integer
        Dim W As Integer
        Dim ItemValue As String
        If IsNumeric(varValue) Then
            varValue = Trim(Str(varValue))
        End If
     
        Set lbo = Forms(strFormName)(strListBoxName)
            If lbo.ItemsSelected.Count <> 0 Then
                W = lbo.ListIndex
                ItemValue = lbo.Column(0, W)
                If ItemValue = "(All)" Then
     
        IsSelectedVarClients = True 'return all if no items selected
        Else
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Then
                IsSelectedVarClients = True
                Exit Function
            End If
        Next
        End If
        End If

    Your code:
    Code:
    Function IsSelectedVarClients( _
            strFormName As String, _
            strListBoxName As String, _
            varValue As Variant) _
                As Boolean
        'strFormName is the name of the form
        'strListBoxName is the name of the listbox
        'varValue is the field to check against the listbox
     
        Dim lbo As ListBox
        Dim item As Variant
        Dim X As Integer
        Dim Y As Integer
        Dim W As Integer
        Dim ItemValue As String
        If IsNumeric(varValue) Then
            varValue = Trim(Str(varValue))
        End If
     
        Set lbo = Forms(strFormName)(strListBoxName)
            If lbo.ItemsSelected.Count <> 0 Then
                W = lbo.ListIndex
                ItemValue = lbo.Column(0, W)
                If ItemValue = "(All)" Then
                    X = lbo.ListCount
                    For Y = 0 To X - 1
                        If lbo.ListIndex = W Then
                            lbo.Selected(Y) = True
                        End If
                    Next
                    lbo.Selected(W) = False
                End If
            End If
     
        If lbo.ItemData(item) = varValue Then 'Or lbo.ItemData(item) = "(All)" Then
        IsSelectedVarClients = True 'return all if no items selected
        Else
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Then
                IsSelectedVarClients = True
                Exit Function
            End If
        Next
        End If
     
     
    End Function
    Last edited by Grek; 10-30-2011 at 01:11 PM. Reason: formating

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Grek, you put my suggested code in the wrong place. Should be:
    Code:
        For Each item In lbo.ItemsSelected
            If lbo.ItemData(item) = varValue Or lbo.ItemData(item) = "(All)" Then
                IsSelectedVarClients = True
                Exit Function
            End If
        Next
    However, making a selection in just the Clients combobox will not select anything because of the criteria for the Status value in the query. Must also make selection in Status. If you want everything to show when the form opens, need to initialize both comboboxes to All.

    Glad you got a working procedure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    Thanks June07 - Your solution is even easier!
    Thanks all - problem solved!
    Enjoy your Sunday

  13. #13
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    Actually, one last question on this: how should I initialize the values to (All) by default?

    I tried this without success:

    Private Sub Form_Open(Cancel As Integer)
    Me.lstClients.Value = "(All)"

    Thanks,

  14. #14
    Toyman is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    233
    Quote Originally Posted by Grek View Post
    Actually, one last question on this: how should I initialize the values to (All) by default?

    I tried this without success:

    Private Sub Form_Open(Cancel As Integer)
    Me.lstClients.Value = "(All)"

    Thanks,
    Try this and see if it works.

    Me.lstClients.Selected(0) = true 'Use "Selected(0)" if you don't have a column header and "Selected(1) if you do

  15. #15
    Grek is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    39
    Works perfect - thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-02-2011, 09:25 PM
  2. Replies: 16
    Last Post: 07-22-2011, 09:23 AM
  3. Using ListBox value as "Body of outlook task"
    By AccessFreak in forum Database Design
    Replies: 3
    Last Post: 05-07-2011, 04:40 PM
  4. ComboBox "Select" and "DLookUp"
    By witooldas in forum Forms
    Replies: 0
    Last Post: 03-23-2011, 03:31 AM
  5. Replies: 4
    Last Post: 12-03-2010, 04:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums