ListBox Multiselect problem

mchoukeir · Apr 14, 2003

Hi,

I've set up a form that selects records from a table based on a ListBox selection. This functionality works if I make one selection in the ListBox, but I don't know how to get it to work when I make more than one selection. I can get the ListBox to allow me to select more than one value (multiselect = simple or extended), but it's not feeding the form to give me all the records that match the multi selected criteria.

Is there an easy way to fix this problem?

Thanks

Mile-O · Apr 14, 2003

What are you wanting to do with these multi-selections?

Create numerous records? Build a sub-list?

mchoukeir · Apr 14, 2003

The ListBox is in the Form Header, and the records are listed in the Form Details section.

I want it to show all the records that match the criteria in the ListBox. For example, if the ListBox had the following three entries:

London
Paris
Milan

and I select London, I want it to show all the records matching London in the Form Details section, or if I select Paris, I want it to display all the records matching Paris. I've managed to get this functionality to work

What I can't get it to do is to show me all the records that contain Paris or London when I select London and Paris (multiselect) in the ListBox.

Hope that make sense.

Mile-O · Apr 14, 2003

Assuming your database is normalised...

This is one of those occasions where I'd use VBA to produce results.

Using the example you've given, here's what I'd do:

Code:

    Dim intCounter As Integer
    Dim strSQL As String, strCriteria As String

    For intCounter = 0 To lstCities.ListCount Step 1
        If Me.lstCities.Selected(intCounter) = True Then
            strCriteria = strCriteria & "[City] = " & lstCities.Column(0, intCounter) & " OR "
        End If
    Next intCounter
    
    If strCriteria <> vbNullString Then
        strCriteria = Left(strCriteria, Len(strCriteria) - 4)
    End If
    
    strSQL = "SELECT * FROM tblYourTable" & _
        IIf(strCriteria = vbNullString, ";", " WHERE " & strCriteria & ";")

That would generate an SQL statement for you that you can treat as a query.

ListBox Multiselect problem

mchoukeir

Guest

Mile-O

Back once again...

mchoukeir

Guest

Mile-O

Back once again...

Similar threads

Users who are viewing this thread