multi select list box for query criteria

[superfly5203]

I'll start off by saying, I have searched this form and all over google and I have seen tons of these examples, but I can't get any of them to work. I saw one in this thread from a few days ago and a couple months ago, but no matter how much I alter the code I don't get the correct outcome.

So, I have an unbound form that has one listbox, "contractlist" that has three different contract names in it. The point of this query will be to search all records for as many of the contracts the user has selected. Right now i'm using this code, and it sort of works:

Private Sub cmdok_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")
For Each varItem In Me!contractlist.ItemsSelected
strCriteria = strCriteria & "," & Me!contractlist.ItemData(varItem) & ""
Next varItem
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything." _
, vbExclamation, "Nothing to find!"

Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT* FROM tblChangeBasic " & _
"WHERE tblChangeBasic.contract IN(" & strCriteria & ");"
qdf.SQL = strSQL

DoCmd.OpenQuery "Query1"
Set db = Nothing
Set qdf = Nothing
End Sub

Right now there are two issues. One is after I make my selections on my form and hit my command button, I get a pop up to enter a parameter value for the Contract I selected in my list box. If I did two selections, I'll get two pop ups. If i type in my contract title again in the pop up, the query runs correctly and returns all the records associated with those contracts. However, it returns all the fields in the table I'm querying, and it keeps deleting my query settings.

I would like to add a couple more list boxes with the same multi select option to this query, once I can get one working. Also, I have no idea what about 90% of this code means, but I keep learning one issue at a time.

Thanks for any help!

 

[pbaldy]

You can use this to see how the SQL is coming out:

http://www.baldyweb.com/ImmediateWindow.htm

If I had to guess, your contract field is text so you'd tweak this line:

strCriteria = strCriteria & ",'" & Me!contractlist.ItemData(varItem) & "'"

 

[superfly5203]

The contract field is short text. I updated the code with your adjustment and it give me a run time error 3075 syntax error missing operator in query expression. 'tblChangeBasic.contract IN(,Boeing,'Boeing')'.

Boeing is one of my contract names, but I don't know why its listed twice and why one of them has quotes around it.

I put in that code for debug but i can't tell if it did anything, i didn't see anything change.

 

[superfly5203]

I slightly edited my code and I no longer have that pop up box, here is the new code:

Private Sub cmdok_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")

For Each varItem In Me!contractlist.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!contractlist.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    
strSQL = "SELECT* FROM tblChangeBasic " & _
    "WHERE tblChangeBasic.contract IN(" & strCriteria & ");"
    
qdf.SQL = strSQL
DoCmd.OpenQuery "Query1"
Set db = Nothing
Set qdf = Nothing

DoCmd.Close acForm, "ContractTypeSearch"

End Sub

Now I still have the problem of everything in the whole table showing up. Do I need to set up a table just for this query so it only returns the fields I want?

 

[pbaldy]

Is it filtering correctly? You can specify the fields to return:

strSQL = "SELECT Field1, Field2 FROM tblChangeBasic " & _

 

[superfly5203]

Thanks a lot, that did it! It is filtering correctly, if I select only one contract only that one is shown, or what ever combination of the 3 it displays the correct records. And now it only shows the fields I want.

How would I add two more list box criteria to this? Do i just need to declare two more variables and go through the same For Each and If statements but for the new boxes?

 

[pbaldy]

You'd build a separate variable for each, and join them like:

"WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND OtherField IN(" & strCriteria2 & ")"

 

[superfly5203]

That worked, but now my query lists matching records against my two listboxes that repeats forever. I have about 60 entries but this query repeated for over 6,000 records! I must have missed something... but I copied directly from my previous variable.

Private Sub cmdok_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")

For Each varItem In Me!contractlist.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!contractlist.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything in the Contract field." _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

For Each varItem In Me!namelist.ItemsSelected
    strCriteria1 = strCriteria1 & ",'" & Me!namelist.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria1) = 0 Then
    MsgBox "You did not select anything in the Name field." _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If
    strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)
    
    
strSQL = "SELECT [SSCN Number], [SSCN Title], [Contract], [assignedCS], [actualdef] FROM tblChangeBasic, tblPeople, tblChangeDates " & _
    "WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND tblPeople.assignedCS IN(" & strCriteria1 & ");"
    
qdf.SQL = strSQL

DoCmd.OpenQuery "Query1"
Set db = Nothing
Set qdf = Nothing

DoCmd.Close acForm, "ContractTypeSearch"

End Sub

 

[pbaldy]

You don't have joins between your tables, which will cause a Cartesian product.

 

[superfly5203]

I joined the two tables together and it worked perfectly. Now I need to join two additional tables, but I keep getting a syntax error in my join. Can you see an error? Sorry its one long line, every time I start a new row the code builder automatically closes my " " and the statement no longer works.

strSQL = "SELECT [SSCN Number], [SSCN Title], [Contract], [assignedCS], [assignedCO], [propprice], [negprice] FROM ((tblChangeBasic INNER JOIN tblPeople ON tblChangeBasic.PersonID = tblPeople.peopleID) INNER JOIN tblChangeDollars ON tblChangeBasic.moneyID = tbl.ChangeDollars.DollarID) INNER JOIN tblChangeDates ON tblChangeBasic.dayID = tblChangeDates.dateID " & _
    "WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND tblPeople.assignedCS IN(" & strCriteria1 & ");"

Thanks

 

[pbaldy]

I would build a query in the query design grid that joins the four tables. Once you have it working, switch to SQL view and copy the syntax.

 

[superfly5203]

thanks so much! that did it.

 

[pbaldy]

Happy to help!

 

[superfly5203]

Ok, so I added a third criteria, just like the previous two, and now my query displays no results.

Private Sub cmdok_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strCriteria1 As String
Dim strCriteria2 As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query1")

For Each varItem In Me!contractlist.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!contractlist.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything in the Contract field." _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

For Each varItem In Me!namelist.ItemsSelected
    strCriteria1 = strCriteria1 & ",'" & Me!namelist.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria1) = 0 Then
    MsgBox "You did not select anything in the Name field." _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If
    strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)

For Each varItem In Me!typelist.ItemsSelected
    strCriteria2 = strCriteria2 & ",'" & Me!typelist.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria2) = 0 Then
    MsgBox "You did not select anything in the Type field." _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If
    strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
        
strSQL = "SELECT tblChangeBasic.[SSCN Number], tblChangeBasic.[SSCN Title], tblChangeBasic.Contract, tblChangeBasic.TypeNum, tblChangeBasic.DayID, tblChangeBasic.PersonID, tblChangeBasic.MoneyID, tblChangeBasic.[PIO Number], tblChangeDates.DateID, tblChangeDates.actualproposal, tblChangeDates.actualdef, tblChangeDollars.DollarID, tblChangeDollars.propprice, tblChangeDollars.negprice, tblChangeTypes.TypeID, tblChangeTypes.Type, tblPeople.PeopleID, tblPeople.AssignedCS, tblPeople.AssignedCO FROM tblPeople INNER JOIN (tblChangeTypes INNER JOIN (tblChangeDollars INNER JOIN (tblChangeDates INNER JOIN tblChangeBasic ON tblChangeDates.[DateID] = tblChangeBasic.[DayID]) ON tblChangeDollars.[DollarID] = tblChangeBasic.[MoneyID]) ON tblChangeTypes.[TypeID] = tblChangeBasic.[TypeNum]) ON tblPeople.[PeopleID] = tblChangeBasic.[PersonID]" & _
    "WHERE tblChangeBasic.contract IN(" & strCriteria & ") AND tblPeople.assignedCS IN(" & strCriteria1 & ") AND tblChangeTypes.Type IN(" & strCriteria2 & ")  AND tblChangeDates.actualdef BETWEEN [Forms]![ContractTypeSearch]![startdate] and [Forms]![ContractTypeSearch]![enddate] ; "
                 
qdf.SQL = strSQL

DoCmd.OpenQuery "Query1"
Set db = Nothing
Set qdf = Nothing

DoCmd.Close acForm, "ContractTypeSearch"

End Sub

The only thing I can think of that is different about this new list box is I got the list entries by linking it to another table, and this table only has 8 or so entries and all records will have one of these values. This new criteria will sort my records by "types" of changes.

So when everything is working properly the query will sort by date, contract title, who worked on it, and what "type of change" the record is.

 

[pbaldy]

Well, you added a listbox and 2 date textboxes. Are they filled with valid dates that would return records?

By the way, most of us don't expose tables or queries to users, only forms and reports.

 

[superfly5203]

They are valid and the query runs fine with the dates added in, I do have another question about that but I think that will be a thread for another day.

Most likely, I will be the only person using this database. One of my managers might run reports but I think she will just ask me for the information and I'll use the database to give it to her.

I think I might know what the problem is, if I make my new listbox not multiselect and type in the primary key for the "change type" I want to look up, it works fine. Is it because the data is stored as a number and not text as to why it's not pulling up records? The other two boxes were for text, and this one should be integers I'm thinking.

 

[pbaldy]

It certainly could be. You need to compare apples to apples.