Filtering problems using a list box. How do I read multiple rows?

[Tony]

Hi Group

I am trying to filter my main form (frmMainForm) from the results produced by a search form (frmSearch). The results of the search form are displayed in a list box (lstResult). My working code (reproduced below) can only read the first row of the list box but how can I filter my main form when multiple rows (and Farmers ID's) are produced? (I have tried passing the results of the search form to a Where clause but this also does not work because of references to subforms.

Here is code that I am using to filter my main form

Dim stDocName As Strin
Dim stLinkCriteria As Strin

stDocName = "frmMainForm
stLinkCriteria = "[FarmerID] = " & Me!lstResult.Column(0
DoCmd.OpenForm stDocName, , , stLinkCriteri
DoCmd.Close acForm, "frmSearch

Thank

Tony

 

[Graham Mandeno]

Hi Tony

I guess you are talking about a multi-select listbox?

You can do this by going through the .ItemsSelected collection of the
listbox and constructing a string of items in the form "in (a, b, c)". This
is such a common requirement that I have written a small public function to
do the job. It also looks after the special cases where zero or one items
are selected. Just paste it into a standard module.

Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) & Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter & ","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function

To use it, you incorporate its result into a filter string. For example:
stLinkCriteria = "[FarmerID]" & MultiSelectSQL( lstResult )
DoCmd.OpenForm stDocName, , , stLinkCriteria

If the bound column of your listbox is not numeric, you will need to pass a
double-quote as a delimiter. For example:
Me.Filter = "LastName" & MultiSelectSQL( lstNames, """" )
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Group,

I am trying to filter my main form (frmMainForm) from the results produced

by a search form (frmSearch). The results of the search form are displayed
in a list box (lstResult). My working code (reproduced below) can only read
the first row of the list box but how can I filter my main form when
multiple rows (and Farmers ID's) are produced? (I have tried passing the
results of the search form to a Where clause but this also does not work
because of references to subforms.)

Here is code that I am using to filter my main form.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMainForm"
stLinkCriteria = "[FarmerID] = " & Me!lstResult.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearch"

Thanks

Tony

 

[Douglas J. Steele]

See whether http://www.mvps.org/access/forms/frm0007.htm at "The Access Web"
is what you're looking for.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Group,

I am trying to filter my main form (frmMainForm) from the results produced

by a search form (frmSearch). The results of the search form are displayed
in a list box (lstResult). My working code (reproduced below) can only read
the first row of the list box but how can I filter my main form when
multiple rows (and Farmers ID's) are produced? (I have tried passing the
results of the search form to a Where clause but this also does not work
because of references to subforms.)

Here is code that I am using to filter my main form.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMainForm"
stLinkCriteria = "[FarmerID] = " & Me!lstResult.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearch"

Thanks

Tony

 

[Tony]

Graham

Many thanks for code - it worked a treat!
Excellent stuff

Ton