ListBox Query

[database]

Hi everyone,

I have 2 listboxs on my form. In one listbox there are names of groups( group is a field in my table) and the other listbox is blank. When i select the names in the first listbox and press the button "Move selected", the selected name go in to the 2nd listbox.

PROBLEM:
I want to open a reprt for only those group names that are selected by pressing a button.
I know there has to be some code to run a query like
Select * from table where ..... (group names are those that are selected.)
Can anyone tell me how to do this?

and wher do i put this code?

I appreciate your help
Thanks
dB

 

[RichO]

You could accomplish this using the report's filter property and setting up the filter criteria from the list box.

Add an unbound text box control to your form and make it non-visible. Name it txtFilter.

Set up a command button with something like the following code:

    Dim i   As Integer
    Dim str As String
    str = ""
    For i = 0 To MyListBox.ListCount - 1
    str = str & Chr$(34) & MyListBox.ItemData(i) & Chr$(34) & ", "
    Next i
    str = Left$(str, Len(str) - 2)
    
    Me.txtFilter = "[GroupName] IN (" & str & ")"
    DoCmd.OpenReport "MyReport", acViewPreview

Then go into design view of your report and in the On Open event:

Me.Filter = Forms!MyForm!txtFilter
Me.FilterOn = True

Please note that you have to change some of the control names and object names to match the names you are using in your database.

See if that works for you.

 

[database]

You could accomplish this using the report's filter property and setting up the filter criteria from the list box.

Add an unbound text box control to your form and make it non-visible. Name it txtFilter.

Set up a command button with something like the following code:

    Dim i   As Integer
    Dim str As String
    str = ""
    For i = 0 To MyListBox.ListCount - 1
    str = str & Chr$(34) & MyListBox.ItemData(i) & Chr$(34) & ", "
    Next i
    str = Left$(str, Len(str) - 2)
    
    Me.txtFilter = "[GroupName] IN (" & str & ")"
    DoCmd.OpenReport "MyReport", acViewPreview

Then go into design view of your report and in the On Open event:

Me.Filter = Forms!MyForm!txtFilter
Me.FilterOn = True

Please note that you have to change some of the control names and object names to match the names you are using in your database.

See if that works for you.

Hey RichO...

Thanks for the reply...

It still doesnt work,... can you tell me where i am exactly going wrong?

I have attached a sample DB

Thanks for your help !

dB

 

[RichO]

The code you entered runs correctly to concatenate the filter string. That part does work. The problem is that the report is trying to filter by group name (per the filter set up by the code) but the record source of your test report does not contain a field "Group_Name".