Use MultiSelect to fiter form on field in subform

[justme]

Thank you for looking at my questions. I am using Access 2003 on Windows XP.

Please help me with two questions about Filtering a Form on a Field in a Subform. I am able get this working the way it is set up at http://allenbrowne.com/ser-28.html.

Question 1) I would like to use a multiselect list box in place of a combo box. The code below works if only one person’s name is selected. Here is the part of the code where I am stuck (unique IDs are text). I think the WHERE clause is wrong.

If Me!lstUs.ItemsSelected.Count > 0 Then
strRcSrc = strRcSrc & “(“
For Each varItem In Me!lstUs.ItemsSelected
strRcSrc = strRcSrc & “’” & Me!lstUs.ItemData(varItem) & “’ OR “
‘ strRcSrc = strRcSrc & “[SntTo] =’” & Me!lstUs.ItemData(varItem) & “’ OR “
Next
End If
If strRcSrc <> “” Then
strRcSrc = strRcSrc Left$( strRcSrc, Len(strRcSrc) – 4)
Else
MsgBox “strRcSrc is empty”
Exit Sub
End If
‘ Change RecordSource
sSQL = “SELECT DISTINCT tblMain.* FROM tblMain “ & _
“INNER JOIN tblSub ON “ & _
“tblMain.MainID = tblSub.MainID “ & _
“WHERE tblSub.SntTo = “ & strRcSrc & “”
Me.RecordSource = sSQL

You can see by the commented lines that I’ve tried to put the SubForm control, SntTo, on the other side of the = sign in the WHERE clause.
“WHERE tblSub = ([SntTo] = ‘UniqueID1’ OR [SntTo] = ‘UniqueID2’) … Etc. When I did that I get an error “Enter Parameter Value”. When the control is on the left of the = sign,
“WHERE tblSub.SntTo = “ & strRcSrc & “” I get an error “You Cancelled the Previous Operation”. Please help!

Question 2) When I build a filter string on a regular filter (for the main form), I use the following code successfully. How do I change (the first line) so it will work with the code above?

If DCount(“[MainID]”, “tblMain”, strRcSrc) <> 0 Then
sSQL = ………
Else
MsgBox “No records found”
Exit Sub
End If

Any help you provide is greatly appreciated. I had to retype the code so there may be a typo in there.

v/r,
Janet

 

[pbaldy]

To use a multiselect listbox, you have to loop through the selected items. You'd also want to build an IN clause. Here's a technique that isn't exactly what you want, but you can adapt it to build your SQL string:

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

 

[justme]

Thank you PBaldy,

I appreciate your generous help and time.

v/r,
janet