Multiselect box to populate a report

[Datathumper]

Please someone....

I have two list boxes on a form called frm_multiselect. From the left box (isometrics), you can select fields and move them over to the right box (current isometric). I then want to open a report (rpt_foreman_main) and filter the records by the fields which are currently in the current isometric list box. The query for the report is called qry_foreman_main and the field that I wish to filter in the query is isonumber.

Can anyone help with the code??

Thanks in advance.

 

[ByteMyzer]

Paste the following code into the Code Module for rpt_foreman_main:

Private Sub Report_Open(Cancel As Integer)

Dim curItem As String, curCount As Variant

If IsLoaded("frm_multiselect") Then
    For Each curCount In Forms!frm_multiselect![current isometric].ItemsSelected
        curItem = curItem & IIf(Nz(curItem, "") > "", ",", "") & """" _
            & Forms!frm_multiselect![current isometric].ItemData(curCount) & """"
    Next curCount
    Me.Filter = "[isonumber] In (" & curItem & ")"
    Me.FilterOn = True
End If

End Sub


Private Function IsLoaded(ByVal strFormName As String) As Boolean
    If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
        If Forms(strFormName).CurrentView <> 0 Then
            IsLoaded = True
        End If
    End If
End Function

 

[Datathumper]

Thanks, your a genius...one small thing left, however.

I dont want to have to "select" or highlight the values in this list box in order to run the report, once I transfer them from the left box to the right box, I want it to automatically select all items in the right box (being the current isometric box). Is there a way to automate this so the user does not have to highlight the items for the report?

Thanks

 

[ByteMyzer]

Ahh, in that case, change this statement in the code:

    For Each curCount In Forms!frm_multiselect![current isometric].ItemsSelected

...to this:

    For curCount = 0 to Forms!frm_multiselect![current isometric].ListCount - 1

 

[Datathumper]

Thanks again...It works perfectly!

You would not believe how long I was struggling with that one.

 

[Datathumper]

New issue with this one...once I get to a certain number of items selected (not sure exactly, but maybe 100), it stops functioning and prints all items that are in the table for the report instead of just the selected items in the box...any ideas?