how to add list box with multivalue selection in Form and link query output

[Vaibhav2015]

Hi,
I have created form with list box and added list from query. I selected option extended.
I added expression in query to show selected value from Form but in query output it show blank.
Example:- I selected material number in list box in form and when I select multiple material number


Query output must show selected material data.

Can you please guide me how I can select multivalue in list box in Form and show query output.

Thanks,
Vaibhav

[June7]

Requires VBA code. Review http://allenbrowne.com/ser-50.html

[Vaibhav2015]

Requires VBA code. Review http://allenbrowne.com/ser-50.html

Hi,
I tried that vba codes but its not working, can you please advice any other vba codes.
Thanks

[orange]

but its not working....??

What exactly does this mean?
Show us your work, code, error message, line on which failure occurs, .....

[June7]

There is no other method. This code is tried and proven. As orange advises, provide your exact code for analysis and tell us where it fails. Step debug. Refer to link at bottom of my post for debugging guidelines.

You must use a form or report for displaying the data - not the query object directly - form or report can have the query as RecordSource but the filter criteria is applied to the form or report, not a parameter in query.

[Vaibhav2015]

Hi,

Sorry I am not expert in VBA codes. I have used below VBA codes as per my query.

I have one query named "Material_List" where I have list of material(text field) and that I want to add in Form in text box with multiselect (simple) option.
I have another query named "Plant_ACT" where I have all data related to material numbers.

I want to do below action in form :-
when I select multiple material numbers and click Preview, that report or query must extract the data whatever I selected in Form in Material list text box.

Please advice.


Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "Material list"


'Loop through the ItemsSelected in the list box.
With Me.Mat_list
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = ItemData(varItem) & strDelim & ","

End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Material] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Material: " & Left$(strDescrip, lngLen)
End If
End If


Exit_Handler:
Exit Sub


Err_Handler:
If Err.Number <> 2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

[June7]

Your code builds the criteria string but then does nothing with it.

Allen Browne's example shows using the string to filter report. Review the article again.

[Vaibhav2015]

Hi,

I am not able to understand the codes , can you please help what codes I need to add in string.

[orange]

In the Allen Browne article, he uses the strWhere he has developed to open a Report.

...
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere, OpenArgs:=strDescrip
....

Your example does nothing with the criteria string you have developed.