Multi Select List Box to open specific select Data Report

[ijaz8883]

Hi Experts
I have a database and I have a Form where I have put a button with list box to select the data and open the report
But I am facing problem to sort out the data It does not sort the data and give the error of parameter but I want to open with a single parameter without entering the para meter
The DB samplesample.zip is also attached so that you may understand my problem.

[ranman256]

don't use multi select list...they require programming. Instead, use a regular list box and dbl-click item to add to a 'picked' table.
then use the tPicked table to join to the table and only pull those items.
No programming.

[pbaldy]

That code looks familiar. You need delimiters because your value is text:

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

resolves your error.

[ijaz8883]

That code looks familiar. You need delimiters because your value is text:

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

resolves your error.

Mr. pbaldy
your code worked on Pending based query and button. But still a problem facing that its does not filter the data by selecting the Point No.1 or Point No.2 or Point No.3 in report but it shows all the data including unselected point in report.
And can I add a button to select All and Deselect All below this list box?

[ijaz8883]

don't use multi select list...they require programming. Instead, use a regular list box and dbl-click item to add to a 'picked' table.
then use the tPicked table to join to the table and only pull those items.
No programming.

Mr. ranman256
Can you provide me the sample of this DB. It may also solve my problem but I don't understand how to do this because I am not expert in Access.
The above said Code i used is also theft from a website DB and amend it as I required.

[pbaldy]

Check the field you have in the code plus the values returned in the listbox. I think it's the wrong field and the value in the bound column of the list listbox was "pending" if memory serves.

[ijaz8883]

Check the field you have in the code plus the values returned in the listbox. I think it's the wrong field and the value in the bound column of the list listbox was "pending" if memory serves.

No Sorry: I just want the list of Points in List box and the Status Pending/Decided/Transfer in query not in selection suggestion.

Sorry I don't understand. Now Just to Filter the Point (Point No.1/Point No.2/Point No.3) is remaining here and every thing is complete.
Let me know what to do here

[ijaz8883]

Check the field you have in the code plus the values returned in the listbox. I think it's the wrong field and the value in the bound column of the list listbox was "pending" if memory serves.

Oh Yes Thanks a lot:
I Got it with some alteration! I just change in List Box Query: Where there was Status I finished it and write the same as other
Attachment 40028
It was just an Idea and its work: But may be its make any problem afterword but at this time its working perfect.
and also change just one word in code "Point"
DoCmd.OpenReport "Testreport", acPreview, , "Points IN(" & strWhere & ")"
Now its working
Thanks you Mr. pbadly
I am also waiting the DB of Mr. ranman256 it would also better for me for multiple choice
I am marking your answer as reputation
Select or clear button are also done.

[pbaldy]

That attachment doesn't work for me. In the original, the listbox bound column was Status, which it sounds like you caught. The second was in your VBA code:

DoCmd.OpenReport "Testreport", acPreview, , "Status IN(" & strWhere & ")"

which referred to Status instead of Points.

[ijaz8883]

That attachment doesn't work for me. In the original, the listbox bound column was Status, which it sounds like you caught. The second was in your VBA code:

DoCmd.OpenReport "Testreport", acPreview, , "Status IN(" & strWhere & ")"

which referred to Status instead of Points.

Sorry I think there may be problem in my attached file
The Updated and Complete Working File is attached
Lets check it. May be I have no need to enter an extra field in query by changing in code.

sample Completed.zipsample Completed.zip

[pbaldy]

You could have just selected the Points field:

SELECT DISTINCT TestQuery.Points
FROM TestQuery;

You just would have needed to change the column count and column widths properties of the listbox accordingly. You could also have referred to the second column of the listbox:

ctl.Column(1, varItem)

1 is the second column as the column property is zero based. In your case I'd have done the first for simplicity.