Multi Select List Box --> Query

S

stephen81

Registered User.
Local time
Today, 21:58
Joined
Nov 27, 2002
Messages
198
Help!!!

I need to create a query which selects records from table [Employees] where [Employee ID] matches [ID] selected from a multi select list box on a form when I click a button on my form.
My list box is called list0. I have found several posts on this subject with samples of code etc. but I am a real newbie and don't understand where this code has to go. Could someone tell me what code I need, with comments so I can understand what it means and let me know where I have to put this code please.

Thanks

Stephen
 
You need to create the list box on a form. Then create a button on the form that runs your query. In the relevant field in your query you need to enter the name of the control that you have created on the form to hold your list box.

So if your form is called frmMyForm and the list box is list0, then as a criterion in your query under [Employee ID] you enter
[forms].[frmMyForm].[list0]
This will then take the value selected in the list box and use it as the criterion in the query.

By the way, you should not use spaces in your field names. There are occasions when this will create a major problem. Similarly, you should not use Access reserved words, or punctuation in field names.
 
Last edited:
neileg, the solution you posted will work fine with single-select list boxes, but not multiselect boxes, which the user specified he was using. In the multiselect case, [forms].[frmMyForm].[list0] evaluates to Null if one, some, or all of the values are selected.

A simple query unfortunately won't return the required records. Stephen, if you can settle for just selecting a single value at a time, then that would be simpler and neileg's solution will work.

However, if you want to be able to select more than one value, it gets more complicated. Let us know.
 
Sorry, I might not have been clear enough in my original post. I have the form and my list box, and a button to run my query which would work fine with a non-multi select list box, but I need it to work with a multi select list box which, from reading other posts on the subject, requires VBA coding to get all the selected values from the list box. I need the code and I need to know where the code goes (i.e. as part of the on click event for my button or wherever).
 
Sorry dcx693. You must have posted while I was writing my last comment. You're right. I do need to select more than one value.
 
Oops, failed to read the question properly!

I don't know how to make this work in a multi-select box since I've never needed to use them before.
 
No problem Neil. Hopefully dcx693 or someone else can come up with the goods.
 
Here's one way to come up with a comma-delimited list of the selected entries in your multi-select list box. This assumes that the desired column in your list box is the first one (i.e., column 0):

Code: 
Dim varItem As Variant
Dim strList As String

    For Each varItem In Me.lstTest.ItemsSelected
        strList = strList & Me.lstTest.Column(0, varItem) & ", "
    Next varItem
    
    strList = Left(strList, Len(strList) - 2)
    
End Sub

That code can be in the OnClick event of a command button called "Find users" or something like that.
 
Thanks. How do I then get that comma separated list into a query (or table)?
 
stephen81, how do you want to display the results?
 
I want to use the selected values from the list to go into a select query so I can display the records relating to those values.
e.g.

Table ([Employees]) contains [Employee_Name] and [Employee_ID] and I want to display both these fields, but only for the records selected from my list box with [ID] (which corresponds to [Employee_ID] in my table.
 
There are several ways to do this. I initially tried setting up this public function:
Code: 
Public Function ListIDs() As String
Dim varItem As Variant
Dim strList As String

    With Forms("Form1")
        For Each varItem In .lstTest.ItemsSelected
            strList = strList & .lstTest.ItemData(varItem) & " Or "
        Next varItem
    End With
    
    strList = Left(strList, Len(strList) - 4)
    ListIDs = strList
      
End Function
I tried that, so it could be called from the select query, but for some reason, I couldn't get it to work. It generates a list of numbers separated by " Or ", so that it becomes something like "9 Or 10 Or 11". That solution should work as a criteria in a query, but it wouldn't. Perhaps someone can help us out with that.

The other two options are (1) write the selected IDs to a temp table and query it by joining the temp table to the temp table (2) write a dynamic SQL query referencing the list items, save the querydef and the open it like a normal select query.

Edit: Actually, I'm quite curious as to why my initial thought didn't work out so I'm going to post a question my in this forum. :p
 
Last edited:
OK, I came up with this ADO routine:
Code: 
Private Sub cmdCreateQuery_Click()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim vew As ADOX.View
Dim strNewQueryName

    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    Set cmd = New ADODB.Command
    
    cmd.CommandText = "SELECT * FROM Employees " & _
        "WHERE Employee_ID In (" & ListIDs() & ");"
    strNewQueryName = "qryIDs"
    
    cat.Views.Delete strNewQueryName
    cat.Views.Append strNewQueryName, cmd
    DoCmd.OpenQuery strNewQueryName
    
    Set cmd = Nothing
    Set cat = Nothing
    
End Sub
You are responsible for the error checking (making sure the form is open and that the query deletion only runs if the query exists). I put the code behind a command button called cmdCreateQuery. I altered this line in my ListIDs() function from my previous post:
Code: 
Public Function ListIDs() As String
Dim varItem As Variant
Dim strList As String

    With Forms("Form1")
        For Each varItem In .lstTest.ItemsSelected
            strList = strList & .lstTest.ItemData(varItem) & ","
        Next varItem
    End With
    
    strList = Left(strList, Len(strList) - 1)
    Debug.Print strList
    ListIDs = strList
      
End Function
 
I've tried that code but I get an error when I try it - 'User defined type not defined.' for the three ADO declarations in this code.

Private Sub Bulk_Update_Query_Click()
Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim vew As ADOX.View
Dim strNewQueryName

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
Set cmd = New ADODB.Command

cmd.CommandText = "SELECT * FROM Employees " & _
"WHERE ID In (" & ListIDs() & ");"
strNewQueryName = "qryIDs"

cat.Views.Delete strNewQueryName
cat.Views.Append strNewQueryName, cmd
DoCmd.OpenQuery strNewQueryName

Set cmd = Nothing
Set cat = Nothing

End Sub


Any ideas??? Do I need to do anything to tell Access that this is an ADO routine? I've never come across ADO before so I don't really know anything about it.
 
You know I'm pretty ignorant about this, but would the following work?

Create a new control on the form that holds the multiselect box.
Use a fragment of the code you are discussing to create a comma separated list of values in this control.
Use the name of this control as the criterion for an In clause in the SQL of the query.
Make the control invisible if required.

Sorry if this is a red herring but it seems an easier approach.
 
Last edited:
Please ignore my last post, I've now figured out that I just needed to select ADO in the available references. I now get a 'Variable not defined' error for CurrentProject.Connection

Thanks Neil. I'll have a look at that if I can't get this to work.
 
stephen81, in addition to referencing the Microsoft ActiveX Data Objects 2.x Library, you also need a reference to Microsoft ADO Ext. for DDL and Security. Try that, if you don't already have it. If you do have it selected, then there is some other problem.

What version of Access are you using? We can rewrite the code to use DAO if you need to use Access 97.

neileg, your technique is a variation of the one I am using. The only difference is that the comma-delimited list is then placed into a control on the form. That's good because it keeps all the logic on the form. My technique works as a public function. No real difference, it's more a matter of style in this case, I think.
 
I have already got a reference to Microsoft ADO Ext. for DDL and Security but I am using Access 97. Is that likely to be where the problem is? If you could show me how to do it using DAO (if that will make it work) I would greatly appreciate it.
 
OK, here's the DAO version of the code in my cmdCreateQuery_Click routine. It's a good idea to remove those references to ADO and ADOX and restore (if you've removed them) the reference to Microsoft DAO 3.x.

Code: 
Private Sub cmdCreateQuery_Click()
Dim dbs As DAO.Database
Dim strNewQueryName As String
Dim txtSQL As String

    Set dbs = CurrentDb
    
    txtSQL = "SELECT * FROM Employees " & _
        "WHERE Employee_ID In (" & ListIDs() & ");"
    strNewQueryName = "qryIDs"
    
    On Error Resume Next
    dbs.QueryDefs.Delete strNewQueryName
    On Error GoTo 0
    dbs.CreateQueryDef strNewQueryName, txtSQL
        
    DoCmd.OpenQuery strNewQueryName
    
    Set dbs = Nothing

End Sub

The ListIDs public function is unchanged. I've thrown in some simple error checking that cancels the error message if the qryIDs does not already exist.
 
You must log in or register to reply here.

Similar threads

D
Solved Creating a query with 1 table and 4 queries adds a new record
Replies
10
Views
232
Pat Hartman
P
F
Your Query does not include the specified Expression ‘ID’ as part of an Aggregate Function
Replies
8
Views
569
June7
June7
K
Solved Struggling with Distinct
Replies
6
Views
179
KitaYama
K
H
Pull a data from Customer Table based on a combo box
Replies
13
Views
291
hrdpgajjar
H
E
Repeating Details
Replies
11
Views
457
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom