Multiselect listbox

[Fay]

I am currently in over my head. I went to
http://support.microsoft.com/?kbid=135546 get some information about using a
multiselect listbox to select records.

Using Access03. Have frmListing which is bound to qryListingLearners.
qryListingLearners is comprised of two tables: tblLearners and
tblLearnerDepartments. There are two unbound listboxes the first is getting
it's information from tblDepartments the bound field is Department. I am just
working with this listbox at this time. But will also want to use the second
listbox to filter on job titles. But one thing at a time.

There is a datasheet sub form fsubListing on the main form, frmListing. The
subform is also bound to qryListingLearners. I am using the following code
and it ain't working. Cn anyone give me some guidance.

Dim Q As QueryDef, DB As Database
Dim Criteria As String
Dim ctl As Control
Dim Itm As Variant

' Build a list of the selections.
Set ctl = Me![lstDept]

For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
Else
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
& Chr(34)
End If
Next Itm

If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the" & _
" list box!", 0, "No Selection Made")
Exit Sub
End If

' Modify the Query.
Set DB = CurrentDb()
Set Q = DB.QueryDefs("qryListing")
Q.SQL = "Select * From qryListingLearners Where [PerDiem2Unit] In(" &
Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "qryListingLearners"

When this is run it does not limit the returned results.

Thank you for your help.

Fay

 

[Fay]

Sorry for the double posting. I thought I had discarded any not sent this
message.

 

[Bas Cost Budde]

Set Q = DB.QueryDefs("qryListing")
Q.SQL = "Select * From qryListingLearners Where [PerDiem2Unit] In(" &
Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "qryListingLearners"

When this is run it does not limit the returned results.

Can you post the resulting SQL statement in qryListing? -- I don't
understand the last OpenQuery, since that opens the source dataset, not
the filtered one.

 

[Bas Cost Budde]

Sorry for the double posting. I thought I had discarded any not sent this
message.

Glad you notice!

 

[Fay]

Hi thank you. Here is the SQL

SELECT *
FROM tblLearnersDepartment
WHERE ((([PerDiem2Unit]) In ("Cardiology - SAH")));

Obvisouly this shows the last attempt. The DoCmd.OpenQuery probably should
have been left off.

Thank you for your help.

Set Q = DB.QueryDefs("qryListing")
Q.SQL = "Select * From qryListingLearners Where [PerDiem2Unit] In(" &
Criteria & _
");"
Q.Close

' Run the query.
DoCmd.OpenQuery "qryListingLearners"

When this is run it does not limit the returned results.

Can you post the resulting SQL statement in qryListing? -- I don't
understand the last OpenQuery, since that opens the source dataset, not
the filtered one.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

 

[Bas Cost Budde]

Hi thank you. Here is the SQL

SELECT *
FROM tblLearnersDepartment
WHERE ((([PerDiem2Unit]) In ("Cardiology - SAH")));

Obvisouly this shows the last attempt. The DoCmd.OpenQuery probably should
have been left off.

This is not a very useful way of applying IN. There is only one value.
If that is what was selected, it should work, though.

If you inspect the query, do you get a limited set now?