VBA Code for Multi-Select ListBox

[mo9rissat]

Hello,
In a form, I have a listbox displaying the marks. In the header of the list box I have displayed the value "--All--" based on a union query. So my listbox looks like:

--All--
18
05
13
13
02

I wanted to give the user the ability to select multiple values ​​in the list, So in the properties of the listbox, I selected "Extended" in "Multiple Selection".
To view detailed information about the selected values in the listbox, I saw that I must use VBA code... So I used a Submit button. In this button's event I put:

Private Sub cmd0_Click()
    Dim x As Long
        For x = 0 To Me.lst0.ListCount - 1
            If Me.lst0.Selected(x) = True Then
                If [lst0].Value = "--All--" Then
                DoCmd.OpenQuery "AllQuery"
                Else
                DoCmd.OpenQuery "CompleteQuery"
                End If
            End If
            Next x
End Sub

Where:
lst0: the name of my listbox;
AllQuery: the Query that i want to display if the user clicks on the value: --All--;
CompleteQuery: the query that i want to display if the user clicks on the other values (the marks).

When I click Submit, it takes me to an empty query, ie it didn't display any record ... I know I have errors in my VBA code, but I don't know exactly where.
thanx for ur help.

 

[MarkK]

You have two DoCmd.OpenQuery commands. What happens when you open those queries directly without using code?

Note that your DoCmd.OpenQuery commands don't ever use the data that may have been selected in the list.

So your code loops once for each item selected in the list. Say you select 5 items (excluding "ALL"). Your code will loop 5 times, opening the same "CompleteQuery" each time, but it can only be opened once, so it just stays open. I think that is what you are seeing.

I don't see errors in the code, but the changes in your list selection have no impact on any query except to determine how many times you loop executes.

Makes sense?
Mark

 

[mo9rissat]

What happens when you open those queries directly without using code?

When I open:
AllaQuery: It displays All the records because it's a

Select * from Student;

CompleteQuery depends on the selected items, because i put in the where condition:

... Where ([MyQuery].[MyField]=[Forms]![MyForm]![MyList])

When I tested my queries it worked, also when I tested to put a VBA code based on the queries on the button's event in simple listbox (no multiple selection), it worked very nice.

Private Sub cmd0_Click()
            
                If [lst0].Value = "--All--" Then
                DoCmd.OpenQuery "AllQuery"
                Else
                DoCmd.OpenQuery "CompleteQuery"
                End If
            
End Sub

I don't see errors in the code, but the changes in your list selection have no impact on any query except to determine how many times you loop executes.

In your opinion what's the solution? What should I do to solve the problem?

Thanx for your help.

 

[MarkK]

I would likely construct the where clause on the fly, something like...

Private Function GetWhereClause()
    Dim x As Long
    dim tmp as string
    For x = 0 To Me.lst0.ListCount - 1
        If Me.lst0.Selected(x) Then
            tmp = tmp & "OR MyField = " & me.lst0.itemdata(x) & " "
        End If
    Next x
    if tmp <> "" then GetWhereClause = "WHERE " & mid(tmp, len("OR "))
End Sub

In this case the WHERE clause is constructed in this function, (code was not tested.)
Does that make sense?
Mark

 

[mo9rissat]

Do you mean, that if I use your code, I must delete the where clause in my query??

Also I didn't understand your code (sory I'm still beginner in VBA).

I would likely construct the where clause on the fly, something like...

Private Function GetWhereClause()
    Dim x As Long
    dim tmp as string
    For x = 0 To Me.lst0.ListCount - 1
        If Me.lst0.Selected(x) Then
            tmp = tmp & "OR MyField = " & me.lst0.itemdata(x) & " "
        End If
    Next x
    if tmp <> "" then GetWhereClause = "WHERE " & mid(tmp, len("OR "))
End Sub

In this case the WHERE clause is constructed in this function, (code was not tested.)
Does that make sense?
Mark