Multi-Select Listbox problem

[Orson9750]

Access 2003- I am using a Multi-Select Listbox to filter a Report. I select 3 rows on the list and click the Run Report. The following error pops up:

"Syntax error (missing operator) in query expression '(CareOptionsID IN(Combative,Beginning Alzheimers,Dementia))'

The expression debug refers to this line in the expression:
DoCmd.OpenReport "RptCareOptions", acPreview, , "CareOptionsID IN(" & strWhere & ")"

I followed the example of Multi-Select Listbox for filtering a report from www.Baldy.

I apologize if I haven't explained this well enough. I want to multi-selct a number of care options from a list and run a report based on the criteria.

Thanks for any help you may send my way.

 

[pbaldy]

Because your values are text, you'd need to use this format, as mentioned on my site:

strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

The page I think you were looking at is here:

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

 

[wiklendt]

I apologize if I haven't explained this well enough. I want to multi-selct a number of care options from a list and run a report based on the criteria.

Thanks for any help you may send my way.

if you could make sure your text is not miniscule next time, that would also be of help to us.

 

[Orson9750]

I tried to mirror your Multi-Select form example and copied/pasted the event procedure to my command button (onClick) property. I then made the necessary substitutions where needed. I don't know if the syntax error I receive when clicking on the Run Report button has something to do with my using two table to create the query used as the record source for the report.

Any suggestions? Oh and thank you.

 

[pbaldy]

Did you switch to the method above, which will put single quotes around your values?

 

[Orson9750]

Thanks for responding and your help. Yes I did make the change. Now I get this messae: Data Type Mismatch in criteria expression.

Copy of my event

Option Compare Database
Option Explicit
Private Sub cmdOpenReport_Click()
On Error GoTo Err_cmdOpenReport_Click
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant
'make sure a selection has been made
If Me.CareOptions.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Care Option"
Exit Sub
End If
'add selected values to string
Set ctl = Me.CareOptions
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
'open the report, restricted to the selected items
DoCmd.OpenReport "rptCareOptions", acPreview, , "CareOptionsID IN(" & strWhere & ")"
Exit_cmdOpenReport_Click:
Exit Sub
Err_cmdOpenReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenReport_Click
End Sub

 

[pbaldy]

Is CareOptionsID really a text field? Or should we be getting an ID value instead of the text values? Can you post the db?

 

[Orson9750]

CareOptionsID is an autonumber and my primary key for my tblCareOptions. I will attempt to send my DB. It's large so I will have to cut it down.

 

[pbaldy]

That would help. Obvious from your first error that you're comparing apples and oranges here:

CareOptionsID IN(Combative,Beginning Alzheimers,Dementia)

My guess is that we need to go back to the first method that doesn't insert the single quotes, but get the autonumber ID from the listbox instead of the text. It may be as simple as changing the bound column of the listbox.

 

[jayanth1808]

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=9

 

[Orson9750]

PBaldy,

I am unable to send my file due to security token missing. I will see what I can do to get permission

 

[Orson9750]

PBaldy....
With your great advice and patience I finally got the Multi-Select List Box to work with the OpenReport cmdbutton. There still is a problem with the List Box. The List Box displays the Care Options numerous times. Earlier in my DB development, I created a Main Form with the Facility informtion from my tblFacilities (each record is a facility); plus a CareOptions subform. The subform uses a dropdown to select Care Options for the facility, I have done this for each record in my Main Form until all facilities have care options associated to them. My CareOptions subform uses the tblCareOptions. I am sure that is why I see so many entries of each Care Option in my listbox. Do I need to come at this from another approach or can the list box be tweaked to only show a Care Option once?

 

[pbaldy]

Ah, glad to hear it. Normally I would expect the listbox to get its selections from a table that listed options once. If that's not the case, try something like:

SELECT DISTINCT CareOption
FROM tblCareOptions

 

[Orson9750]

Thanks however I need big training wheels when it comes to code. Exactly how do I get this bit of code into my event procedure to make it work?

 

[pbaldy]

That wouldn't be code, it would be the listbox's rowsource query.