Results 1 to 7 of 7

using a multi select listbox for a parameter query

  1. #1
    RWade is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    4

    using a multi select listbox for a parameter query

    I've been trying to use a list box to for a parameter query and I'm having no luck.

    I gather the selected listbox items into a string.

    Private Sub lstCrafts_LostFocus()
    Dim cnt As Integer
    Dim varItem As Variant

    strSelected = ""
    Result = ""

    For Each varItem In lstCrafts.ItemsSelected
    If strSelected = "" Then
    strSelected = lstCrafts.Column(1, varItem)
    Else
    strSelected = strSelected & " OR " & lstCrafts.Column(1, varItem)
    End If

    Result = strSelected
    Next varItem

    End Sub

    and in my query I call a function to return the string. I launch the query with a button using the macro function.

    Function Selected()
    Selected = strSelected
    End Function

    if I have a single item selected it works fine but as soon as its an or situation it fails to find anything. my string appears to be fine but its not interepreted correctly somehow. would love to know what I"m doing wrong.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    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.

  3. #3
    RWade is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    4
    I guess I could use the filter but is there some reason why my method isn't working ?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    The function doesn't run the code that is in the sub. The value returned by the function is an empty string.

    Variables declared within a procedure have a life only within that procedure. Unless you globally declare strSelected in a general module, it is wiped from memory when the sub procedure finishes.
    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.

  5. #5
    RWade is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    The function doesn't run the code that is in the sub. The value returned by the function is an empty string.

    Variables declared within a procedure have a life only within that procedure. Unless you globally declare strSelected in a general module, it is wiped from memory when the sub procedure finishes.
    The variable strSelected is declared as public in the VB module and passes fine as long as I don't have multiple things selected in the listbox.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,362
    Okay, I see now that you did not declare strSelected in the procedure. Odd, seems the code should work. Want to provide db for analysis? Follow instructions at bottom of my post.

    I do wonder what purpose the Result variable serves. It is also globally declared?
    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.

  7. #7
    RWade is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Posts
    4
    Quote Originally Posted by June7 View Post
    Okay, I see now that you did not declare strSelected in the procedure. Odd, seems the code should work. Want to provide db for analysis? Follow instructions at bottom of my post.

    I do wonder what purpose the Result variable serves. It is also globally declared?

    LOL the result was just going a a text box to prove my string was what I thought it should be. The database is confidential I will see if I can strip it down, so you can get feel for it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. multi select listbox
    By crowegreg in forum Forms
    Replies: 3
    Last Post: 07-28-2012, 01:48 PM
  2. Looping through a multi select listbox - how do I do it?
    By shabbaranks in forum Programming
    Replies: 4
    Last Post: 04-02-2012, 11:56 AM
  3. Replies: 11
    Last Post: 09-22-2011, 01:13 PM
  4. Need Multi-Select listbox code example
    By Buakaw in forum Forms
    Replies: 4
    Last Post: 08-21-2011, 08:37 PM
  5. multi-select listbox and new entry button
    By user622 in forum Forms
    Replies: 2
    Last Post: 07-05-2011, 09:14 AM

Posting Permissions

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