Populating the Criteria in a Query with the values from a Multilist box

[MattFitzgerald]

Please Help!

I have a form [Frm_Main_Menu]
in this form I have a list box[List1ViewStatusSelect], I have set the property of Multi Select to Extended

I also have a query [Qry_OV Pending Cancelled Null]
in this query I have a field [Status] on which I wish to filter by my Multi Select List Box.

I have tried the following 3 things in the criteria of my query with no sucess:-

Forms![Frm_Main_Menu]![List1ViewStatusSelect]

Forms![Frm_Main_Menu]![List1ViewStatusSelect].MultiSelect

[Forms]![Frm_Main_Menu]![List1ViewStatusSelect].[ItemsSelected]

I have looked through the forum and think I need to use the following code:-

Dim Val1ViewStatusSelect As Variant
Dim str1ViewStatusSelect As String 
 
    For Each Val1ViewStatusSelect In Me.listboxName.ItemsSelected
        str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
    Next Val1ViewStatusSelect
 
    ' to remove trailing comma
    str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect)-2)

I am only guessing at the code but think I may have it correct,
The problem I am having is that I do not know anything about code so I am unsure :-

If I need to attach the code to an event procedure on one of the properties of the list box[List1ViewStatusSelect] in form [Frm_Main_Menu] and then some how call the variant [Val1ViewStatusSelect] in the criteria of my query [Qry_OV Pending Cancelled Null]

or

If I should put the code somewhere in my Query [Qry_OV Pending Cancelled Null]

[MattFitzgerald]

Update on my problem:

I have tried adding the below code to the on click property of the command button for a report which calls the data from my query but I get the message "Object required" which is a little to ambiguous as I am new to access I do not know what object and why it is requiered.

Private Sub CmdPending_Report_Click()
On Error GoTo Err_CmdPending_Report_Click
 
    Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
 
' Concatenates all Status selected in the list box
    For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
        str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
    Next Val1ViewStatusSelect
 
' To remove trailing comma
    str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
 
' Open the Report filtered on the selected Status
    stDocName = "RPT_OV Pending Cancelled Null"
    DoCmd.OpenReport stDocName, acPreview, , [Status] = str1ViewStatusSelect
 
Exit_CmdPending_Report_Click:
    Exit Sub
 
Err_CmdPending_Report_Click:
    MsgBox Err.Description
    Resume Exit_CmdPending_Report_Click
 
End Sub

I am blundering through and hopefully not compounding any errors I have already made. All help is much appreciated as I am either going to solve this or really get myself in a mess

[FishVal]

Update on my problem:

I have tried adding the below code to the on click property of the command button for a report which calls the data from my query but I get the message "Object required" which is a little to ambiguous as I am new to access I do not know what object and why it is requiered.

Private Sub CmdPending_Report_Click()
On Error GoTo Err_CmdPending_Report_Click
 
    Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
 
' Concatenates all Status selected in the list box
    For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
        str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
    Next Val1ViewStatusSelect
 
' To remove trailing comma
    str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
 
' Open the Report filtered on the selected Status
    stDocName = "RPT_OV Pending Cancelled Null"
    DoCmd.OpenReport stDocName, acPreview, , [Status] = str1ViewStatusSelect
 
Exit_CmdPending_Report_Click:
    Exit Sub
 
Err_CmdPending_Report_Click:
    MsgBox Err.Description
    Resume Exit_CmdPending_Report_Click
 
End Sub

I am blundering through and hopefully not compounding any errors I have already made. All help is much appreciated as I am either going to solve this or really get myself in a mess

Hi, Matt.

You have wrong SQL syntax in criteria string you pass to DoCmd.OpenReport method.
The right syntax for multiple comparisson is
[FieldName] IN (Value1, Value2, ... ValueN)

[MattFitzgerald]

Thanks Fishval

I have tried to take on board your reply but I think I have missed something in the way I have applied it as I now recieve Compile error: Expected end of statement when typing the code I think it should be.

My code now looks like this:-

Private Sub CmdPending_Report_Click()
On Error GoTo Err_CmdPending_Report_Click
 
    Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
 
' Concatenates all Status selected in the list box
    For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
        str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
    Next Val1ViewStatusSelect
 
' To remove trailing comma
    str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
    str1ViewStatusSelect = "( " & str1ViewStatusSelect & " )"
 
' Open the Report filtered on the selected Status
    stDocName = "RPT_OV Pending Cancelled Null"
    DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect
 
Exit_CmdPending_Report_Click:
    Exit Sub
 
Err_CmdPending_Report_Click:
    MsgBox Err.Description
    Resume Exit_CmdPending_Report_Click
 
End Sub

The line of code with the error is

DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect

I think the answer is almost there I just need another push in the right direction

[FishVal]

Thanks Fishval

I have tried to take on board your reply but I think I have missed something in the way I have applied it as I now recieve Compile error: Expected end of statement when typing the code I think it should be.

My code now looks like this:-

Private Sub CmdPending_Report_Click()
On Error GoTo Err_CmdPending_Report_Click
 
    Dim stDocName As String, Val1ViewStatusSelect As Variant, str1ViewStatusSelect As String
 
' Concatenates all Status selected in the list box
    For Each Val1ViewStatusSelect In Me.List1ViewStatusSelect.ItemsSelected
        str1ViewStatusSelect = str1ViewStatusSelect & "'" & Frm_Main_Menu.List1ViewStatusSelect.ItemData(Val1ViewStatusSelect) & "', "
    Next Val1ViewStatusSelect
 
' To remove trailing comma
    str1ViewStatusSelect = Left(str1ViewStatusSelect, Len(str1ViewStatusSelect) - 2)
    str1ViewStatusSelect = "( " & str1ViewStatusSelect & " )"
 
' Open the Report filtered on the selected Status
    stDocName = "RPT_OV Pending Cancelled Null"
    DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect
 
Exit_CmdPending_Report_Click:
    Exit Sub
 
Err_CmdPending_Report_Click:
    MsgBox Err.Description
    Resume Exit_CmdPending_Report_Click
 
End Sub

The line of code with the error is

DoCmd.OpenReport stDocName, acPreview, , [Status]IN str1ViewStatusSelect

I think the answer is almost there I just need another push in the right direction

Hi, Matt.

WhereCondition argument of DoCmd.OpenReport has to be string type.

DoCmd.OpenReport stDocName, acPreview, , "[Status] IN " & str1ViewStatusSelect

[MattFitzgerald]

FishVal

Thanks once again

I tried your suggestion but I think there was something else wrong with my code. I found in a book on Access containing a section on trouble shooting where I found out about break points and setting a watch on my Variables and Strings.

I could see from watching my variable that the values from my variable did not populate my string as expected.

I have since found another example of code and have tried to alter this to my requirements and now my string contains what I expect to see when I use the watch function.

I have decided to use a different report and my variable and string names and list box have all changed, but I am still trying to achieve the same thing.

I still have not managed to get this working correctly I get the following error:-

Syntax error (missing operator) in query expression '([S&D Group] IN ('Newport,'Stratford,'Telesales))'.

Newport, Stratford & Telesales were my selections made in my listbox.

My new code is:-

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
 
    Dim stDocName As String, strWhere As String, varItem As Variant
    stDocName = "RPT_Agent Cadbury and BT Data Summary"
    ' Request to edit items selected in the list box
    ' If no items selected, then nothing to do
    If Me!ListSDG.ItemsSelected.Count = 0 Then Exit Sub
    ' Loop through the items selected collection
    For Each varItem In Me!ListSDG.ItemsSelected
        ' Grab the ContactID column for each selected item
        strWhere = strWhere & "'" & Me!ListSDG.ItemData(varItem) & ","
        Next varItem
    ' Throw away the extra comma on the "IN" string
    strWhere = Left$(strWhere, Len(strWhere) - 1)
    ' Open the contacts form filtered on the selected contacts
    strWhere = "[S&D Group] IN (" & strWhere & ")"
    DoCmd.OpenReport stDocName, acPreview, , strWhere
 
Exit_Command29_Click:
    Exit Sub
 
Err_Command29_Click:
    MsgBox Err.Description
    Resume Exit_Command29_Click
 
End Sub

I feel quite out of my depth but am learning lots as I go along unfortunately I have not yet got the answer but I have faith that i'll get there with a little more help.

[FishVal]

Hi, Matt.

Take a look at the string you obtain

'([S&D Group] IN ('Newport,'Stratford,'Telesales))'

String constants are not properly enclosed with single quotes. ;)
Make a little change to the code to make him add both quotes

strWhere = strWhere & "'" & Me!ListSDG.ItemData(varItem) & "',"
 

Hope you've noticed this quote mark before comma.

Regards,
Fish

[MattFitzgerald]

FishVal

Thank you very much for all your help and patience my listbox now works.

I have one last question regarding this:-

In my Listbox properties I have made the following changes

Row Source type = Value List
Row Source = "Newport";"Stratford";"Telesales";"Joint"
Default Value = "Newport";"Stratford";"Telesales";"Joint"

I have tried setting the default value to select all the values. this is because most of the time I will be interested in all values but not always

Thanks again Matt

[FishVal]

FishVal

Thank you very much for all your help and patience my listbox now works.

I have one last question regarding this:-

In my Listbox properties I have made the following changes

Row Source type = Value List
Row Source = "Newport";"Stratford";"Telesales";"Joint"
Default Value = "Newport";"Stratford";"Telesales";"Joint"

I have tried setting the default value to select all the values. this is because most of the time I will be interested in all values but not always

Thanks again Matt

Hi, Matt.

To generate criteria consisting all listbox items you may use the same code having replaced cycle iterating through ItemsSelected with cycle iterating through all items.

    With Me.[Listbox name]
        For i = 0 To .ListCount - 1
            Debug.Print .Column(0, i)
        Next i
    End With