Multi Select List box

[Thompyt]

I am attempting to make a multiselect list box where I can report out more than 1 selection at one time. Where I select 1 or multiple I get a form devoid of data.

Field_Name Row Source

SELECT DISTINCT Field.Field_Name, Field.Field_ID
FROM Field
WHERE (((Field.Field_ID)<>0))
ORDER BY Field.Field_ID;

Form VBA Code:

Option Explicit
Private Sub Cancel_By_Field_Report_Click()
    DoCmd.Close acForm, "Select Field"
End Sub
  
Private Sub Open_By_Field_Report_Click()
    Dim oItem As Variant
    Dim sTemp As String
    Dim iCount As Integer
    sTemp = Nz(Me![My_Field].Value, " ")
    iCount = 0
    If Me![My_Field].ItemsSelected.Count <> 0 Then
        For Each oItem In Me![My_Field].ItemsSelected
            If iCount = 0 Then
                sTemp = sTemp & Me![My_Field].ItemData(oItem)
                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & Me![My_Field].ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected", vbInformation
        Exit Sub
    End If
    Me![Field_Name].Value = sTemp
    DoCmd.OpenReport "By Field Report", acViewReport, "", "", acNormal
    DoCmd.Close acForm, "Select Field"
End Sub

By Field Query:

[SELECT Field.Field_ID, Field.Field_Name, System.[System Name], Nomenclature.Nomenclature, Component.Component, Version.Version, Accreditation.[ACC_#], Accreditation.Type_ACC, Accreditation.Accred, Accreditation.Expires, Accreditation.Compliance, Main.Hold
FROM Version RIGHT JOIN (System RIGHT JOIN (Nomenclature INNER JOIN (Field INNER JOIN (Component INNER JOIN (Accreditation INNER JOIN Main ON Accreditation.Accred_ID = Main.[ACC_#]) ON Component.Comp_ID = Main.Comp_ID) ON Field.Field_ID = Main.Field_ID) ON Nomenclature.Nomen_ID = Main.Nomen_ID) ON System.SYS_ID = Main.SYS_ID) ON Version.Vers_ID = Main.Vers_ID
GROUP BY Field.Field_ID, Field.Field_Name, System.[System Name], Nomenclature.Nomenclature, Component.Component, Version.Version, Accreditation.[ACC_#], Accreditation.Type_ACC, Accreditation.Accred, Accreditation.Expires, Accreditation.Compliance, Main.Hold
HAVING (((Field.Field_Name) Like [forms]![Select Field].[Field_Name] & "*"))
ORDER BY Field.Field_ID;

[pbaldy]

I don't think it will work that way. I'd take the criteria out of the query and do this:

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

[Thompyt]

Pbaldy,
Thanks. I get the jist of what you are doing with the code, but if I select 1 or more than 1 item, the report lists all fields. It doesn't report the selected ones.

Private Sub Open_By_Field_Report_Click()
    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    Set ctl = Me.My_Field
If Me.My_Field.ItemsSelected.Count = 0 Then
     MsgBox "Nothing was selected"
        Exit Sub
    End If
For Each varItem In ctl.ItemsSelected
  strWhere = strWhere & ctl.ItemData(varItem) & ","
  strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
    strWhere = Left(strWhere, Len(strWhere) - 1)
    DoCmd.OpenReport "By Field Report", acViewReport, "", "", acNormal
    DoCmd.Close acForm, "Copy of Select Field"
End Sub

[pbaldy]

You didn't use strWhere in the OpenReport line.

[Thompyt]

Not sure how to do that. I have the report directed to the [By Field] query for its data.

In the Field_Name control of the [By Field] query I have - Like [forms]![Select Field].[Field_Name] & "*"))

I am thinking I should change this one somehow.

[pbaldy]

Did you review the link? LIke I said, take the criteria out of the query, then combine the code above with

DoCmd.OpenReport "By Field Report", acViewReport, , "Field_Name IN(" & strWhere & ")"

[Thompyt]

OK, Thanks. I guessed on the right track in where you woould put some code in the DoCmd.Open line, but not the correct code. I also took out the like criteria in the Query. When I select the first 2 lines individually or together, I get a runtime error 3075. Syntax error (missing operator) in query expression'Field_Name IN(Digital Radios,'Digital Radios')' or both fields listed if both are selected.



If I select the 3rd line "Fires", I get a Enter Parameter Value pop-up box titled Fires and requesting a variable input. I select OK and leave it blank and it opens up the form with the correct data for Fires. If I select more than 1 line from the 3rd row on down, I get the same pop-up box for each selected item. I select OK for each one and the report opens with the correct selected data.

[pbaldy]

Take this line out of your code:

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

You want one or the other, not both. The other is for text values, this one for numeric.

[Thompyt]

Works as advertised!!!! Thanks PBaldy, much appreciated!!!!

[pbaldy]

Happy to help!