multi-select list box

[MSD]

I am running a report that uses a query as its record source and opens a
form collecting beginning and ending item numbers to feed to the query. This
works, but now I'm trying to use the result of a multi-select list box on
the form to feed multiple item numbers to the query.

In other words - if you pick item #100,105,110 from the list box, the query
should display information for those 3 items.

If I type 'IN (100,105,110)' in the criteria cell of the 'itemNo' field in
my query, it works - I get information for those 3 items. However, I would
like the list of items to be variable, coming from the items selected in the
multi-select list box on my form.

How do I loop through the items in the list box and feed them to the query?
What should I put in the criteria cell for my query?

Thanks very much,

Emma

[Douglas J. Steele]

Take a look at http://www.mvps.org/access/forms/frm0007.htm at "The Access
Web"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"MSD" <ms*********@hotmail.com> wrote in message
news:jweUb.403401$ts4.58439@pd7tw3no...

I am running a report that uses a query as its record source and opens a
form collecting beginning and ending item numbers to feed to the query. This works, but now I'm trying to use the result of a multi-select list box on
the form to feed multiple item numbers to the query.

In other words - if you pick item #100,105,110 from the list box, the query should display information for those 3 items.

If I type 'IN (100,105,110)' in the criteria cell of the 'itemNo' field in
my query, it works - I get information for those 3 items. However, I would
like the list of items to be variable, coming from the items selected in the multi-select list box on my form.

How do I loop through the items in the list box and feed them to the query? What should I put in the criteria cell for my query?

Thanks very much,

Emma

[Rich P]

Well, if you don't mind writing a little bit of code in your report you
could do something like this in the Report Open Event and Detail Event:

************************************************** ****
Option Compare Database
Option Explicit

Dim RS As Recordset

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Text0 = RS(0)
RS.MoveNext
End Sub

Private Sub Report_Open(Cancel As Integer)
Dim frm As Form, ctl As Control,
Dim v As Variant, str1 As String, strSql As String
Set frm = Forms!Form1
Set ctl = frm!List16
For Each v In ctl.ItemsSelected
str1 = str1 & "'" & ctl.ItemData(v) & "',"
Next
str1 = Left(str1, Len(str1) - 1) 'get rid of last comma
Me.RecordSource = "Select fld1 From Table1 " _
& "Where fld1 In (" & str1 & ")"
strSql = "Select fld1 From Table1 " _
& "Where fld1 In (" & str1 & ")"
Set RS = CurrentDb.OpenRecordset(strSql)
End Sub
************************************************** ****

In this example, the report is not directly bound to a recordsource.
You set the recordsource in the Report_Open event. I am only using one
field from a table, but you could select multiple fields and you filter
this recordset from the selections on the listbox on your form (which is
list16 in my example). You can use the

DoCmd.OpenReport "Report1" statement

and when Report1 open it will automatically read the selections from the
listbox. Then say you have 5 fields in the detail section of your
report, they could be txt0, txt1, txt2, txt3, txt4 or maybe you named
them

txtID, txtRegion, txtName, txtAddress, txtPhone

In the Detail section you would do this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
txtID = RS(0) 'or RS!ID
txtRegion = RS(1) 'or RS!Region
txtName = RS(2) 'or RS!Name
txtAddress = RS(3) 'or RS!Address
txtPhone = RS(4) 'or RS!Phone
RS.MoveNext
End Sub

And, of course, your sql statement in the report_open event would select
the same fields for the RecordSource and for the Recordset var (RS).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Emma Danielson]

Thanks so much for your help. I think I'm close to having it work now.

I modified your code a bit - I create a string that contains the values
from the list box that I need for the parameter query. It looks like
this, e.g. 201,203,205.

In my parameter query, in the criteria cell for itemNo, I have :
In ([Forms]![frmSelectItems]![txtSelect])
where txtSelect is the name of the hidden control containing the string
'201,203,205' and frmSelectItems is the form with the list box and
hidden text box.

If I type:
In (201,203,205)
directly into the criteria cell, it works, however it doesn't work the
way I am doing it - even though the value of
[Forms]![frmSelectItems]![txtSelect] is 201,203,205.

Any ideas?

Thanks again,

Emma

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Shane Pea]

Hi Emma,

This is the code I use to print from a multi-select box:

InSQL = CreateINClauseFromLB("lbListbox", "frmSelectItems",
ColWhereIDIsObtainedFrom, NoPosted)
SQLString = "[FieldName] IN (" & InSQL & ")"
DoCmd.OpenReport ReportName, , , Filter
Msgbox NoPosted & "Item(s) printed"


Public Function CreateINClauseFromLB(LB As String, FormName As String, Col
As Long, ByRef Count As Long) As String
'Create an comma seperated string from the selected items in a list box
'Returns the no of items in the list in Count

Dim ItemIndex As Variant
Dim Tempst As String
If TestMode = False Then On Error GoTo ErrorHand

Count = 0

If (Forms(FormName)(LB).ItemsSelected.Count < 1) Then
' If there are no items in the list, close form and exit procedure.
CreateINClauseFromLB = ""
Exit Function
End If

ItemIndex = 0

'Determine which listbox items are selected.
For Each ItemIndex In Forms(FormName)(LB).ItemsSelected
If Nz(Forms(FormName)(LB).Column(Col, ItemIndex), -1) > 0 Then
Tempst = Tempst & Format(Forms(FormName)(LB).Column(Col, ItemIndex))
& ","
Count = Count + 1
End If
Next ItemIndex

'Ensure that temp string has some characters first, then remove last comma
If Tempst <> "" Then Tempst = Left(Tempst, Len(Tempst) - 1)

CreateINClauseFromLB = Tempst
Exit Function
ErrorHand:
EHD , MODULE_NAME
End Function

"Emma Danielson" <ms*********@hotmail.com> wrote in message
news:40***********************@news.frii.net...

Thanks so much for your help. I think I'm close to having it work now.

I modified your code a bit - I create a string that contains the values
from the list box that I need for the parameter query. It looks like
this, e.g. 201,203,205.

In my parameter query, in the criteria cell for itemNo, I have :
In ([Forms]![frmSelectItems]![txtSelect])
where txtSelect is the name of the hidden control containing the string
'201,203,205' and frmSelectItems is the form with the list box and
hidden text box.

If I type:
In (201,203,205)
directly into the criteria cell, it works, however it doesn't work the
way I am doing it - even though the value of
[Forms]![frmSelectItems]![txtSelect] is 201,203,205.

Any ideas?

Thanks again,

Emma

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!