Using a list box as input fields in a query

[Jeff9458526]

Hi There,

I have a table of employees and the certificates they have. The fields in the table are all the possible certificates one can have and the records are set up as yes/no fields for if they have the certificate or not. I am trying to create a query that selects multiple fields (certificates) from the table using a list box with all the possible certificates and show me which employees have the selected certificates.
I have setup a form with the populated listbox from the certificate fields I have, but I cannot seem to link it to the query I am trying to run. I want the query to only show the employees that have the certificates that the user selects from the listbox.

Please Help!

[MMcCarthy]

You will need to create the query programmatically. Create a query based on anything you like called qryDummy. Make sure that the Microsoft DAO library is checked in the references list. Then something like ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim valSel As Variant
Dim strSQL As String
 
   strSQL = "SELECT EmpID, EmpName FROM TableName WHERE "
   For Each valSel In Me!ListboxName.ItemsSelected
      strSQL = strSQL & "[" & Me!ListboxName.ItemData(valSel) & "]=True OR "
   Next valSel
 
   strSQL = Left(strSQL, Len(strSQL)-3 ' remove last OR from statement
 
   Set db = CurrentDB
   Set qdf = db.QueryDefs("qryDummy")
    qdf.SQL = strSQL
 
   qdf.Close
   Set db = Nothing
 
End Sub
 

Mary

[Jeff9458526]

Thanks Mary!

Now that the list box is working properly, do you know how to get the selected listbox fields to show in the results as well in the line:

strSQL = "SELECT Name, selected listbox items FROM tblEmployeeCertification WHERE "

Regards,

Jeff

[MMcCarthy]

Try this ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim valSel As Variant
Dim strSQL As String
Dim strCriteria As String
 
   strSQL = "SELECT Name, " 
 
   For Each valSel In Me!ListboxName.ItemsSelected
     strSQL = strSQL & Me!ListboxName.ItemData(valSel) & ", "
     strCriteria = strCriteria & "[" & Me!ListboxName.ItemData(valSel) & "]=True OR "
   Next valSel
 
   strSQL = Left(strSQL, Len(strSQL)-2 ' remove last , from statement
   strCriteria = Left(strCriteria, Len(strCriteria)-3 ' remove last OR from statement
   strSQL = strSQL & " FROM tblEmployeeCertification WHERE " & strCriteria
 
   Set db = CurrentDB
   Set qdf = db.QueryDefs("qryDummy")
    qdf.SQL = strSQL
 
   qdf.Close
   Set db = Nothing
 
End Sub
 

Mary

[Jeff9458526]

I seem to be getting a run-time error '3075' missing opererator in query expression. I can't seem to find it.

[Jeff9458526]

I seem to be getting a run-time error '3075' missing opererator in query expression. I can't seem to find it.

I have spaces in some of my field names which seems to be messing it up.

[MMcCarthy]

OK, this should work ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim valSel As Variant
Dim strSQL As String
Dim strCriteria As String
 
   strSQL = "SELECT Name, " 
 
   For Each valSel In Me!ListboxName.ItemsSelected
     strSQL = strSQL & "[" & Me!ListboxName.ItemData(valSel) & "], "
     strCriteria = strCriteria & "[" & Me!ListboxName.ItemData(valSel) & "]=True OR "
   Next valSel
 
   strSQL = Left(strSQL, Len(strSQL)-2 ' remove last , from statement
   strCriteria = Left(strCriteria, Len(strCriteria)-3 ' remove last OR from statement
   strSQL = strSQL & " FROM tblEmployeeCertification WHERE " & strCriteria
 
   Set db = CurrentDB
   Set qdf = db.QueryDefs("qryDummy")
   qdf.SQL = strSQL
 
   qdf.Close
   Set db = Nothing
 
End Sub
 

Mary

[Alosja]

Hey everyone,

This problem is similar to what I am trying to make. I have the same problem, but instead of using one table with yes/no records, I used a "many-to-many" relationship, with one table for the employees, one table for the certificates and a third table with records containing the employee ID and certificate ID (plus some extra information on when this certificate was obtained).
In this way, it is easier to update the list of certificates.

Is there a way to achieve the same thing? make a listbox containing the list of certificates and have a query display all the Employee names which are connected to at least all the certificates selected?

I don't know if it is similar in way of working, but it's worth the try.

Thanks in advance!