Search Form Listbox - empty until a search term is entered?

[kate10123]

Hi there,

In the attached search form, on opening it the results listbox shows all possible entries and then when the user enters a search term into the textbox, the listbox refreshes to show any matches.

Is there a way to make the listbox empty until a search term is entered?

I am just thinking that the performance of the form will decrease as records get added and have to load everytime the form opens!

Any advice/suggestions appreciated

 

[aqif]

Personally I think it would not make much of a difference but I can understand that for visual reasons it will look a bit more professional to display blank list box at the start and then filter it as you press keys on Change event. I would suggest following strategy

Don't assign any row source to the list box so that when you open form the list box is empty and therefore load quickly. Then on Change event you can assign the row source to the list box and then requery it. Your code on Change event will look like this

<Code>

Private Sub Search_Change()

Dim vSearchString As String

If Me.Search.Text = "" Then
Me.QuickSearch.RowSource = ""
Else

Me.QuickSearch.RowSource = "SELECT tbl_Student.Surname AS Expr1, tbl_Student.First_Name AS Expr2, tbl_Student.StudentID AS Expr3, tbl_Student.Course AS Expr4 " & _
" FROM tbl_Student WHERE tbl_Student.Surname Like '*" & [Forms]![frmStudentSearch]![Search2] & "*' Or tbl_Student.StudentID Like '*" & [Forms]![frmStudentSearch]![Search2] & "*' " & _
" ORDER BY tbl_Student.Surname;"
End If

vSearchString = Search.Text
Search2.Value = vSearchString

Me.QuickSearch.Requery

End Sub
</Code>

I have attached the modified db. It will be enough for you to get started.

 

[kate10123]

Thanks for the quick reply.

I will play around with this to see how it works, looks just like what I needed so thanks for your help.