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!

msquared

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 ...

Expand|Select|Wrap|Line Numbers

 
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

msquared

Try this ...

Expand|Select|Wrap|Line Numbers

 
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

Quote:

Originally Posted by 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.

msquared

OK, this should work ...

Expand|Select|Wrap|Line Numbers

 
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!

Using a list box as input fields in a query

re: Using a list box as input fields in a query

re: Using a list box as input fields in a query

re: Using a list box as input fields in a query

re: Using a list box as input fields in a query

re: Using a list box as input fields in a query

re: Using a list box as input fields in a query

re: Using a list box as input fields in a query

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about