Filter mainform using listbox

[Del_Piero_3]

I have a form called “frm_TrainingLog” based on “TrainingLog” table. This form is continuous and tabular – I need to have it this way. In the header of this form I’ve created a listbox called “QuickSearch” which displays staff details i.e. StaffID, Name etc.

The mainform and the listbox are linked using the following code:

Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[StaffID] = " & Str(Me![QuickSearch])
    Me.Bookmark = rs.Bookmark

The above code does partially work and selects the right record in the mainform but it doesn’t filter out rest of the records which are not equivalent to selected StaffID from the listbox.

I could really do with someone’s help….thanks

 

[Del_Piero_3]

Solution

Never mind, solved it.....heres the solution, might help someone else:

Private Sub QuickSearch_Click()
On Error Resume Next
     
    QuickSearch.SetFocus
    If QuickSearch.Value > 0 Then
        Me.Filter = "[StaffID]=" & QuickSearch.Value
        Me.FilterOn = True
    End If
End Sub

 

[RuralGuy]

For the record, it is only necessary for the control to have the focus if you are using the .Text property. If you use the .Value property, which is the default property and need not be specified, then you don't have to give the focus to the control first.