Using Listbox contents as query parameter

[dixie]

I have an extended multi-select list box of names in which the bound column
is a person ID field called ID (text, not numerical).

I want that list to act as a parameter in a query that is simply going to
produce a medical report for each person in the list box. Now, I know this
should be easy. My query for the medical report only has 4 fields, ID,
Firstname, Lastname, Medical, but I cant seem to use the listbox of names to
act as the ID parameter for the query.

I have read the item on Dev Ashish's site and am still in the dark (I really
believe I am quite dumb when I read some of the articles and techniques used
in Access L)

The Listbox is called lboExcID.

Can anyone please help me get this to work.

dixie

[Pieter Linden]

> I want that list to act as a parameter in a query that is simply going to

produce a medical report for each person in the list box. Now, I know this
should be easy. My query for the medical report only has 4 fields, ID,
Firstname, Lastname, Medical, but I cant seem to use the listbox of names to
act as the ID parameter for the query.

Sounds like you're making this harder than it should be. Just use the
code to create the value list, for example, 'A' OR 'B' OR 'C', and
then just pass that string as the Where clause in the OpenReport
command.

Option Explicit

Private Sub Command2_Click( )
'************** ****** Code Start *************** *********
Dim frm As Form, ctl As Control
Dim varItem As Variant

'---this will be the filter string that I'll open the report with
Dim strFilter As String

Set frm = Me
Set ctl = frm!List0
strFilter = "[SerialNo]='"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelect ed
strFilter = strFilter & ctl.ItemData(va rItem) & "' OR
[SerialNo]='"
Next varItem

'Trim the end of strSQL
strFilter = Left$(strFilter , Len(strFilter) - 16)
'************** ****** Code end *************** *********

DoCmd.OpenRepor t "rptCompute rs", acViewPreview, , strFilter

[dixie]

Pieter, I tried to run this code and I get an Error 5, Invalid Procedure
call or argument at the line:

strFilter = Left$(strFilter , Len(strFilter) - 16)

The changes I have made to your original code are as follows:
I have substituted my Listbox name in the Set ctl line and also ID for
SerialNo

Mine now looks like this:

Private Sub MedicalReport_C lick()
Dim frm As Form, ctl As Control
Dim varItem As Variant

'---this will be the filter string that I'll open the report with
Dim strFilter As String

Set frm = Me
Set ctl = frm!lboBulkComm end
strFilter = "[ID]='"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelect ed
strFilter = strFilter & ctl.ItemData(va rItem) & "' OR [ID]='"
Next varItem

'Trim the end of strSQL
strFilter = Left$(strFilter , Len(strFilter) - 16)

DoCmd.OpenRepor t "rptMedicalInfo rmation", acViewPreview, , strFilter

End Sub

Also, could you explain what you are trimming from the strFilter and why the
number 16?

dixie

Private Sub Command2_Click( )
'************** ****** Code Start *************** *********
Dim frm As Form, ctl As Control
Dim varItem As Variant

'---this will be the filter string that I'll open the report with
Dim strFilter As String

Set frm = Me
Set ctl = frm!List0
strFilter = "[SerialNo]='"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelect ed
strFilter = strFilter & ctl.ItemData(va rItem) & "' OR
[SerialNo]='"
Next varItem

'Trim the end of strSQL
strFilter = Left$(strFilter , Len(strFilter) - 16)
'************** ****** Code end *************** *********

DoCmd.OpenRepor t "rptCompute rs", acViewPreview, , strFilter

[dixie]

Pieter, ignore that last message. I didn't actually have any Items selected
in the list box when I ran the code, but now, when I select just the first
person, I get Error 3075 - Syntax error in string in query expression
'([ID]='0)'. If I select 2 people, that error then reads '([ID]='030154' OR
[ID]='0)'.
030154 is the ID of the first person in the items selected, but there is no
ID for the 2nd person, just the 0. As I select more people, it continues
like that, always missing the ID of the last person.
Note my ID field, although made up of numbers is actually a text field.
The error is occurring in the last line of the code which is the opening of
the report with the strFilter.

dixie

"dixie" <di****@dogmail .com> wrote in message
news:SL******** *********@nnrp1 .ozemail.com.au ...

Pieter, I tried to run this code and I get an Error 5, Invalid Procedure
call or argument at the line:

strFilter = Left$(strFilter , Len(strFilter) - 16)

The changes I have made to your original code are as follows:
I have substituted my Listbox name in the Set ctl line and also ID for
SerialNo

Mine now looks like this:

Private Sub MedicalReport_C lick()
Dim frm As Form, ctl As Control
Dim varItem As Variant

'---this will be the filter string that I'll open the report with
Dim strFilter As String

Set frm = Me
Set ctl = frm!lboBulkComm end
strFilter = "[ID]='"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelect ed
strFilter = strFilter & ctl.ItemData(va rItem) & "' OR [ID]='"
Next varItem

'Trim the end of strSQL
strFilter = Left$(strFilter , Len(strFilter) - 16)

DoCmd.OpenRepor t "rptMedicalInfo rmation", acViewPreview, , strFilter

End Sub

Also, could you explain what you are trimming from the strFilter and why the number 16?

dixie

[dixie]

I've been experimenting with the number at the end of the line
strFilter = Left$(strFilter , Len(strFilter) - 16)
and have found that if I change the number 16 to a 10, I can get through the
errors and get a report opening. Can you explain to me what this number is
actually doing and how you arrive at the correct value for it?

dixie