list box filter

[skate]

I have a listbox that displays data according to a selection from a combobox. Now I want the user to be able to make a selection in the listbox and then a subform filters it's records accordingly. Suggestions? This is what i wrote, but it is obviously wrong.

Private Sub lstResults_Click()
On Error GoTo Err_Select_Click
Dim i As Integer
'12 months
i = 0
For i = 0 To 11
If lstResults.Selected(i) = True Then
Me.Day_subform.Form.Filter = "MIndex = lstResults.column(0)"
Me.FilterOn = True
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
End If
Next i
Err_Select_Click:
MsgBox Err.Number
Exit Sub
End Sub

 

[RickSpr]

It's not so obviously wrong, just slightly and subtly.

When you set the Filter property, you need to substitute the value in the list box into the filter string, rather than having the filter string itself determine the value. In other words, you want:
Me.Day_subform.Form.Filter = "MIndex = " & lstResults.Column(0)

You could simplify your procedure in a couple of ways, too:

1. You don't need to loop through all the items in the list box. The index of the currently selected item is the value of the list box's ListIndex property, so instead of the loop you could just code:

    Me.Day_subform.Form.Filter = "MIndex = " & lstResults.Column(0, lstResults.ListIndex)

Since this accesses the selected item directly, you can then eliminate the For/Next loop and the test for Selected(i).

2. If I'm not mistaken, the DoMenuItem is executing the Apply Filter/Sort command. Setting the Filter and FilterOn properties basically do that already, so it's unnecessary.

After simplifying this way, eliminating the no longer necessary variable i, and including the Exit Sub statement you forgot, you have this left:

Private Sub lstResults_Click()
    On Error GoTo Err_Select_Click
    Me.Day_subform.Form.Filter = "MIndex = " & lstResults.Column(0, lstResults.ListIndex)
    Me.FilterOn = True
    Exit Sub
Err_Select_Click:
    MsgBox Err.Number
    Exit Sub
End Sub

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein